Dialect selector (Settings/Views tab)

The SQL dialect is automatically chosen to correspond to the version of Microsoft Access. For Microsoft Access 2000 and Microsoft Access 2002 (XP) the only compatible SQL dialect is Jet 4. For purposes of comparison, we've retained the capability of generating SQL in MS Access 97 dialect (Jet 3). We also have an option allowing you to generate queries in SQL Server T/SQL dialect.

This feature is not intended as a full implementation of natural language query for SQL Server. Basically, it's a preview of the more advanced features supported by our enterprise products, such as VB ELF and the "English Executive" series of products. However, it is possible to run the T/SQL version of SQL translations against SQL Server databases. One reason to do so might be to measure the differences between how certain queries perform under client/server (SQL Server) and non-client/server (Access) architectures.

To illustrate this, we'll give one example. This example assumes that you have an established ODBC DSN referencing the SQL Server Northwind sample database. Setting up such a connection is beyond the scope of this topic.

Assuming there's an available SQL Server instance with the Northwind sample, use the Link Table mechanism of Microsoft Access XP to attach dbo_Employees into the Microsoft Access Northwind database container. This is done by first selecting ODBC Databases in the Files of Type: dropdown box of the Link window. You must then select the DSN corresponding to Northwind and present credentials. Once the Link Tables windows appears, pick dbo.Employees and click OK.

Create a simple View which includes only the dbo_Employees linked table. Enter a question such as "which employees were hired in March 1994". This is translated into Jet 4 SQL and runs successfully against the linked table. The Jet SQL looks like this:

SELECT DISTINCT dbo_Employees.EmployeeID , dbo_Employees.HireDate , dbo_Employees.LastName FROM dbo_Employees WHERE ( ( ( dbo_Employees.HireDate >= #03/01/1994# and dbo_Employees.HireDate < DateAdd ( "m" , 1 , #03/01/1994# ) ) ) ) ;

Now switch into SQL Server dialect by clicking the SQL Server radio button in the Dialect selector. Clear the Pass through checkbox on the Access ELF Query window. (If you don't you'll be prompted to do so at the next step.) The reason you must clear the pass through option is that the Jet query processor can't understand SQL Server (T/SQL) SQL dialect. In this case, the SQL translation looks like this:

SELECT DISTINCT dbo_Employees.EmployeeID , dbo_Employees.HireDate , dbo_Employees.LastName FROM dbo_Employees WHERE ( ( ( dbo_Employees.HireDate >= '03/01/1994' and dbo_Employees.HireDate < DateAdd ( m , 1 , '03/01/1994' ) ) ) ) ;

To post this query to the SQL Server query processor, you'll need to copy this SQL from the SQL window of Access ELF. Paste the SQL text into an editor like NotePad and use the Replace facility to change all references to "dbo_" to "dbo." (this glitch comes from a naming incompatibility between Access and SQL Server). The resulting SQL should look like this:

SELECT DISTINCT dbo.Employees.EmployeeID , dbo.Employees.HireDate , dbo.Employees.LastName FROM dbo.Employees WHERE ( ( ( dbo.Employees.HireDate >= '03/01/1994' and dbo.Employees.HireDate < DateAdd ( m , 1 , '03/01/1994' ) ) ) ) ;

You could also automate this step by activating an Answer script, for instance:

function Patch
   SQL = Replace(SQL,"dbo_","dbo.")
end function

Create a new query in MS Access and paste in this SQL as its definition. From the Query / SQL Specific menu item, choose Pass-Through. Click View / Datasheet View to run the query. You'll be prompted for the OBDC DSN of SQL Server's Northwind sample; use the same DSN and credentials as you used to link in the dbo.Employees table. Now the query will run against the SQL Server database, using SQL Server's own query processor instead of MS Access's Jet query processor.

Only experienced database administrators will want to draw conclusions from the observed difference in speed. First, if the Access database and the SQL Server database are both local, differences may be impossible to detect. The fairest tests would place the Access database on a networked drive of the same remote machine hosting SQL Server. In that case, the performance of SQL Server relative to Access should improve proportionally to the size of the database tables consulted and inversely with the number of records returned by the query.