• The DAO MFC Classes
  • CDaoWorkspace
  • CDaoDatabase
  • CDaoTableDef
  • CDaoQueryDef
  • CDaoRecordset
  • CDaoException
  • CDaoFieldExchange
  • CDaoRecordView
• Summary

- 13 -
Understanding MFC's DAO Classes

Until Microsoft created Visual C++ 4, database programmers had some difficulty justifying Visual C++ as a mainstream database development platform. Visual C++'s sole support for database developers was ODBC. Visual C++ didn't support the more powerful (and useful) Microsoft Jet database engine. In fact, Visual C++ programmers often were told by Visual Basic programmers that they had a better database development platform. Today, with Visual C++ 4, Visual C++ programmers are no longer at such a disadvantage. They now have access to the Microsoft Jet database engine and DAO (Database Access Objects).

WARNING

The DAO database classes are meant for single-threaded operation only. Don't try to utilize these classes in more than one thread at one time.

With Visual C++ 4, the DAO database classes are considered distinct from the MFC ODBC database classes. All of the DAO database class names have a prefix of CDao. Usually the DAO classes are more powerful than the ODBC classes. DAO classes use the Microsoft Jet database engine to access data. In turn, the Microsoft Jet database engine uses ODBC drivers to access data that isn't supported directly. The DAO classes also support the Data Definition Language (DDL) operations—for example, operations for creating databases and adding tables and fields via the classes—all without having to call DAO directly.

This chapter documents the new MFC classes that support the DAO interface. These classes (seven main new classes and several more supporting classes and structures) are very similar to their ODBC database counterparts, such as CDatabase and CRecordset.

Generally, the majority of Visual C++ database applications will be written using AppWizard and will be expanded by the programmer to become complete, working database applications. It's possible to write a database application from scratch using the DAO database classes—and a few programmers do. However, neither Microsoft nor the authors recommend doing this. With this in mind, your database application will have a few of the classes included by AppWizard (these classes are described next). During the development cycle, you will probably add other classes.

The MFC classes for DAO are like all other MFC classes. They serve as wrappers, encapsulating DAO's basic functionality. For example:

• The CDaoWorkspace class encapsulates the functionality of the DAO Workspace object.
  
  
• The CDaoRecordset class encapsulates the functionality of the DAO Recordset object.
  
  
• The CDaoDatabase class encapsulates the functionality of the DAO Database object.
  
  

This process continues through the other DAO MFC classes.

Not all of the DAO functionality is encapsulated in the DAO MFC classes. You'll find that the major DAO objects are supported, but some objects, such as fields, indexes, parameters, and relations, aren't directly supported by the DAO MFC objects.

---

NOTE

There are three technical papers (called Technical Notes) specific to DAO:

TN053	Writing Custom DFX Routines for DAO Database Classes
TN054	Calling DAO Directly While Using MFC DAO Classes
TN055	Migrating MFC ODBC Database Class Applications to MFC DAO Classes

---

---

NOTE

Technical Note 54 (in the Visual C++ Books Online, see Visual C++ Books, MFC 4.0, MFC Technical Notes) has an example of using nonencapsulated DAO functionality in an MFC application.

---

Table 13.1 shows a mapping of DAO objects to MFC classes. The Comments and Related ODBC Classes column lists the corresponding ODBC class when applicable.

The DAO MFC Classes

The following sections document the DAO MFC classes by showing significant member functions and giving examples of usage.

As I mentioned earlier, some of the DAO MFC classes have ODBC analogues. This makes it easier to convert ODBC applications to use the DAO MFC classes. Generally, if you're unsure whether you should use DAO or ODBC, you should choose DAO to base your application on if the underlying database structure is either Access or one of the database structures that DAO supports directly (such as FoxPro).

---

NOTE

All DAO functions require the DAO header. Use the statement #include <afxdao.h>. When you use AppWizard to create an application, the #include is added automatically.

---

---

NOTE

See the DAO MFC documentation for more information about each of the DAO MFC classes. Simply search for the class name in the Books Online in Visual C++.

