Your SQL queries can be saved as permanent QueryDef objects in the database or created, analyzed, and executed on an ad-hoc basis.
The saved QueryDef object is a particularly important object in a Microsoft Jet database. It’s a convenient way to store and reuse commonly asked questions. After you analyze your business problem and create a query that answers that question, you can save the “question” as a permanent query. From then on, you can ask the question again by rerunning the query.
Each time you prepare a request for information, either by sending an SQL statement as an argument to the OpenRecordset method of a Database object or by saving a QueryDef object in your database, Microsoft Jet runs through a complex series of analysis and optimization steps. When you create a QueryDef object, Microsoft Jet performs a parsing phase (reading and interpreting your SQL statements) and an optimization phase (turning that SQL statement into a plan for the most efficient way to retrieve your answer). When you create a permanent QueryDef object, these steps are performed once. You can then execute the saved QueryDef object either to retrieve the answer you need, or to perform the action you requested. For a full discussion of how Microsoft Jet interprets and executes your queries, see “Query Optimization” later in this chapter.
See Also For a full discussion of how QueryDef objects are created and stored in a Microsoft Jet database file, see Chapter 3, “Data Definition and Integrity.”
The QueryDef object is also important because it can be used, for the most part, as if it were a table in your database. You can prepare a complex, multiple-table join that brings together data from a variety of sources and then summarizes and analyzes that data. But because the query is saved as a permanent QueryDef object, that complexity can be hidden from your application. You only have to know the name of the QueryDef object in order to use it. Depending on the type of query created, the query can be updatable: Any changes made to the Recordset object built from an updatable QueryDef object are automatically reflected in the underlying tables.
The examples in this chapter present only the text of the SQL statements that illustrate various Microsoft Jet query features under discussion. To execute these queries, you must save them as permanent QueryDef objects, or execute them in VBA code.
See Also For information about how to create Recordset objects based on these SQL statements, see Chapter 5, “Working with Records and Fields.”