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


The information in this article applies to:

  • Microsoft Access 2000
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.

MORE INFORMATION

Steps to Reproduce the Behavior

  1. Open Access 2000, and then create a database named DB1.mdb in the Access 2000 format.
  2. Create a new table named Table1 with one Text field named Text1. It does not matter whether or not you add a primary key.
  3. Save the new table, and then open it in Datasheet view. Type the following records:
       Text1
   -----
   abc
   ada
   aaa
   bcd
   efg
  4. 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 "a*";

  5. Close and save the query as Query1.
  6. On the Tools menu, click Options.
  7. 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.
  8. Open the Query1 query again in SQL view.
  9. Change the wildcard character from an asterisk (*) to a percent sign (%). This is ANSI-92 syntax.
  10. Close and save the query.
  11. Close DB1.mdb, and then open it in Access 2000.
  12. Note that the Query1 query is still listed in the Queries group of the Database window.

REFERENCES

For more information about ANSI-92 query mode, click Microsoft Access Help on the Help menu, type about ansi sql query mode in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
Modification Type:MajorLast Reviewed:11/24/2004
Keywords:kbnofix kbprb KB274385
©2004 Microsoft Corporation. All rights reserved.