A great deal of information can be stored in table definitions of Access databases. This information can be easily extracted to build documentation for a database or a data dictionary for a query builder. In this article, we demonstrate how to navigate Access Database tabledef structures to build a data dictionary for an Access application.

Access allows a developer to store descriptions of fields in the table design with the field definition. Other useful information that can be stored are the

caption (which is the default label for the field),

rowsource for fields that will be edited and displayed using listboxes and combo boxes

field data type

field default value

validation rule

and many more.

This information is then used by Access form wizards to draw the appropriate control when a designer drags a field onto the form layout. It is also used when a user edits records in datasheet mode to provide the appropriate control.

This information can also be extremely handy if one wants to create documentation that details the definitions of each field or to build a data dictionary for a query builder.

Outlined below is a subroutine that navigates a tabledef and dumps the field names, captions, descriptions, data types of each field in the table into another table that we will call the data dictionary table. The data dictionary table should have the following fields:

This subroutine navigates through each field definition that a tabledef comprises. Each field has a property bag which the subroutine also navigates through. Some of these properties exist only if they have been set by a designer. For example if no Rowsource is set for a field, then the field definition will not have a "Rowsource" property in its property bag. This routine only adds a couple of these to the data dictionary table. Others can be added. Note that the routine loops through each property but selectively targets some for addition to the dictionary table. To see a listing of other properties, one can put a