Access Database Design and Programming, Second Edition

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.

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.

Running a Query

Recall from Chapter 6, that Microsoft Access supports several types of queries. In particular, a select query returns a recordset, whereas 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 ...