Creating a Permanent QueryDef Object

A QueryDef object is based on an SQL statement. To create a new permanent QueryDef object, you must provide a value for the name argument of the CreateQueryDef method. You can specify the SQL statement in the sqltext argument of the CreateQueryDef method, or in the SQL property of the newly created QueryDef object.

The following code creates a QueryDef object. In this example, dbs is a Database object, strQueryName is the name for the new QueryDef object, and strSQL is the SQL statement on which the QueryDef object is based:

Set qdf = dbs.CreateQueryDef(strQueryName)

' Set SQL property.
qdf.SQL = strSQL

You may initialize the strSQL variable with an SQL statement such as the following, which returns a result set containing total sales for each employee for May 1996:

strSQL = "SELECT Orders.EmployeeID, " & _
	"Sum((UnitPrice * Quantity) - Discount) AS ExtendedPrice " & _
	"FROM Orders INNER JOIN [Order Details] ON " & _
	"Orders.OrderID = [Order Details].OrderID " & _
	"WHERE (((Orders.OrderDate) " & _
	"Between #5/1/96# And #5/31/96#)) " & _
	"GROUP BY Orders.EmployeeID;"

Note that unlike with other collections, such as Properties, Fields, or Indexes, it’s not necessary to explicitly append the QueryDef object to the QueryDefs collection of the database. Simply assigning values for the name argument and the SQL property immediately creates the QueryDef object.

You can immediately use this new QueryDef object in your application. You can create a Recordset object from it, join it to another query or table, base a report on it, and so on.