It’s not always necessary to create a permanent QueryDef object in order to execute an SQL statement, or to create a Recordset object in code. For example, you can pass an SQL string directly to the Execute method of a Database object in order to delete or insert records in a table. In this example, strDbPath is the path to the NorthwindTables database:
Dim dbs As Database Set dbs = OpenDatabase(strDbPath) dbs.Execute "DELETE FROM Orders WHERE OrderDate < #9/1/94#;"
Microsoft Jet automatically parses the SQL statement and creates a temporary QueryDef object in the database, executes the temporary QueryDef object, and then deletes the QueryDef object.
The advantage to this method is that you avoid adding many permanent QueryDef objects to the database. The code becomes more readable because you don’t have to refer to an external tool such as Microsoft Access to find out what the QueryDef object is supposed to accomplish.
However, when you use a temporary QueryDef object, you can’t take advantage of certain properties of a permanent QueryDef object, such as the RecordsAffected property. To work around this problem, you can use the RecordsAffected property on the Database object. In addition, there is a slight decrease in performance each time the temporary QueryDef object is created and deleted; it must be parsed and “compiled” into its internal storage format each time this code is executed. A permanent QueryDef object is parsed and compiled once, when it’s first executed, so subsequent executions can begin more quickly. Also, because the temporary QueryDef object is not named, it can’t be used in a SELECT statement to join to another table or QueryDef object.
For multiuser databases, there are advantages to creating a QueryDef object that is not permanently saved in a database. You may want to do this, for example, if you are building up a SELECT query on the fly in code based on user input. This way, you don’t need to create uniquely named QueryDef objects in a multiuser database. To create a temporary QueryDef object, you simply supply a zero-length string ("") for the name argument of the CreateQueryDef method. You can specify the SQL statement in the sqltext argument of the CreateQueryDef method or as the SQL property setting of the newly created QueryDef object.
A common situation in which it’s to your advantage to create a temporary QueryDef object is when you have to execute it repeatedly, but it’s not needed on a permanent basis. The following example creates a temporary parameter QueryDef object that repeatedly builds a Recordset object until the user clicks Cancel. In this example, strDbPath is the path to the database:
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim strCust As String, strMsg As String
Set dbs = OpenDatabase(strDbPath)
' Create temporary QueryDef object.
Set qdf = dbs.CreateQueryDef("")
' Construct SQL string.
qdf.SQL = "PARAMETERS [Which CustomerID] Text; " & _
"SELECT Count(*) AS RecordCount, Orders.CustomerID " & _
"FROM Orders GROUP BY Orders.CustomerID " & _
"HAVING Orders.CustomerID Like [Which CustomerID];"
' Initialize variable.
strCust = " "
' Prompt user for input.
Do While Len(strCust) > 0
strCust = InputBox("Enter first letter or letters of customer name.")
If Len(strCust) > 0 Then
' Append wildcard character to input.
strCust = strCust & "*"
' Provide value for paramter.
qdf![Which CustomerID] = strCust
' Open Recordset object on QueryDef object.
Set rst = qdf.OpenRecordset
strMsg = strMsg & vbCrLf & "Orders for Customer(s) like " & strCust & ": "
' Loop through Recordset object to append values to string.
Do Until rst.EOF
strMsg = strMsg & vbCrLf & rst!CustomerID & ": " & rst!RecordCount
rst.MoveNext
Loop
strMsg = strMsg & vbCrLf
End If
Loop
' Display string if it is not empty.
If Len(strMsg) > 0 Then
MsgBox strMsg
End If
If the action specified by the QueryDef object is going to be performed only once, there is no need to create a temporary QueryDef object. You can pass the SQL statement directly to the Execute method of a Database object, for example, or use the SQL SELECT statement as the argument to an OpenRecordset method. In the following example, strDbPath is the path to the database and strTableName is the name of the table whose records are to be counted:
Dim dbs As Database
Dim rst As Recordset
Set dbs = OpenDatabase(strDbPath)
Set rst = dbs.OpenRecordset("SELECT Count(*) AS RecordCount FROM " _
& strTableName & ";")