DAO Queries

This article explains what a query is and how to create and run one using the MFC DAO classes. Topics include:

Query: definition
Querydefs and recordsets
Creating a query with a querydef
Creating a query with a recordset

Query: Definition

A query is a formalized instruction to a database to either return a set of records or perform a specified action on a set of records as specified in the query. For example, the following SQL query statement returns records:

SELECT [Company Name] FROM Publishers WHERE State = "NY"

You can create and run select, action, crosstab, parameter, and other queries using the MFC DAO classes. Queries are expressed with Structured Query Language (SQL) statements like the one shown above. The most common query type is the SELECT query. For a list of other query types, see the topic "Type Property" in DAO Help, under Settings and Return Values listed for querydef objects.

How to Write SQL Statements

For general information on writing SQL queries, see the following topics in DAO Help:

Querying a Database with SQL in Code
Building SQL Statements in Code

Syntax of SQL Used with DAO

For a description of the SQL syntax used by DAO, see the topic "Comparison of Microsoft Jet Database Engine SQL and ANSI SQL" in DAO Help. For SQL syntax specific to your target database, see the documentation for your DBMS. For a list of additional topics on SQL in DAO Help, see the article DAO Queries: SQL for DAO.

Querydefs and Recordsets

You can work with queries in two ways:

Use a DAO querydef object ? the corresponding MFC object is represented by class CDaoQueryDef.
Use a DAO recordset object ? the corresponding MFC object is represented by class CDaoRecordset.

A querydef is a query definition, which you can optionally save as a persistent object in the database. A recordset is an object that represents and gives access to a set of records returned by a query.

Creating a Query with a Querydef

Once you create a querydef object, based on CDaoQueryDef, you can do the following with it:

Save the querydef object in the database, which lets you run its query again later.
Create parameters for the querydef, so you can run parameterized queries with it.
Create a recordset based on the querydef.
Use the querydef's Execute member function to directly execute a query that doesn't return records, such as an action query or a SQL pass-through query.

To create a querydef

See the detailed instructions in the article DAO Querydef: Using Querydefs.

Creating a Query with a Recordset

You can create a query with a recordset, represented by CDaoRecordset, in one of three ways:

First create or open a CDaoQueryDef object; then base a CDaoRecordset object on it, using the version of CDaoRecordset::Open that takes a pointer to a CDaoQueryDef object.
First create or open a CDaoTableDef object, then base a CDaoRecordset object on it, using the version of CDaoRecordset::Open that takes a pointer to a CDaoTableDef object.
Create a recordset, usually based on a class that you derive from CDaoRecordset using AppWizard or ClassWizard, and open the recordset. No querydef is required.

To create a recordset with or without a querydef or tabledef

See the article DAO Recordset: Creating Recordsets.

You’ll most likely base your recordsets on querydefs when you have a saved querydef for a query that you run frequently, or when you have just created a querydef that you plan to save in the database for later reuse. Or you’ll base recordsets on tabledefs when you have a tabledef and want to work with the data in the table that the tabledef represents. Otherwise, you’ll simply create a recordset (without a querydef) whenever you need one.

For related information, see the following topics in DAO Help:

QueryDef Object
Recordset Object
CreateQueryDef Method