Navigation:  Articles > Dec-1997 >

Cross-Reference Your Tables and Fields

Previous pageReturn to chapter overviewNext page

Duane Hookum          

 

Here's a cool use of the crosstab query working with Access's built-in documentation tool. Access's built-in documentor, a single query, and some Excel all combine to give you a complete cross-reference to your fields and tables.
 
I often need to cross-reference all of the tables and fields in a single Access database file. In particular, I want a report with table names listed as column headings and field names as row headings. In addition to documenting which fields are common to which tables, the report also identifies inconsistencies in field names, types, and sizes.
 
While the data definition add-in does a nice job of displaying and reporting table structure information on single tables, finding information across many tables requires viewing multiple pages and records to find common fields. So, I created an "Object Definition" table that, combined with a cross-tab query and Excel, would quickly and easily show me all the information I needed in a useful format. Figure 1 shows the result of my work. Here's how you can do it.

199712_dh1
Figure 1
 
Getting started
The first step is to run the "Database Documentor" found FILE - ADD-ins menu (if you're using Access 8.0, select TOOLS -- ANALYZE -- DOCUMENTOR). On the intial screen, make sure that "Object TYPE" is set to "Tables" before selecting the tables you want to be included in the cross-reference.
 
After selecting the relevant tables, choose "OPTIONS" and select only the "Names, Data Types, and Sizes" radio button in the Fields section of the dialog box. There should be no selections from the Tables section, and "None" should be checked under Indexes. With those options set, press "OK" to confirm the options and return to the Database Documentor window.
 
Now press the "OK" button to start the document-ation process. Access will create a temporary report of the information it gathers and open it in preview mode. Select FILE -- Save as TABLE to create a table, and name it "Object Definition." If you've previously saved this table, a number will be added to the end of the name.
 
After exiting the report preview, open the new Object Definition table to view its contents. The Object Definition table contains the requested properties of each table you selected from the documentor's table list. Each selected table and its fields generate a record in the Object Definition table. The Object Definition table itself has the seven fields shown in Table 1.
 
Table 1. The Object Definition table.

Field Name

Field Description

ID

This field is a unique long integer similar to a counter field.

Parent ID

This field is a long integer that stores the ID value of the record's parent. For instance, a record describing a field would have its table's ID in this field.

Object Type

This field is a memo. In our example, it contains "Table," "Column," or "Property."

Name

This text column contains the name of the table or field. If the record describes a property of a table or field, it will contain the name of the property.

Extra1

This memo field is used to store the value of a property.

Extra2

In our example, this text field contains the type of field for all records describing a field.

Extra3

Similar to Extra2, except it contains the size of the field.

 
 
 
Querying definitions
Now start a new Query and add the Object Definition table to it twice (Access allows a table or query to be included in a query multiple times). The second table added will have "_1" appended to its name. In the crosstab query that you're building, the first Object Definition table will be used for table information and the second will be used for its column (field) information.
 
At this point, you should join the "Parent ID" field of the "Object Definition_1" table to the "ID" field of "Object Definition." The effect of this join is to link a "Column" record with its parent "Table" record. Change the query to a Crosstab query and from the VIEW menu, select Table Names to display the name of the table under each field in the query. Next, drag fields from the two tables to the grid. Since the two field lists are identical, make sure you select your fields from the proper Table. The Object Definition table will be used to show the table names, while Object Definition_1 will be used for field names, types, and sizes. The finished grid is shown in Figure 2.

199712_dh2
Figure 2

 
 
Pressing the Datasheet View button shows the resulting recordset. Since the Extra3 field gives the size of each field, I use it as the Value for the crosstab (every field has a size, after all). As a bonus, it also puts the size of each field in the report, and that can be very useful information. The Extra2 field contains the field types. You'll probably want to adjust the column widths and might even be happy enough with the results to print the query as is.
 
I prefer to send the query to Excel for publishing. This allows me to align the table column name headings vertically for a nicer-looking finished product. It also lets me print row headings on each page, which makes the report much easier to use. To send the query to Excel, select File | Output To in Access 2.0 and Access 95, or Save As in Access 97. Then choose "Microsoft Excel . . .," give the file a name and destination, and press "OK." Once the export is complete, open the file in Excel, select the table names cells, and format their alignment to the appropriate vertical format. Set the row heights and column widths as needed and print. You're done.
 
As an indication of what the tool can do, look again at Figure 1. The cross-reference that appears in Figure 1 is from the Resource Scheduling database template that ships with Access 8.0. The "-" in the grid where the field size should be signifies a memo field.
 
There are other Access documenting tools available to you. But, this tool does two great things: It provides a complete table/field cross reference, and it doesn't take any cash out of your pocket. Sounds like a great deal to me.
 
Read about the download SYSCROSS.EXE on this page