---

CDaoWorkspace

The CDaoWorkspace object is the highest object in the DAO hierarchy. It represents the DAO Workspace object. The DAO workspace defines the session for the user. It contains open databases and support for simultaneous transactions.

The CDaoWorkspace object manages named database sessions (from initial logon to logoff) for a single user. This database may be password protected. Generally, an application will use a single workspace. With AppWizard applications, it's unnecessary to create an explicit Workspace object. If your application needs multiple sessions, you can create additional Workspace objects. For each Workspace object, there may be more than one open database object at any given time. Under MFC's implementation, the workspace is basically a manager of transactions.

The CDaoWorkspace class provides the following functionality:

• It provides explicit access to a default workspace. This default workspace is created by initializing the database engine. Typically, you would use CDaoWorkspace's default workspace implicitly by creating DAO database and recordset objects.
  
  
• It provides a transaction space where transactions will be applied to all databases that are open in the workspace. There are no restrictions in creating additional workspaces to manage separate transaction spaces.
  
  
• It provides the interface to many of the properties of the Microsoft Jet database engine. The database engine can be initialized by opening or creating a workspace. The database engine can also be initialized by calling a static class member function before an open or create call.
  
  
• It provides access to the Workspaces collection of the database engine. All active workspaces that have been appended to it will be stored in the Workspaces collection. It's also possible to create and work with workspaces without having to append a workspace to a Workspaces collection.
  
  

The DAO MFC classes don't implement the security control Users and Groups collections. When security is a concern, you will need to provide the necessary interface using direct calls to the underlying DAO interface.

---

NOTE

For information on accessing the underlying DAO interface, go to Books Online and search for the topic Technical Note 54.

---

The CDaoWorkspace call may be used to do the following things:

• Open the default workspace explicitly. Typically, the opening of the default workspace is implicit—for example, when you open a new CDaoDatabase or CDaoRecordset object. However, a situation might arise where you have to access the default workspace explicitly—for example, to access database engine properties or the Workspaces collection.
  
  
• Create a new workspace. If you want to add new workspaces, you would call Append.
  
  
• Open an existing workspace that is in the Workspaces collection.
  
  

In order to use CDaoWorkspace to create a new workspace that doesn't exist in the Workspaces collection, use the CDaoWorkspace.Create() member function. With CDaoWorkspace, workspace objects won't persist between Microsoft Jet database engine sessions. If your application links MFC statically (that is, you link the MFC code using a static library rather than a DLL), when the application ends, the Microsoft Jet database engine will still be initialized. However, if the application uses MFC using the MFC DLL, the Microsoft Jet database engine will be uninitialized when the MFC DLL is unloaded.

Use the Open() member function to open an existing workspace. Use the Close() member function to close a workspace session. When Close() is called, all databases that haven't already been closed will be closed. Also, all uncommitted transactions will be rolled back (discarded).

DAO is used to manage transactions at the workspace level. As such, any transactions to workspaces with more than one open database will be applied to all open databases. For example, if there are two databases with uncommitted updates, and there is a call to CommitTrans(), all of the updates are committed, regardless of which database the changes are being applied to. When it's necessary to limit your transactions to one of a number of open databases, you must create a separate workspace object for the database to which you wish to limit your transactions.

Your application will use the DAO default workspace implicitly when the following events occur:

• When a new CDaoDatabase object is created without using an existing CDaoWorkspace object, CDaoWorkspace will create a temporary workspace object for you. This temporary workspace will correspond to DAO's default workspace. This is true even when done with multiple databases—all the database objects will be associated with the default workspace. The database's workspace may then be accessed using the CDaoDatabase member.
  
  
• If a CDaoRecordset is created that doesn't have a pointer to a CDaoDatabase object supplied to it, MFC will create a temporary CDaoDatabase DAO object. MFC will also create a temporary Workspace object. The database and its workspace may be accessed using the CDaoRecordset data member.
  
  

