Set ANSI SQL query mode

Caution   Be careful when changing the ANSI SQL query mode. Existing queries may not run or may return unexpected results. The range of data types, reserved words, and wildcard characters are different in each query mode.

Set the ANSI SQL query mode for the current database

1. On the Tools menu, click Options, and then click the Tables/Queries tab.
2. Do one of the following:
   • Select the This database check box to set the query mode to ANSI-92 SQL.
   • Clear the This database check box to set the query mode to ANSI-89 SQL.

Note   Microsoft Access closes, compacts, and then re-opens the database before the new setting takes effect.

Set the ANSI SQL query mode default for new databases

ANSI-89

1. On the Tools menu, click Options, and then click the Tables/Queries tab.
2. Clear the Default for new databases check box.

ANSI-92

1. On the Tools menu, click Options, and then click the Advanced tab.
2. Select Access 2002 from the Default File Format list box.
3. Click the Tables/Queries tab.
4. Select the Default for new databases check box.

Notes

• ANSI-89 is the default setting for a new Microsoft Access database in 2002 and 2000 file format. You cannot set the SQL query mode new database default to ANSI-92 in 2000 file format because the option is disabled; ANSI-89 is the only query mode setting available for a database in Access 2000 file format.
• Although you can set the query mode to ANSI-92 in a replica, Access will ignore it. You can only set this option in the Design Master of a replica set.