Data Access object (DAO)

1.1  Database: Definition

A DAO database object, represented in MFC by class, represents a connection to a database through which you can operate on the data. You can have one or moreCDaoDatabase objects active at a time in a given workspace, represented by an object.

1.2  External Databases

Besides using to work with Microsoft Jet (.MDB) databases, you can also access external data sources, particularly Open Database Connectivity (ODBC) data sources. For a list of external data sources .

1.3  Database Collections

In DAO:

• Each workspace object contains a collection of open database objects, called the Databases collection.
• Each DAO database objects contains collections of tabledefs, querydefs, recordsets, and relations.

In MFC, access to a workspace’s Databases collection is through member functions of class . Access to a database object’s collections is through member functions of class .

Note   DAO collections are not collections in the sense of the MFC collection classes. They are an integral part of DAO.

Note   MFC exposes all of a database’s collections via member functions except for the Recordsets collection. In MFC, you always have an explicit object for each recordset you create, and it is up to you to track these objects.

For more information about DAO collections in MFC, see the article DAO Collections. For related information, see the topic "Databases Collection" in DAO Help.

1.4  Database Roles

allows you to:

• Create new Microsoft Jet (.MDB) database files.
• Store tabledef objects that you can use to manipulate the structure of the database’s tables.
• Store querydef objects so you can reuse the queries they represent later.
• View and manipulate data in the database’s tables.
• Work with data in local or remote databases.
• Work with the database’s collections.

1.5  Accessing Database Objects

When you open a object without specifying an open object, MFC implicitly creates a CDaoDatabase object, along with the that contains the database and the underlying DAO database object. You can also create explicit CDaoDatabase objects.
See the article DAO: Accessing Implicit MFC DAO Objects for information on accessing:

• The CDaoDatabase object associated with a CDaoRecordset object.
• The CDaoWorkspace object associated with a CDaoDatabase object.

1.6  Database Persistence

Database objects exist in memory for the life of a database engine session. When that session terminates, the default workspace, the Workspaces collection, the Databases collection in each open workspace, and the database objects in the Databases collection(s) cease to exist (although the databases they represent do persist). These software objects are not stored on disk or in a database. When you begin a new database engine session and want to use the workspaces and databases you used in the last session, you must re-create any explicit workspace objects you need, and reopen any databases you were using in the workspace.
Microsoft Jet Database Engine
The Microsoft Jet Database Engine is a database engine on which several Microsoft products have been built. A database engine is the underlying component of a database, a collection of information stored on a computer in a systematic way. The first version of Jet was developed in 1992, consisting of three modules which could be used to manipulate a database.
JET stands for Joint Engine Technology, sometimes being referred to as Microsoft JET Engine or simply Jet. Microsoft Access and Visual Basic use or have used Jet as their underlying database engine. It has since been superseded for general use, however, first by Microsoft Desktop Engine (MSDE), then later by SQL Server Express. Jet is now part of Microsoft Windows and is no longer a component of Microsoft Data Access Components (MDAC). For larger database needs, Jet databases can be upgraded (or, in Microsoft parlance, "up-sized") to Microsoft's flagship database product, SQL Server.

DAO Recordset

1.7  DAO Recordset: Definition

A DAO recordset, represented in MFC by a 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 in theClass Library Reference. Also see the list of additional recordset articles in Further Reading About DAO Recordsets.

1.8  DAO Recordset Types

You can create three kinds of 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.

1.9  Derived DAO Recordset Classes

Normally, you work with recordsets by deriving your own application-specific recordset classes from . 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.

1.9.1    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, , 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.

1.9.2    Binding Records Dynamically

You do not necessarily have to derive a recordset class. You can use objects directly, employing the 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.

1.10                        Recordsets and Querydefs

Besides constructing -based objects directly, you can create them indirectly from a 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 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.

1.11                        Recordsets and Tabledefs

As with querydefs, you can construct a recordset from a object. A tabledef encapsulates the structure definition of a table. Tabledefs are saved in the database object’s TableDefs collection. A version of 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.

1.12                        Recordsets and DAO Collections

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

1.12.1                       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 object in your program for each DAO recordset you create. It's up to you to keep track of the recordsets you open.

1.12.2                       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 , , , and member functions of CDaoRecordset, see the articles DAO Collections: Obtaining Information About DAO Objects and DAO Collections.

1.13                        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.

DAO Querydef

1.14                        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.

1.15                        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 articleDAOQuerydef: Action Queries and SQL Pass-Through Queries.

1.16                        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.

1.17                        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.