The CDaoWorkspace class provides a host of other database operations, such as repairing corrupted databases or compacting databases. Table 13.2 lists the members of the CDaoWorkspace class.

CDaoDatabase

The CDaoDatabase object is used to represent the connection to a database (from the CDaoWorkspace object) through which you can operate on the database's data.

---

NOTE

For more information on which database formats are supported, refer to the CDaoWorkspace::GetName() member function in the Visual C++ Books Online.

---

More than one CDaoDatabase object may be active at a given time in a given CDaoWorkspace object. The workspace maintains a collection of open database objects called the Databases collection. For more information, see the section "CDaoWorkspace."

You can create CDaoDatabase database objects implicitly when you create Recordset objects. You also can create a CDaoDatabase object explicitly. If you want to use an existing database explicitly with CDaoDatabase, you can do one of the following:

• You can construct a CDaoDatabase object and pass it a pointer to an already open CDaoWorkspace object.
  
  
• You can create a CDaoDatabase object without specifying the workspace. In this situation, MFC will create a new, temporary CDaoWorkspace object.
  
  

---

NOTE

It's possible to create a new Microsoft Access (.MDB) database. First, construct a CDaoDatabase object, and then call the CDaoDatabase object's Create() member function. It's not proper to call Open() after Create() in this situation.

---

When using CDaoDatabase, you may open an existing database by creating the CDaoDatabase object and then calling the Open() member function.

Each of these techniques will append a CDaoDatabase object to your CDaoWorkspace object and then open a connection to the database's data. You then will create CDaoRecordset, CDaoTableDef, or CDaoQueryDef objects to utilize the database. When CDaoRecordset, CDaoTableDef, or CDaoQueryDef objects are created, you pass to the constructors for these objects a pointer to your CDaoDatabase object. When the application has finished working with the connection, a call to the Close() member function closes the connection. You then must close any recordsets that you haven't already closed and destroy the CDaoDatabase object.

---

NOTE

If you already know ODBC, things might be starting to look familiar since ODBC and DAO share a common model. The following paragraphs discuss transaction processing—which, except for class names, works like ODBC's transaction processing.

---

DAO provides transaction processing at the workspace level. See the CDaoWorkspace::BeginTrans, CDaoWorkspace::CommitTrans, and CDaoWorkspace::Rollback functions in the Visual C++ Books Online. For additional information about transactions, search for the article "DAO Workspace: Managing Transactions" in the Programming with MFC title in Books Online.

As I mentioned earlier, DAO can interface with ODBC data sources. Microsoft recommends that you attach your ODBC data sources to a Microsoft Access (.MDB) database as external tables. For further information about this technique, see the article "DAO External: Working with External Data Sources" in the Programming with MFC title in Books Online.

With DAO, each database will maintain its own collections of Tabledef, Querydef, Recordset, and Relation objects. The DAO CDaoDatabase class will supply the necessary member functions to allow your application to manipulate these objects. These objects are stored in DAO itself, not in the DAO MFC class objects. There are also MFC classes for Tabledef, Querydef, and Recordset objects. There is no MFC class for Relation objects at this time.

Table 13.3 lists the members of the CDaoDatabase class.

CDaoTableDef

The DAO CDaoTableDef object is used to represent the stored definition of a base table or a table that is attached. Each DAO database object has a collection called TableDefs that contains all the saved DAO Tabledef objects.

You can manipulate a table definition with CDaoTableDef. Here are some things you can do using CDaoTableDef:

• In databases, you can examine field and index structures of any local, attached, or external table.
  
  
• You can call the SetConnect() and SetSourceTableName() member functions for attached tables. You also can use the RefreshLink() member function to update your connections to attached tables.
  
  
• You can use the CanUpdate() member function to find out whether you can edit the field definitions in the table.
  
  
• You can get and set validation rules and text using the GetValidationRule(), SetValidationRule(), GetValidationText(), and SetValidationText() member functions.
  
  
• You can create a table-, dynaset-, or snapshot-type CDaoRecordset object by using the Open() member function.
  
  

