Expert Access™ 2007 Programming

Chapter 6. Using SQL to Retrieve Data and Manipulate Objects

Structured Query Language — more commonly referred to as SQL — is the language used by relational databases to define queries against a database. The database engine accepts requests created using the SQL language and returns a recordset or executes actions based on the values in the SQL request.

In this lengthy chapter, we examine the following:

• How to create select queries to return data from a database

• How to create action queries to add, modify, and remove data from a database

• How to create and work with subqueries

• How to create and work with crosstab and union queries

• How to use Data Definition Language queries to create and modify schema

• Some of the subtleties of working with ANSI-92 compatibility mode

The Microsoft Access database engine (commonly referred to as ACE) was introduced with Microsoft Access 2007. ACE is an update to the venerable Microsoft Jet database engine, which has powered all previous versions of Access, and which is shipped with every version of Microsoft Windows since Windows 98. ACE is completely backward compatible with Jet, and adds support for the multi-valued lookup fields (often referred to as complex data) introduced in Access 2007. With the exception of syntax added to interact with multi-valued lookup fields, the SQL syntax is identical for both Jet and ACE, and all of the sample queries presented in this chapter will work on either Jet or ACE unless we mention otherwise.

By default, both the ...