DAO Querydef: Using Querydefs

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

This article explains how to use objects. Topics covered include:

• Creating a querydef

• Saving a querydef (in Microsoft Jet (.MDB) databases only)

• Opening a previously saved querydef

• Using a temporary querydef

• Creating a recordset from a querydef

• Directly executing a query (an action query or an SQL pass-through query that doesn't return records)

For a general understanding of querydefs and their uses, see the topic "QueryDef Object" in DAO Help.

Creating a Querydef

Creating a querydef, whether you save it in the database or use it as a temporary object, requires specifying the SQL statement that defines the query and setting any needed properties of the querydef. If the querydef represents a parameterized query, you also need to create the parameters and their corresponding fields and later set their values.

****Tip   ****You can also set and get field values and parameter values (in a recordset) dynamically, without using a querydef. See the article DAO Recordset: Binding Records Dynamically.

Creating a new MFC object creates the underlying DAO querydef object.

To create a querydef

1. Construct a object.

2. Call the querydef object's member function.

   In the Create call, pass a user-defined name for the querydef and a string that contains the SQL statement on which the querydef is based. While you can define the SQL string for a recordset with AppWizard or ClassWizard, you must write the SQL string for a querydef yourself. (You usually use class CDaoQueryDef directly rather than deriving your own querydef classes from it.)

3. Save the querydef object in the database by calling its member function, unless you want to work with a temporary (unsaved) querydef. (See Using a Temporary Querydef.)

4. Either create a recordset based on the querydef or call the querydef object's member function.

Close the querydef when you finish with it. Call its member function. For more information, see the detailed instructions under in the Class Library Reference.

Querydef objects have several properties you can set — primarily for querydefs to be used with ODBC data sources.

To set a querydef's properties (primarily for ODBC)

1. Create the querydef, using a object, as described above.

2. Call any of the following member functions: , , , , .

3. Save the querydef in the QueryDefs collection by calling , unless you want to use the querydef as a temporary object. (See Using a Temporary Querydef.)

4. Use the querydef.

You can use SetName and SetSQL for a querydef based on any kind of database. You can call these member functions at any time to rename the querydef object or to respecify its SQL statement. SetReturnsRecords applies only to SQL pass-through queries. The other functions apply only to ODBC data sources.

After creating a querydef, you will usually want to save it in the database by appending it to the QueryDefs collection. See Saving a Querydef. The alternative is to use the querydef as a temporary object. See Using a Temporary Querydef. You can’t use the querydef unless you correctly create it as a temporary querydef or you append it to the collection.

Once created, use the querydef to create recordsets or to execute action queries or SQL pass-through queries. For information about action queries and SQL pass-through queries, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries.

Saving a Querydef

A saved querydef persists in its database (.MDB only), stored there along with the database's tables and data. You can think of a saved query as a compiled SQL statement — when you run the query, it executes faster than a standard new query because the database engine doesn't have to compile the SQL statement before executing it.

****Tip   ****The easiest way to create a querydef is to do it in Microsoft Access. Open your target .MDB database, create querydefs, and save them in the database. Then you can use the querydefs in your code.

To save a querydef

1. Create the querydef as described under Creating a Querydef.

2. Call for the object.

Appending the querydef object to the database's QueryDefs collection makes the object persistent between database engine sessions. You can open and run the query, or modify it, at any time. Other users of your database can use the querydef as well.

The alternative to saving a querydef is using it as a temporary object.

Opening a Previously Saved Querydef

Once you’ve saved a querydef in a database’s QueryDefs collection, you can open it at any time and run its query, either by creating a recordset or by calling .

To open a saved querydef

1. Construct a object.

2. Call its member function.

   In the Open call, pass the user-defined name under which the querydef was stored.

Using a Temporary Querydef

A temporary querydef object has the following characteristics:

• It is never appended to the QueryDefs collection in the database, unlike a saved querydef.

• It is created by passing either NULL or an empty string for the querydef’s name.

****Note   ****MFC differs from the underlying DAO implementation in the way querydefs are appended to the collection. In DAO, a newly created querydef (provided you give it a name) is automatically appended to the QueryDefs collection. In MFC, you must explicitly call .

Saved querydefs are accessible to other users of your database (who have the appropriate permissions, if security is in effect). Temporary querydefs are not accessible to other users. In some cases, you might want to create a querydef and use it without storing it. For example, you might want to use querydef parameters but not want to save the querydef for reuse.

Whether a querydef is temporary or not depends on what you pass in the lpszName parameter to . Querydefs can be in one of the states listed in the following table.

QueryDef States and Their Meanings

The following procedure explains how to create temporary querydefs.

To create a temporary querydef

1. Construct a object.

2. Call its member function, passing NULL or an empty string (“”).

3. Don't call .

You can still use a temporary querydef to create recordsets or to execute action queries or SQL pass-through queries.

Creating a Recordset from a Querydef

The most common way to use a querydef is to base a recordset on it. The recordset inherits the querydef's SQL statement.

To create a recordset from a querydef

1. Create a saved or temporary querydef as described in Creating a Querydef, or open a previously saved querydef.

2. Construct a object.

3. Call the recordset object's member function, passing a pointer to your querydef object.

Calling Open runs the query. For a more detailed discussion, see the article DAO Recordset: Creating Recordsets.

You can create any number of recordsets from the same querydef object. They will all have the same SQL statement unless you change the querydef's SQL statement between creating recordsets.

For related information, see the article DAO Queries.

Executing a Querydef

Not all queries return records. Queries that don't return records include:

• Action queries, which update data or alter the database's structure.

• SQL pass-through queries, which pass the SQL statement to the back-end DBMS without processing it in the Microsoft Jet database engine.

To execute such queries, you use a querydef rather than a recordset. For more information about action queries and SQL pass-through queries, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries.

To directly execute a query that doesn't return records

1. Create a saved or temporary querydef as described in Creating a Querydef.

2. Call the querydef's Execute member function.

For more information about executing queries, see in the Class Library Reference and the topic "Execute Method" in DAO Help.

What do you want to know more about?

• DAO Querydef

• DAO Recordset

• DAO Queries

• DAO Querydef: Action Queries and SQL Pass-Through Queries

See Also   DAO: Where Is...