To work with an existing table or create a new table, you will use Tabledef objects. First, construct a CDaoTableDef object. Next, give CDaoTableDef a pointer to a CDaoDatabase object to which the table belongs. Then you have to do one of the following things. What you do depends on what you want to do in your application:

• Call the Tabledef object's Open() member function, supplying the name of the saved table to use an existing saved table.
  
  
• Call the Tabledef object's Create() member function, supplying the name of the table to create a new table. To add fields and indexes, call CreateField() and CreateIndex().
  
  
• Call the Tabledef object's Append() member function to save the table you opened or created. You do this by appending the table to the database's TableDefs collection. Since Create() opens the Tabledef, it's not necessary to call Open().
  
  

It's easy to create tables using Microsoft Access. Open the database in Access, create your tables, and save the database. Using Access for this type manipulation is easier than trying to have your Visual C++ application perform these tasks. Of course, if your application must perform this functionality, and you're reasonably sure that all users of your application won't have Access available, you'll have to perform this task using your Visual C++ application instead of Access.

You specify a name for the Tabledef with a dbOpenTable value in the nOpenType parameter when opening a CDaoRecordset.

When using a Tabledef object to create a CDaoRecordset object, you might follow the steps just mentioned. After creating the object, construct your Recordset object using a pointer to your Tabledef object when you call CDaoRecordset::Open(). When you pass a Tabledef, it must be open.

As with other objects, when you're finished using the Tabledef object, call its Close() member function. After closing the Tabledef object, destroy it.

Table 13.4 lists the members of the CDaoTableDef class.

CDaoQueryDef

