Microsoft KB Archive/274385

= Queries that are changed to ANSI-92 syntax still appear in a database that is opened in Access 2000 =

Article ID: 274385

Article Last Modified on 11/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q274385



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

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



SYMPTOMS
After you add ANSI-92 syntax to a query in a database that is also in ANSI-92 mode, the query still appears when you open the database in Access 2000. The query may or may not run correctly.



CAUSE
When you switch a database to ANSI-92 mode, any queries that you create from that point on are flagged as ANSI-92 queries the first time that they are saved. These queries do not appear in the Database window when you open the database in Access 2000.

However, if you switch a database that contains existing queries to ANSI-92 mode, and then change one of the existing queries to contain ANSI-92 syntax, that query is not flagged as an ANSI-92 query. When you open the database in Access 2000, the changed query still appears in the Database window. Additionally, because Access 2000 is not ANSI-92 compliant, the query may not work as expected.



RESOLUTION
To prevent existing queries that you have changed to ANSI-92 syntax from appearing in the Access 2000 user interface, follow these steps in Access 2000:
 * 1) Enable the SQL Server Compatible Syntax (ANSI 92) query mode.
 * 2) Open the existing query in SQL View.
 * 3) Make the ANSI-92 changes to the SQL statement.
 * 4) Select and copy the entire SQL statement.
 * 5) Close the query.
 * 6) Create a new query in SQL View, and then paste the SQL statement into the SQL Text Editor.
 * 7) Save the query with a temporary name, and then run it to verify that it works correctly.
 * 8) Delete the old query, and then rename the new one with the appropriate name.

The new query is flagged as ANSI-92 and does not appear in the Database window when you open the database in Access 2000.



Steps to Reproduce the Behavior
 Open Access 2000, and then create a database named DB1.mdb in the Access 2000 format. Create a new table named Table1 with one Text field named Text1. It does not matter whether or not you add a primary key.  Save the new table, and then open it in Datasheet view. Type the following records:   Text1 -  abc ada aaa bcd efg  Close the table, and then create a new query in SQL View. Type or paste the following statement in the SQL Text Editor:

SELECT Text1 FROM Table1 WHERE Text1 Like &quot;a*&quot;;

 Close and save the query as Query1.</li> On the Tools menu, click Options.</li> In the Options dialog box, click the Tables/Queries tab. Under SQL Server Compatible Syntax (ANSI 92), click to select the This database check box, and then click OK. When prompted, click OK to convert the SQL syntax.</li> Open the Query1 query again in SQL view.</li> Change the wildcard character from an asterisk (*) to a percent sign (%). This is ANSI-92 syntax.</li> Close and save the query.</li> Close DB1.mdb, and then open it in Access 2000.</li> Note that the Query1 query is still listed in the Queries group of the Database window.</li></ol>

<div class="references_section">