Understanding the Role of SQL in Access 2003

This chapter describes Structured Query Language (SQL), the grammar of the language, and SQL Server's dialect of ANSI-92 SQL called Transact-SQL (T-SQL). Earlier chapters have demonstrated how Access translates queries you build in its Query Design view into Jet SQL statements. Jet SQL is another SQL dialect that closely resembles T-SQL, but Jet SQL lacks T-SQL's support for views and its extensions for functions, stored procedures, and linked servers. Jet SQL is unique in its support for VBA functions such as CCur() and DatePart() in queries. T-SQL has equivalents to many VBA functions, but the usage syntax differs.

SQL (usually pronounced "sequel" or "seekel," but more properly "ess-cue-ell") is the common language of client/server database management. The principal advantage of SQL is that it's standardized you can use a common set of SQL statements with all SQL-compliant database-management systems. The first U.S. SQL standard was established in 1986 as ANSI X3.135-1986. The current version is ANSI X3.135-1992, usually known as SQL-92. ANSI is an acronym for the American National Standards Institute. X3.135 is the code name for the ANSI subcommittee that's responsible for editing and publishing SQL standards documentation. The corresponding International Standards Organization (ISO) standard is ISO/IEC 9075:1992.

SQL is an application language for relational databases, not a system or programming language. SQL is a set-oriented language, not a procedural language like VBA. ANSI SQL includes neither a provision for program flow control (branching and looping) nor keywords to create data-entry forms and print reports. Publishers of ANSI SQL-compliant RDBMSs are free to extend the language if the basic ANSI commands are supported in accordance with the standards. Unlike standards (called recommendations) for HTML, XML, and other Web-related languages coordinated by the World Wide Web Consortium (W3C), updates to ANSI SQL are few and far between. The latest standard is SQL-99 (called SQL3 during the seven-year standards process), which supports hierarchical, network, and other database models, not just relational databases.

Note

SQL-99 hasn't generated much interest among RDBMS vendors or purchasers. When this book was written, searching the Microsoft Web site with SQL-99, SQL-1999, and similar combinations returned only a few valid hits. According to Microsoft's Richard Waymire, an SQL Server product manager, "...SQL3 was only finalized after most of our development work was done for this [SQL Server 2000] release."

An SQL background helps you understand the query process, and design more efficient Jet SQL and T-SQL queries. You need a basic knowledge of SQL to write subqueries and UNION queries and for any application that uses VBA to generate Recordsets for populating list and combo boxes. Simple examples of Jet SQL and T-SQL are presented in other chapters in this book. These examples demonstrate what occurs behind the scenes when you create a query using either of Access's visual Query by Example (QBE) tools. QBE is the original name for pre-Windows query tools that emulate query-design grids in text-only displays. Almost all graphical query-design tools are based on early QBE techniques.

You probably can use Jet's Query Design window to generate the SQL statements for 90% or more of the queries you need to support conventional Access applications. (You must write UNION and pass-through queries in the SQL window.) The graphical project designer (also called the da Vinci toolset) is likely to cover 75% or so of your query needs for Access Data Projects (ADP); you must write the remaining 25% in T-SQL. T-SQL offers many additional features, such as IF...ELSE and WHILE statements for flow control within queries. Taking advantage of most of the features of SQL Server 2000 requires at least the ability to modify T-SQL statements you create with the project designer.

Tip

Learn SQL by osmosis. Each time you design a query in Jet's Query Design view or the project designer for ADP, open the SQL window or pane and read the underlying SQL statement. The relationship between the SQL statement and graphic query design is evident for simple queries. As you advance to more complex queries with joins and aggregate functions, carefully compare the SQL statement with the contents of the QBE grid. Over time, you'll find that SQL lives up to its original name, SEQUEL Structured English Query Language.