About ANSI SQL query mode (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

Some of the content in this topic may not be applicable to some languages.

You can create SQL queries in one of two ANSI SQL query modes (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.):

In Microsoft Access 2000 using ADOX (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.), you could programmatically create queries that used ANSI-92 SQL syntax. However, any queries you created were not visible in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.) because there was no option to set this mode in the user interface. Now in Access 2002 or later, you can set the ANSI SQL query mode through the user interface for the current database and as the default setting for new databases.

ShowWhy use ANSI-92 SQL?

You may want to use ANSI-92 SQL for the following reasons:

ShowWhy you should avoid mixing queries created under different ANSI SQL query modes

The two ANSI SQL query modes (ANSI SQL query mode: One of two types of SQL syntax: ANSI-89 SQL (also called Microsoft Jet SQL and ANSI SQL), which is the traditional Jet SQL syntax; and ANSI-92 SQL, which has new and different reserved words, syntax rules, and wildcard characters.), ANSI-89 and ANSI-92, are not compatible. When you create a Microsoft Access database (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.), you need to decide which query mode you are going to use, because mixing queries (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.) created in both query modes could produce runtime errors or unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

ShowExample of a query using wildcard characters

A query that uses wildcard characters in a criteria expression can produce different results under each query mode. For example, if you run:

  • An ANSI-89 SQL query in a database set to ANSI-92 query mode, such as:

SELECT * FROM Customers WHERE Country Like 'U*'

It returns all customers from a country/region named "U*", not all countries/regions beginning with the letter "U", because the asterisk (*) is not a wildcard character in ANSI-92 SQL.

  • An ANSI-92 SQL query in a database set to ANSI-89 query mode, such as:

SELECT * FROM Customers WHERE Country Like 'U%'

It returns all customers from a country/region named "U%", not all countries/regions beginning with the letter "U", because the percent sign (%) is not a wildcard character in ANSI-89 SQL.

ShowExample of a query with a duplicate field and alias name

If a query uses an alias that is the same as a base column name and you create a calculated field using the ambiguous name, the query will produce different results under each query mode. For example, if you run the following query:

SELECT Orders.OrderID AS Freight, Orders.Freight, [Freight]*1.1 AS [New Freight] FROM Orders;

Under ANSI-92 SQL, Access calculates the New Freight value using the Freight field, and therefore increases each Freight by 10%. Under ANSI-89 SQL, Access calculates the New Freight value using the Freight alias, and therefore increases each OrderID by 10%.

ShowHow to avoid problems caused by mixing queries under different ANSI SQL query modes in the same database

In general, avoid doing the following to prevent problems caused by mixing queries created under different ANSI SQL query modes:

  • Converting an Access database set to ANSI-92 SQL query mode from 2002 - 2003 file format to 2000 or 97 file format. ANSI-92 SQL query mode is only supported in the 2002 - 2003 file format.
  • Changing the ANSI SQL query mode for the current database after you've created one or more queries. If you must do this, retest the existing queries to ensure that they still run or produce expected results, and rewrite the queries if necessary.
  • Importing queries created under one ANSI SQL query mode into an Access database set to another mode, or exporting queries created under one ANSI SQL query mode to an Access database set to another mode.
  • Changing the ANSI SQL query mode in an existing application. This could break your application and require rewriting your application.

In Access 2000, you can only programmatically change the ANSI SQL query mode and any queries created under ANSI-92 mode were hidden in the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.). In Access 2002 or later, you or a user can change ANSI SQL query mode through the user interface and ANSI-92 queries are no longer hidden in the Database window, so you should help prevent accidental or intentional changes to the ANSI SQL query mode of your application by helping protect your code and helping prevent the changing of the query mode through the application's user interface.

  • Using ambiguous aliases and column names. To avoid confusion, ensure that aliases and column names are always unique in an SQL statement.

ShowThe ANSI SQL query mode default for new Access databases in 2002 - 2003 and 2000 file formats

  • ANSI-89 is the default setting for a new Access database in 2002 - 2003 and 2000 file format.
  • You cannot set the SQL query mode new database default to ANSI-92 in 2000 file format because the option is disabled; ANSI-89 is the only query mode setting available for a database in Access 2000 file format.
 
 
Applies to:
Access 2003