Access projects and data access pages now use ANSI SQL-92 syntax

Applies to
Microsoft Office Access 2003
Microsoft Access 2002

In Access 2002 or later, projects (.adp) and data access pages support the ANSI SQL-92 standard exclusively. This will affect the way that you develop filter expressions and queries in Access projects. Result data sets will also be displayed differently in data access pages. Previous versions of Access supported ANSI SQL-89 syntax. For more information on the differences between ANSI SQL-89 and ANSI SQL-92 wildcard syntax, see Access Help.

ShowFilters and queries in an Access project

Access projects are designed to connect to a Microsoft SQL Server or Microsoft SQL Serverâ„¢ 2000 Desktop Engine (formerly MSDE) back-end database. Structured Query Language (SQL) is a standard interactive and programming language for working with relational databases. The Transact-SQL language is the native SQL dialect used by both SQL Server and the Desktop Engine. Transact-SQL supports ANSI SQL-92, the latest SQL standard. In Access 2002 or later, projects running queries or filter expressions against SQL Server data must do so in ANSI SQL-92 syntax.

Access databases use the Jet Database Engine to query against the native data. Jet supports the ANSI SQL-89 standard (hereafter Jet SQL syntax). Jet SQL syntax differs from the ANSI SQL-92 standard in several ways. For example, Jet SQL syntax uses an asterisk (*) for a multiple character wildcard, while ANSI SQL-92 syntax uses a percent sign (%).

For example, Jet SQL syntax for returning all customers from a country or region beginning with the letter U:

SELECT * FROM Customers WHERE Country Like U*

ANSI SQL-92 syntax for returning all customers from a country or region beginning with the letter U:

SELECT * FROM Customers WHERE Country Like U%

Likewise, filter expressions in Access 2002 or later projects use the ANSI SQL-92 standard. If you are migrating previous version Access projects to Access 2002, you should check all your expressions to make sure they are returning intended results.

For example, if you had a macro expression that searched for CustomerName = B*, it no longer finds all customer names beginning with the letter B because the asterisk (*) is not an ANSI SQL-92 wildcard character. To resolve this, revise the macro expression to use the percent sign (%) wildcard character instead: CustomerName = 'B%'.

ShowResult data sets in data access pages

The Jet Database Engine is used to work with the data in an Access (.mdb) database and supports the ANSI SQL-89 standard. However, data access pages in Access databases connect to Jet by using ActiveX Data Objects (ADO) and the Jet OLEDB provider. ADO and OLEDB always use ANSI SQL-92 syntax. Thus, data access pages must use ANSI SQL-92 syntax. This means that a form and a page bound to the same query can return different result data sets.

 
 
Applies to:
Access 2003