Some properties of a QueryDef object can’t be set in code. For example, the Type property is a read-only property that indicates the type of SQL statement used to create the QueryDef object. The value of the Type property may be one of dbQSelect, dbQCrosstab, dbQMakeTable, and so on, depending on the value of the SQL property of the QueryDef object.
The ReturnsRecords property is always created when you add a new QueryDef object to the database, but it has meaning only in the context of an SQL pass-through query. If you are creating a pass-through QueryDef object, you should specify whether the query performs an action (such as DELETE or INSERT), or whether it returns its result as a set of records. Microsoft Jet uses this setting to determine whether to create a Recordset object to contain the results of the pass-through query when it’s executed. It’s also used to indicate whether the QueryDef object can be used in an SQL SELECT statement to join to another table or QueryDef object.
Chapter 2, “Introducing Data Access Objects,” explained that a QueryDef object can be both a permanent object stored in the database and a temporary object created and executed within code. Certain properties, such as RecordsAffected, have a value only after the QueryDef object is executed. For example, the following code creates a permanent QueryDef object that deletes records from the Orders table, and then executes the QueryDef object. After the QueryDef object executes, the RecordsAffected property contains the number of records that were deleted. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database
Dim qdf As QueryDef
Dim lngReturn As Long
Set dbs = OpenDatabase(strDbPath)
' Delete any existing QueryDef object with same name.
On Error Resume Next
dbs.QueryDefs.Delete "DeleteOldOrders"
On Error GoTo 0
Set qdf = dbs.CreateQueryDef("DeleteOldOrders")
qdf.SQL = "DELETE FROM Orders WHERE OrderDate < #9/1/94#;"
' Execute query.
qdf.Execute
' Return number of affected records.
lngReturn = qdf.RecordsAffected