DAO Recordset

This article describes the key features of the MFC CDaoRecordset class. Additional articles explain how to use recordsets. For task-oriented information, see the article DAO Recordset: Creating Recordsets. For an understanding of the DAO recordset object underlying each MFC CDaoRecordset object, see the topic "Recordset Object" in DAO Help.

Topics covered include:

DAO recordset: definition
DAO recordset types
Derived DAO recordset classes
DAO recordset operations
Recordsets and querydefs
Recordsets and tabledefs
Recordsets and DAO collections
DAO recordset performance features
Further reading about DAO recordsets

DAO Recordset: Definition

A DAO recordset, represented in MFC by a CDaoRecordset object, represents the records in a base table or the records that result from running a query. Recordsets are the principal way in which you work with data using the MFC DAO classes. For a description of the DAO recordset object underlying each CDaoRecordset object, see the topic "Recordset Object" in DAO Help.

A recordset represents, simultaneously:

All of the records in a table or query — a set of records.
The current record in that set, whose fields fill the recordset's field data members, if any. Scrolling to a different record in the set fills the recordset's field data members with new values.

For information about recordset features and capabilities, including searching, navigating, updating, bookmarking, and constraining which records are selected, see class CDaoRecordset in the Class Library Reference. Also see the list of additional recordset articles in Further Reading About DAO Recordsets.

DAO Recordset Types

You can create three kinds of CDaoRecordset objects:

Table-type recordsets, representing a base table in a Microsoft Jet (.MDB) database
Dynaset-type recordsets, which result from a query
Snapshot-type recordsets, consisting of a static copy of a set of records

The following table summarizes the characteristics and purposes of the three recordset types.

Characteristics of Recordset Types

Characteristic	Table-Type	Dynaset-Type	Snapshot-Type
Based On	A base table	A query	A query
Updatable	Yes	Yes	No
Dynamic	Yes	Yes	No
Best Uses	Working with a single table (in a non-ODBC database).	Working with records, possibly containing fields from one or more tables. Reflects changes by other users and is updatable.	Finding data or preparing reports. Reflects the state of the data at the time of the snapshot.
Limitations	Can use only with .MDB databases or ISAM tables opened directly.	Doesn’t reflect new records that meet selection criteria after the recordset opens. See below.	Not updatable. The snapshot is not quite instantaneous. See below.

A table-type recordset is based directly on the table rather than on a query.

A dynaset is a recordset that reflects changes to the underlying records by other users of the database or by other recordsets. As your application scrolls to a changed record, a new copy is retrieved, bringing it up to date. This behavior is ideal for situations in which it is important to be completely up to date.

Note A dynaset is a dynamic but fixed set of records. New records that meet the selection criteria after the dynaset-type recordset has been created are not added to the recordset. This includes records that other users add.

A snapshot reflects the state of the data at a particular moment, the moment the snapshot is taken. This behavior is ideal for reporting.

Note Because it takes time to retrieve the records for a snapshot, the moment at which the snapshot occurs is not instantaneous.

Derived DAO Recordset Classes

Normally, you work with recordsets by deriving your own application-specific recordset classes from CDaoRecordset. You can create your recordset classes with AppWizard or ClassWizard (or by writing the same code yourself). When you use AppWizard or ClassWizard, the wizard prompts you to specify a database, a recordset type, and a table name on which to base the recordset. The wizard then lets you specify which columns to use in the recordset.

Recordset Features

The resulting recordset class has the following features:

It contains a data member for each column (field) in the recordset.
It has a member function you can use to get the name of the data source on which the recordset is based.
It has a member function you can use to get the SQL string on which the recordset query is based. You indirectly define the SQL string with ClassWizard. The string might contain a table name (for a table-type recordset that selects all fields in each record) or an SQL SELECT statement.
It has a member function, DoFieldExchange, that manages exchanging data between the data source and the recordset's data members (in both directions).

For more information about these features, see the article DAO Recordset: Architecture.

Binding Records Dynamically

You do not necessarily have to derive a recordset class. You can use CDaoRecordset objects directly, employing the GetFieldValue member function to retrieve individual columns (fields) of the current record immediately. For more information, see DAO Recordset: Binding Records Dynamically.

For information about using recordsets, see the article DAO Recordset: Creating Recordsets.

Recordsets and Querydefs

Besides constructing CDaoRecordset-based objects directly, you can create them indirectly from a CDaoQueryDef object. A querydef is a predefined query usually saved in a DAO database object's QueryDefs collection. Querydefs are a way to prepare frequently used or complex queries and store them in a database for reuse. One version of the CDaoRecordset::Open member function is initialized by a pointer to a CDaoQueryDef object.

Tip For convenience, you can use Microsoft Access to create querydefs. Then you can use the querydefs in your MFC program.

For more information, see the articles DAO Recordset: Creating Recordsets and DAO Querydef.

Recordsets and Tabledefs

As with querydefs, you can construct a recordset from a CDaoTableDef object. A tabledef encapsulates the structure definition of a table. Tabledefs are saved in the database object’s TableDefs collection. A version of CDaoRecordset::Open is initialized by a pointer to a CDaoTableDef object.

Tip For convenience, you can use Microsoft Access to create tabledefs. Then you can use the tabledefs in your MFC program.

For more information, see the articles DAO Recordset: Creating Recordsets and DAO Tabledef.

Recordsets and DAO Collections

DAO maintains a Recordsets collection, and each recordset maintains collections of DAO field objects and Index objects.

The Recordsets Collection

In DAO, the DAO database object maintains a Recordsets collection containing all active recordsets based on the database. When you open a DAO recordset it is appended to the collection.

MFC chooses not to expose the DAO Recordsets collection. In MFC, you have an explicit CDaoRecordset object in your program for each DAO recordset you create. It's up to you to keep track of the recordsets you open.

The Fields and Indexes Collections

In DAO, a recordset object maintains a collection of the fields in the recordset and a collection of the indexes in the underlying table.

MFC exposes each of these collections via member functions that let you get the number of objects in the collection and examine information about any of the objects. For more information about the GetFieldCount, GetFieldInfo, GetIndexCount, and GetIndexInfo member functions of CDaoRecordset, see the articles DAO Collections: Obtaining Information About DAO Objects and DAO Collections.

DAO Recordset Performance Features

In MFC, you can:

Cache multiple records from an ODBC data source in a configurable buffer.
It takes longer to fill the buffer, but having multiple records in memory speeds searching and navigating in the recordset. Caching has no effect or benefit for non-ODBC data sources. For more information, see the article DAO Recordset: Caching Multiple Records.
Use a double-buffering mechanism in which two copies are kept of the current record. The second copy is used to test whether fields in the first copy have changed.
Double buffering saves you the work of calling member functions such as SetFieldDirty or SetFieldNull for a field being edited. The trade-off is storing two copies, which can be a significant amount of memory for variable-length data types. For more information, see the article DAO Record Field Exchange: Double Buffering Records.