Using Access Collections

by Tony Harris - Jenton Software
Image of Line Break

When developing a database system I like to have definitions of the tables handy for reference. I also don't want to have to wade through reading several pages of the standard printouts that come from FILE-PRINT DEFINITION in ACCESS. And I certainly don't want to wade through the printing task in the first place - selecting each table in turn and then selecting FILE-PRINT DEFINITION.

All I want is the definition of the tables, any indexes, the field names, types and sizes; for the database I select. So here is a small program that prints out the database definition - all tables - from one button push.

The Main Form

This allows the user to select an MDB file from anywhere on the disk, start the print run and finally exit the program. To keep the number of files in this "tiny" projectdown, I chose to include the constant definitions in the declarations section of the form. By setting the pattern property in the file box to "*.MDB", the file search is limited to only ACCESS database files.

To co-ordinate the 3 Drive/Dir/File controls - use the Change Event in both the Drive and DIR, so that as each control changes - it affects the next control in a cascade event. There is no need to do this for the File Control as it is the last link in the chain. However, in order to focus on one database file to print, use the Click event for the File control. This then places the complete path into the text box. Because it is a list box you could alternatively allow multiple selections and then have the computer loop through each database.

The text box is disabled so we don't accidentally enter a wrong filename. The heart of the program comes from using the collections and associated objects within ACCESS. The ones used in this program are structured hierarchically :

Screen Snap of Select DB

Database Table Type Indexes Name
Fields Name

The program opens the database, searches for the names of the tables in the TABELDEFS collection, checks whether it is a system table (no definitions are printed for System tables) by checking if it has the DB_SYSTEMOBJECT attribute, and if it is an attached table, external table or normal table.

As the program cycles through each table, it checks the FIELDS Collection of the Table and outputs whatever information I might care to use. In this example, I print the Name, DataType and Size. The 3dpanel on the Main Form is filled from left to right indicating the progress of the print routine. Iím sure there are lots of enhancements you could make, so feel free...

Image of How-To Icon The source code...

Written by: Tony Harris
August 1995

Image of arrow to previous article Image of arrow to next article