Access™ 2007 VBA Programmer's Reference

6.11. Data Access with DAO

Accessing data is the reason you use databases, and a large proportion of your programming will usually revolve around manipulating those objects that deal with data: queries and recordsets. In this section, you take a detailed look at how to access and manipulate your database data using DAO objects.

6.11.1. Working with QueryDefs

When you build a query with the graphical Query Designer, you are building a QueryDef object in the default Access workspace. When you save the query, you are also appending a reference to it in the QueryDefs collection. You can also build a QueryDef in code, which is one of the purposes of this section.

You can think of permanent (Access workspace) QueryDefs as SQL statements that are compiled the first time they are executed. This is similar in concept to the way code is compiled. Once compiled, permanent queries run marginally faster than their temporary, unsaved counterparts, because Access does not need to compile them before execution. Temporary QueryDefs are useful when you don't need to save them, as when you create their SQL statements during runtime. You would normally build and run SQL statements in line with your code when you need to change its clauses depending on current operating conditions or the value of some variable.

6.11.1.1. Creating a QueryDef

To create a QueryDef, execute the CreateQueryDef method against the Database object.

In Microsoft Access workspaces, if you set a QueryDef's Name property to ...