Microsoft KB Archive/282398

= ACC2002: No Effect When You Programmatically Set ANSI-92 Mode =

Article ID: 282398

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2002 Standard Edition

-



This article was previously published under Q282398



Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).



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



CAUSE
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.



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



STATUS
This behavior is by design.



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
 Open the sample database Northwind.mdb. Press CTRL+G to open the Microsoft Visual Basic Editor.  Type the following statement in the Immediate window, and then press Enter: Application.SetOption &quot;ANSI Query Mode&quot;, True  Quit the Visual Basic Editor and return to Access. 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.</li>  Create the following query: SELECT CategoryID, CategoryName FROM Categories WHERE CategoryName LIKE &quot;B%&quot;; NOTE: This query uses the ANSI-92-compliant &quot;%&quot; wildcard character instead of the Jet &quot;*&quot; wildcard character.

</li> 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.</li> Close the database and reopen it.</li> Run Query1 again.

Notice that one record is now returned.</li></ol>

<div class="references_section">