Programming DAO: Data Definition Language - Access Database Design and Programming - page 240

Figure 15-3. Relationships window after running exaRelations

15.6 Creating a QueryDef

Creating a QueryDef object is done using the CreateQueryDef method. The syntax is:

Set QueryDefVar = DatabaseVar .CreateQueryDef _
([ QueryDefName ][, SQLText ])

where QueryDefName is the name of the new QueryDef object and SQLText is a string
expression that constitutes a valid Access SQL statement.

15.6.1 Notes

If you include QueryDefName , the QueryDef is automatically saved (appended to
the appropriate QueryDefs collection) when it is created. The Name property and
the SQL property of a QueryDef can be changed at any time.

•

You can create a temporary QueryDef , which is not appended to a collection, by
setting the QueryDefName property to a zero-length string (““). You cannot
change the name of a temporary QueryDef .

•

If you omit the SQLText argument, you can define the QueryDef by setting its
SQL property before or after you append it to a collection.

•

To remove a QueryDef object from a QueryDefs collection, use the Delete
method.

•

15.6.2 Running a Query

Recall from Chapter 6 that Microsoft Access supports several types of queries. In
particular, a select query returns a recordset. An action query does not return a recordset,
but rather takes action on existing data, such as making a new table, deleting rows from a
table, appending rows to a table, or updating the values in a table.

If a QueryDef object represents an action query, then we can use its Execute statement to
run the query. If the QueryDef object represents a select query, then we can open the
corresponding result table (recordset) using the OpenRecordset method on the QueryDef