Creating Temporary Queries with DAO

You can create temporary QueryDef objects that you can use in code. You create a temporary QueryDef object in your code by using a zero-length string ("") in place of the query name. You can then execute the QueryDef object and close it. Temporary QueryDef objects are well-suited to creating and running update, parameterized, and pass-through queries.

The following example shows how you may use a temporary QueryDef object to execute an action query that updates data as defined by an SQL statement passed to the procedure in the strSQL variable. In this example, strDbPath is the path to the database, and strSQL is an SQL statement for an update query:

Dim dbs As Database
Dim qdf As QueryDef

Set dbs = OpenDatabase(strDbPath)
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute
qdf.Close

For example, you may supply the following values for strDbPath and strSQL:

strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb"
strSQL = "UPDATE Products INNER JOIN Suppliers " & _
	"ON Products.SupplierID = Suppliers.SupplierID " & _
	"SET Products.UnitPrice = [UnitPrice]*1.1 " & _
	"WHERE Suppliers.CompanyName=""Exotic Liquids"";

This update query increases the unit price of all Exotic Liquids products in the Products table by 10 percent. After you run the update query, close the temporary QueryDef object because it’s no longer needed.

Note that if the SQL statement specified a select query rather than an update query, you need to open a Recordset object on the QueryDef object in order to view the result set. For example, suppose you specified a value for strSQL as follows:

strSQL = "SELECT ProductName, UnitPrice FROM Products " & _
	"WHERE UnitPrice >= 40 ORDER BY ProductName;"

Rather than executing the temporary QueryDef object, you can open a Recordset object on it, then close the temporary QueryDef object and manipulate the Recordset object, as follows:

Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset, fld As Field

Set dbs = OpenDatabase(strDbPath)
Set qdf = dbs.CreateQueryDef("", strSQL)
Set rst = qdf.OpenRecordset
qdf.Close
Do Until rst.EOF
	For Each fld In rst.Fields
		Debug.Print fld.Value
	Next fld
	rst.MoveNext
Loop