Microsoft Jet database engine has sophisticated query and optimization capabilities that are unmatched by other desktop database engines in its class. These features include updatable views, heterogeneous joins, and the ability to work seamlessly with a wide variety of industry-standard database formats.
The Microsoft Jet query engine is designed to accept user requests for information or action in the form of Structured Query Language (SQL) statements. Microsoft Jet parses, analyzes, and optimizes these queries, and either returns the resulting information in the form of a Recordset object or performs the requested action.
Although Microsoft Jet borrows many query techniques from client/server relational database management systems (DBMSs) such as Microsoft SQL Server, it remains a file-server database. All queries are processed on individual workstations running copies of a host application, such as Microsoft Access, or a custom application created by using a tool, such as Microsoft Visual Basic. Microsoft Jet doesn’t act as a true database server, such as SQL Server, that process data requests independently of the application requesting data. However, Microsoft Jet can send queries to SQL Server or other ODBC database servers for processing.
To understand how Microsoft Jet parses, optimizes, and processes queries, it’s important to understand the distinction between a client/server DBMS and a file-server system.
Understanding how the Microsoft Jet query engine works will give you a head start in designing your application to take advantage of the unique strengths and features of Microsoft Jet.
Getting Answers to Your Questions
Updating Your Data with Queries
Microsoft Jet Query Engine Overview
You can use the code examples in this chapter to help you understand the concepts discussed, or you can modify them and use them in your own applications.
The code examples are located in the JetBook\Samples subfolder on the companion CD-ROM. The code examples for Microsoft Access 97 are in JetSamples.mdb, and the corresponding code examples for Microsoft Visual Basic version 5.0 and other applications that support Visual Basic for Applications (VBA) are referenced in JetSamples.vbp. Both JetSamples.mdb and JetSamples.vbp use tables and queries in NorthwindTables.mdb, also located in the JetBook\Samples subfolder.
To use the code examples, copy the sample files to your hard disk. Be sure to copy NorthwindTables.mdb as well so that you can use the code examples to work with data.
This chapter includes SQL statements that you can use in Visual Basic or run from SQL view of the Query window in Microsoft Access. To make it easier to use the SQL statements in this chapter in code, all SQL statements are saved as string constants in the modules for Chapter 4. In addition, each SQL statement is saved as a query in the NorthwindTables database.
See Also For more information about copying and using the code examples from the companion CD-ROM, see “Using the Companion CD-ROM” in the Preface.
Note The examples in this chapter show strings within an SQL statement delimited by single quotation marks: 'string'. You can use single quotation marks to delimit a string in Visual Basic or in SQL view of the Query window in Microsoft Access. However, if the string itself contains an apostrophe, an error occurs.
Pairs of double quotation marks (""string"") are optimal delimiters when you’re including an SQL statement in Visual Basic code. You can also use Chr(34) to return a string containing a pair of double quotation marks; for example, the expression Chr(34) & Chr(34) & "string" & Chr(34) & Chr(34) concatenates to ""string"".
If you’re running an SQL statement from SQL view in Microsoft Access, you may want to use a single set of double quotation marks to delimit a string: "string". If you use a pair of double quotation marks, Microsoft Access returns an error.