DAO Tabledef: Examining a Database Schema at Run Time

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

This article discusses how to examine the schema of a database — the structure of the database, as defined by its tables and their fields and indexes — at run time. While many applications are based on knowledge of the database schema at design time, there are situations in which you might need to determine the schema dynamically at run time:

• Your application is designed to work with arbitrary schemas.

  See the MFC Database sample for an example of this.

• The schema of your target database tends to change.

  Perhaps users can add and delete tables and even alter the structure of tables by adding or deleting fields and indexes.

How Dynamic Examination of the Schema Works

Dynamic examination of the schema is based on the use of DAO collections. A DAO database object contains the following collections: TableDefs, QueryDefs, Recordsets, and Relations. MFC exposes all of these via member functions except for the Recordsets collection. For details about how MFC exposes collections, see the articles DAO Collections and DAO Collections: Obtaining Information About DAO Objects.

An Example of Dynamic Schema Examination

The following illustration uses the TableDefs collection, but the principles demonstrated apply equally to the other collections.

To enumerate the TableDefs collection for a CDaoDatabase object

1. Get the number of tabledef objects in the underlying DAO collection by calling .

2. In a loop from 0 to the number of tabledefs, call for each object in the collection.

3. For each tabledef object, examine the object returned by GetTableDefInfo. From this object, you can get:

   • The name of the tabledef object as well as the name of the ODBC source table that the tabledef represents.

   • Whether the table schema is updatable.

   • Tabledef attributes.

   • The date the tabledef object was created and the date it was last updated.

   • The ODBC connection information for the table.

   • The validation rule and validation text for the tabledef, if any.

   • The number of records in the underlying table (obtaining this count might take considerable time for a large table, and the count might be somewhat unreliable).

The MFC Database sample performs these steps and lists the table names in a list control or a tree control. It then does the same thing for the fields and indexes in the tables and for the other collections in the database: QueryDefs and Relations.

See Also   DAO: Where Is..., DAO Recordset, DAO Recordset: Binding Records Dynamically