A Querydef is a query definition that is usually (but doesn't have to be) stored or saved in a database. Access databases often have query definitions stored in them. Querydefs consist of SQL statements that describe a query, the query's properties (such as the date it was created), and the time-out value. Since Querydefs don't need to be stored in a database, they can also be temporary objects. It isn't unusual for a Visual C++ front-end application to create a temporary Querydef object.

---

NOTE

Querydefs are always more efficient if they're stored in the database. Any Querydef that is to be executed frequently should always be saved in the database.

---

The CDaoDatabase object will maintain a collection, the QueryDefs collection, containing the object's saved Querydefs.

You can use CDaoQueryDef to work with saved queries and to create a new saved or temporary query. You would use CDaoQueryDef in the following way. First, you would create and construct your CDaoQueryDef object. This object would be supplied with the pointer to your CDaoDatabase object to which the query will belong. Next, you must do one of the following:

• When using an existing, saved query, you would call CDaoQueryDef's Open() member function. In the call to Open(), you must provide the name of the saved query.
  
  
• When creating a new query that is to be saved, you would call CDaoQueryDef's Create() member function. In the call to Create(), you would provide the name of the query. You would then have to call the Append() member function to save the query. You save the query by appending it to the database's QueryDefs collection. Since the Create() function opens the query, it's not necessary to call the Open() function.
  
  
• When creating a new, temporary query, you would call the Create() member function. Use an empty string as the name for the query, and don't call Append() to save the query.
  
  

After you've finished using the Querydef object, call the Close() member function to close it. As soon as the Querydef is closed, destroy the CDaoQueryDef object.

You can easily create Querydef objects using Microsoft Access. Open the database, design your query, and save it. Your Visual C++ database application can then use the query.

A Querydef object is used for either of the following:

• You can use it to create a CDaoRecordset object.
  
  
• You can call the object's Execute() member function to directly execute either an action query or an SQL pass-through query.
  
  

All types of queries can be used as Querydef objects. For example, queries can take the form of select, action, crosstab, delete, update, append, make-table, data definition, SQL pass-through, union, or bulk queries. The SQL statement that makes up the query determines the type of query.

See the Execute() and GetType() member functions in the Visual C++ Books Online for more information about queries. A query that would return a set of rows (as a recordset) is usually in the form of SELECT rows FROM table types of SQL statements.

When you use a Querydef to create a CDaoRecordset object, you create (or open) a Querydef. Then a CDaoRecordset object would be created and passed a pointer to your Querydef object. This pointer would be passed in the call to CDaoRecordset::Open(). The Querydef you pass must itself be open. If the Querydef isn't open, it can't be used to create a recordset.

You would use a Querydef object to gain access to the native SQL dialect for an external (non-Access) database. If you needed to create a Transact SQL query (Transact is the version of SQL used by Microsoft SQL Server), you would create the query and store it as a Querydef object.

Table 13.5 lists the members of the CDaoQueryDef class.

CDaoRecordset

The DAO class CDaoRecordset is used to represent a set of records that have been selected from a datasource. CDaoRecordset corresponds directly to the CRecordset object that is used with ODBC.

The main difference between the DAO recordset and the ODBC recordset is that CDaoRecordset class objects access data through DAO, which is based on OLE, while the CRecordset classes access the database using ODBC.

A CDaoRecordset object is known as a recordset. A CDaoRecordset object can take one of three forms: a table-type recordset, a dynaset-type recordset, or a snapshot-type recordset:

• The table-type recordset represents a base table that you can use to examine records, change records, add records to, or delete records from a single database table.
  
  
• The dynaset-type recordset is the result of a query that can have records that are updatable. The dynaset-type recordset comprises a set of records that may be examined, added to, changed, or deleted from an underlying database table or tables. The dynaset-type recordset may contain fields from more than one table in a database if necessary.
  
  
• The snapshot-type recordset contains a static copy of a set of records returned from a query. This static set of records can be used to display information or create graphs or reports. Like a dynaset, a snapshot-type recordset may contain fields from one or more tables in a database.
  
  

The records contained in the recordset are fixed at the time the recordset is opened. Both the table-type recordset and dynaset-type recordset will reflect any changes made to data after the recordset was opened (by either the current process or perhaps by other users). In contrast, the snapshot recordset is static and isn't updated. Many applications use CDaoRecordset directly (usually using classes created using AppWizard). However, you can derive a class from CDaoRecordset if you wish. When you have a recordset object, you can do the following:

• Use a filter to limit the records selected from the recordset.
  
  
• Scroll through the records either forwards or backwards.
  
  
• With dynaset-type and snapshot-type recordsets, you can use comparison operations to find records. Typical operations include <, <=, =, >=, and >.
  
  
• With table-type recordsets, you can create an index and use the Seek() member function.
  
  
• With dynaset-type and table-type recordsets, you can update the records and specify a locking mode.
  
  
• Sort the recordset.
  
  
• At runtime, you can parameterize the recordset. This will serve to limit the records selected into the recordset.
  
  

To use the CDaoRecordset object, first you must open a database and construct your CDaoRecordset object. A pointer to your CDaoDatabase object must be passed to the constructor. Alternatively, it's possible to create a CDaoRecordset object and have MFC create a temporary CDaoDatabase object.

After you've created the CDaoRecordset, you must call the Open() member function. It takes a parameter that specifies whether the object is to be a table-type recordset, a dynaset-type recordset, or a snapshot-type recordset. Calling Open() selects data from the database and retrieves the first record.

After you've created your recordset object, you then can use the object's member functions and data members to scroll through the records and perform operations on them.

The CDaoRecordset class uses DAO record field exchange (DFX) to support the reading and updating of record fields through type-safe C++ members of your CDaoRecordset or CDaoRecordset-derived class. See the CDaoFieldExchange object in the Visual C++ Books Online for more information.

Table 13.6 lists the members of the CDaoRecordset class.

CDaoException

The CDaoException object is used when handling exceptions when using the DAO MFC classes. The CDaoException class includes several public data members that may be used to analyze the error and assist you in determining the cause of the exception. CDaoException objects are constructed and thrown by member functions of the DAO database classes.

You can access exception objects within the scope of a CATCH expression. Your application also can throw a CDaoException object by using an AfxThrowDaoException() global function.

---

NOTE

The Microsoft documentation mentions the file DAOERR.H. This file is actually called DBDAOERR.H.

---

Table 13.7 lists error codes from DBDAOERR.H.