There appears to be no immediate effect when you programmatically set the ANSI Query Mode property.

This behavior occurs because Microsoft Access determines the query mode when the database is opened. Therefore, any changes to this property do not take effect until the database is closed and then reopened.

To resolve this issue, close the database and reopen it.

This behavior is by design.

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.

For more information about ANSI-92 SQL mode, click Microsoft Access Help on the Help menu, type ansi-92 sql mode in the Office Assistant or the Answer Wizard, and then click Search to view the topic.