By Duane Hookom - Sept. 2005
Earlier version of Microsoft Access (pre-2000) allowed users to save the results of the database documenter (Tools->Analyze->Documenter) in a table in the current MDB file. With a little SQL and reporting knowledge, the user could create custom reports from the table (Object definition).
Later versions of Access don't expose this useful feature. However, you can get at the same table information by linking to the doc_tblObjects table in the ACWZUSRT.MDT file. This file is basically an MDB file and is generally located in C:\Documents and Settings\...Your Profile Here...\Application Data\Microsoft\Access\. The Application Data folder is hidden so you may need to open Windows Explorer and set the folder options to "Show hidden files and folders".
The first step is run the documenter against the objects and properties you would like to report. You can then link to the "documenter" table by selecting File->Get External Data->Link Tables. You will need to enter "*.MDT" in the File Name drop down. Browse to the proper folder and you should find the MDT file and the doc_tblObjects table.
This table is over-written each time you run the documenter. If you want to keep the results of your documenter session, you can import rather than link to the table.
| Field Name | Data Type | Comments |
|---|---|---|
| ID | Autonumber Primary Key | |
| ParentID | Long Integer | Used in self join to point to parent record |
| TypeID | Long Integer | Identifies the type of object or property. For instance a TypeID of 11 is a Column (field). There is some documentation on this field in the table doc_tblProperties. |
| Name | Text | The name of the property or object |
| LocalizedName | Text | Possibly used for user interface |
| Extra1 | Memo | Property values such as captions, default values, ordinal position |
| Extra2 | Text | Stores the field data type for columns/fields |
| Extra3 | Text | Stores the field size for columns/fields |
The key with creating queries with this table is understanding self-joins. A query containing just one copy of doc_tblObjects isn't very informative. Adding a second copy of the same table and then joining the ParentID field from one table to the ID field from the other copy of the table begins to show useful information. For instance, the following SQL will show table name, field name, field type, and field size.
|
SELECT doc_tblObjects.Name AS
TableName, doc_tblObjects_1.Name AS FieldName, doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize FROM doc_tblObjects AS doc_tblObjects_1 RIGHT JOIN doc_tblObjects ON doc_tblObjects_1.ParentID = doc_tblObjects.ID WHERE (((doc_tblObjects_1.TypeID)=11)); |
To view all the field properties, add another copy of the same table into your query and use this crosstab SQL:
| TRANSFORM Min(Left([doc_tblObjects_2]![Extra1],255)) AS
Expr1 SELECT doc_tblObjects.Name AS TableName, doc_tblObjects_1.Name AS FieldName, doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize FROM doc_tblObjects AS doc_tblObjects_2 RIGHT JOIN (doc_tblObjects AS doc_tblObjects_1 RIGHT JOIN doc_tblObjects ON doc_tblObjects_1.ParentID = doc_tblObjects.ID) ON doc_tblObjects_2.ParentID = doc_tblObjects_1.ID WHERE (((doc_tblObjects_1.TypeID)=11)) GROUP BY doc_tblObjects.Name, doc_tblObjects_1.Name, doc_tblObjects_1.Extra2, doc_tblObjects_1.Extra3 PIVOT doc_tblObjects_2.Name; |
Save your queries and use them record sources for reports.