DAO Querydef

  • Article

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

This article describes “querydefs” and the key features of the MFC class. For task-oriented information, see the article DAO Querydef: Using Querydefs. For an understanding of the DAO querydef object underlying each MFC CDaoQueryDef object, see the topic "QueryDef Object" in DAO Help.

Topics covered include:

  • Querydef: definition

  • Querydef uses

  • Querydef parameters

  • Querydefs and DAO collections

  • Further reading about querydefs

Querydef: Definition

A DAO querydef, represented in MFC by a object, is a query definition. The object defines the SQL statement for a query and provides operations for executing the query, for saving it in the database for reuse, for parameterizing the query, and more.

For information about specifying a query with SQL, see the article DAO Queries.

Saved queries are advantageous because you can keep frequently used queries, especially complex ones, for easy reuse later. For information about saving querydefs in a database, see the article DAO Querydef: Using Querydefs.

****Tip   ****If you are working with Microsoft Jet (.MDB) databases, the easiest way to create a querydef is to do it in Microsoft Access. Open your target database, create querydefs, and save them in the database. Then you can use the querydefs in your code.

Querydef Uses

Querydef objects have two primary uses, corresponding to two ways to run the query:

  • Creating recordsets, which you then open to run the query.

  • Directly executing queries that don't return records. These include action queries and some SQL pass-through queries (those that return no records).

For information about these querydef uses, see the article DAO Querydef: Using Querydefs. For information about action queries and SQL pass-through queries, see the article DAO Querydef: Action Queries and SQL Pass-Through Queries.

QueryDef Parameters

Sometimes you'd like to be able to select records using information you've calculated or obtained from your user at run time. Parameterized queries let you pass such information at run time.

A query parameter is an element containing a value that you can change to affect the results of the query. For example, a query returning data about an employee might have a parameter for the employee's name. You can then use one querydef object to find data about any employee by setting the parameter to a specific name before running the query. This has two valuable effects:

  • It can result in better execution speed, particularly on the second and subsequent requeries.

  • It lets you build a query at run time, based on information not available to you at design time, such as information that you must obtain from the user or information that you must calculate.

****Important   ****In DAO, the parameter names are exposed rather than only the positions as in ODBC. While ODBC does allow named parameters, users of the MFC ODBC classes will be more familiar with using positional parameters.

For more information about DAO parameters, see the following topics in DAO Help:

  • Parameter Object, Parameters Collection Summary

  • Creating Parameter Queries with DAO

  • PARAMETERS Declaration (SQL)

For more information about using parameterized queries, see the article DAO Queries: Filtering and Parameterizing Queries.

QueryDefs and DAO Collections

Each DAO database object maintains a QueryDefs collection — a collection of all saved querydefs in the database. Each querydef object maintains two collections of its own:

  • Parameters.  All defined parameters for the query.

  • Fields.  The fields in one or more tables that correspond to the parameters. For example, an Employee Name field corresponds to an Employee Name parameter.

MFC objects don't store a representation of a DAO collection. Instead, MFC accesses the collection through the underlying DAO object. For more information, see the article DAO Collections.

MFC also doesn't provide a C++ class to represent every DAO object. In particular, there is no MFC parameter object or field object. You work with a querydef's parameters and fields through member functions of class . For more information, see the article DAO Queries: Filtering and Parameterizing Queries.

Further Reading About Querydefs

For more information about querydefs in MFC, see the following additional articles (in the recommended reading order):

See Also   DAO: Where Is..., DAO Recordset