Databases - MS Access CM Data

CM - Configuration Management

This page was split off from my generic CM Page do to the number of examples provided.

List Tables | List Forms and Reports | Using MSysObjects | Description

List Tables

To list the tables in the current database Careful, this also lists private tables starting with MSys that the user normally won't see.

Listing Forms and Reports

It was very difficult to find methods to list the forms and reports in the mdb file. I spent several days searching the help files before succeeding - the following note is attached to the Forms Collections help.
To list all forms in the database, whether open or closed, enumerate the Documents collection of the forms' Container object. You can then use the Name property of each individual Document object to return the name of a form.
Great! But an example would be useful. The code above returned the following containers If I had a clue, the debug window would have shown the answer :) Just assign a temporary variable to the CurrentDb and place a break point after it. Then the debug window shows the object tree.

If you use this technique to access Tables, then you will see additional default tables and the Fields collection will not be available.

Using MSysObjects

The names of forms, reports, queries, and tables can be accessed via MSysObjects. For example, the following selects all the forms. Queries and tables are a little wierd because many are generated by the system. In order to limit the returned values to just those created by the designer, you should use code similar to the following. You can also get DateCreate.

Linked Tables

With MS Access 97, it is almost impossible to determine how tables are linked. The "correct" way to do this is to use However, on my system, the paths are so long that the useful data is missing. (No the window can not be enlarged and the data can not be copied to the clipboard .. I've checked.)

The solution uses MSysObjects. Just copy the following query to an sql editor and run it.

The fields are

I discovered MSysObjects while trying to connect to an MS Access database while using Delphi. There are actually a number of hidden system tables. I strongly suggest not modifying any of these ... but browsing them is ok.

Field Descriptions

MS Access allows the developer to annotate each field with a description when the tables are designed. This is actually one of the advantages of using MS Access.

However, when it come to accessing this data via code, this is another poorly documented property!

The following is based on information from Field / Specifics and other help pages listed below.

From the Description Property help
An object's description is displayed in the Description column in the Details view of the Database window.

If you create controls by dragging a field from the field list, Microsoft Access copies the field's Description property to the control's StatusBarText property.

From the Property Object / Specifics help
Until you have set a Microsoft Access-defined property either in table Design view or from Visual Basic code, that property won't appear in the Properties collection. When you set these properties from Visual Basic, you should include error-handling code that checks to see if the property exists in the Properties collection, and creates and appends the property if it doesn't.
This seems to also apply to reading these properties - You have to expect and catch errors. Unfortunately, the error processing is extremely slow! At least 10 times slower!

The 2 commented out lines in the code above were left there to demonstrate what does not work.

The Description is included when you select Tools / Analyze / Documenter.

Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / MSAccess / CM_Data.html