O'Reilly logo

Access™ 2007 VBA Programmer's Reference by Armen Stein, Geoffrey Griffith, Rob Cooper, Teresa Hennig

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required