DAO Tabledef: Examining a Database Schema at Run Time

HomeOverviewHow Do IFAQSampleTutorialODBC 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:

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 CDaoDatabase 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 CDaoDatabase::GetTableDefCount.

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

  3. For each tabledef object, examine the CDaoTableDefInfo 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 DAOVIEW 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