Access 2003 VBA Programmer's Reference

6.10. Data Access with DAO

Accessing data is the reason we 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, we'll take a detailed look at how to access and manipulate your database data using DAO objects.

6.10.1. Working with QueryDefs

When you build a query with the graphical Query Designer, you are building a QueryDef object in the default Jet 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.

In an ODBCDirect workspace, you can only create temporary QueryDefs, which cease to exist when you close the workspace.

You can think of permanent (Jet 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 Jet 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.10.1.1. Creating a QueryDef

To create a QueryDef, execute the CreateQueryDef ...