Introduction to Structured Query Language - Database Processing: Fundamentals, Design, and Implementation - page 46

The Object
Designers button

The SQL Server
Compatible Syntax
(ANSI 92) option
controls the use of
SQL-89 versus
SQL-92 syntax in
Access queries

Use this check box to
use SQL-92 syntax
in just the open
database

Use this check box to
use SQL-92 syntax
when new databases
are created

Figure 2-6
The Microsoft Access 2013
Options Object Designers
Page

application development for Microsoft SQL Server, which supports newer SQL standards. To set
the option in Microsoft Access 2013, click the File command tab and then click the Options com-
mand to open the Access Options dialog box. In the Access Options dialog box, click the Object
Designers button to display the Access Options Object Designers page, as shown in Figure 2-6.
As shown in Figure 2-6, the SQL Server Compatible Syntax (ANSI 92) options control
which version of SQL is used in a Microsoft Access 2013 database. If you check the This
database check box, you will use SQL-92 syntax in the current database. Or you can check
the Default for new databases check box to make SQL-92 syntax the default for all new
databases you create. When you click the OK button to save the changed SQL syntax option,
the SQL-Syntax Information dialog box shown in Figure 2-7 will be displayed. Read the infor-
mation, and then click the OK button to close the dialog box.
Unfortunately, very few Microsoft Access users or organizations using Microsoft Access
are likely to set the Microsoft Access SQL version to the SQL-92 option, and, in this chapter,
we assume that Microsoft Access is running in the default ANSI-89 SQL mode. One advantage
of doing so is it will help you understand the limitations of Microsoft Access ANSI-89 SQL and
how to cope with them.
In the discussion that follows, we use “Does Not Work with Microsoft Access ANSI-89 SQL”
boxes to identify SQL commands and SQL clauses that do not work in Microsoft Access ANSI-
89 SQL. We also identify any workarounds that are available. Remember that the one permanent
workaround is to choose to use the SQL-92 syntax option in the databases you create!
Nonetheless, two versions of the Microsoft Access 2013 Cape Codd Outdoor Sports database
are available at www.pearsonhighered.com/kroenke for your use with this chapter. The Microsoft
Access database file named Cape-Codd.accdb is set to use Microsoft Access ANSI-89, whereas

Figure 2-7
The Microsoft Access 2013
SQL-Syntax Information
Dialog Box