ACC2002: No Effect When You Programmatically Set ANSI-92 Mode (282398)

MORE INFORMATION

The ANSI Query Mode property allows you to programmatically specify whether Access should use Microsoft SQL Server-compatible (ANSI-92) syntax when running queries in the current database. ANSI-92 mode introduces new syntax features that allow you to create SQL Server-compliant queries. This setting can also be modified on the Tables/Queries tab in the Options dialog box. When you manually change the query mode in the Options dialog box, Access automatically closes the database, compacts it, and then reopens it. If you programmatically set the property, you must close and reopen the database afterwards for the new setting to take effect.

Note that changing this setting may cause existing queries to return unexpected results, or to not run at all. Microsoft recommends that you make a backup copy of the database and compact it before altering the query mode.

Steps to Reproduce the Behavior

1. Open the sample database Northwind.mdb.
2. Press CTRL+G to open the Microsoft Visual Basic Editor.
3. Type the following statement in the Immediate window, and then press Enter:

   Application.SetOption "ANSI Query Mode", True
   					

4. Quit the Visual Basic Editor and return to Access.
5. To confirm that the ANSI-92 Query Mode is turned on, click Options on the Tools menu, and then click Tables/Queries.
   
   Notice that the This database check box under SQL Server Compatible Syntax (ANSI-92) is selected.
6. Create the following query:

   SELECT CategoryID, CategoryName 
   FROM Categories 
   WHERE CategoryName LIKE "B%";
   					

   NOTE: This query uses the ANSI-92-compliant "%" wildcard character instead of the Jet "*" wildcard character.
   
   
7. Save the query as Query1 and then run it.
   
   Notice that zero records are returned, even though one record does match the query's criteria.
8. Close the database and reopen it.
9. Run Query1 again.
   
   Notice that one record is now returned.