Use DAO to make an Excel report on an Access database's structure
By. Rocky Mountain Computer Consulting, Inc
This program uses Excel and DAO so you need to add references to the Excel and DAO object libraries. Select the Project menu's References command, select the Microsoft Excel 9.0 Object Library and the DAO 3.6 Object Library (or whatever versions you have), and click OK.
When you click the Analyze button, the program creates an Excel application object. It makes a new Workbook and gets a reference to its first Worksheet.
The program writes some headers into the Worksheet. It then opens the database and loops through its TableDefs collection representing the database's tables. For each TableDef (skipping the system tables, which have names starting with MSys), the program adds the table's name to the Excel Worksheet. It also displays the table's Description property. If the table has no Description, this value is missing from the Properties collection so the program uses an On Error statement so it doesn't crash when it tries to access the missing value.
Next the program loops through the objects in the TableDef's Fields collection. Each of these Field objects represents a field in the table. The program loops through a list of Field properties (Name, Type, Size, AllowZeroLength, DefaultValue, Required, Description) and displays their values. It uses the TypeName function to convert a numeric database type name into a type string such as Integer or Boolean.
After displaying the table's fields, the program loops through the TableDef's Indexes collection. For each Index in the collection, the program displays the index's Name, Primary, Required, Unique, and Foreign property values. It then loops through the index's fields, displaying the names of each field in the index.
After it has finished displaying information about all of the database's tables and their indexes, the program describes the database's relations by looping through the Relations collection. The program displays each Relation's name. It then loops through the fields that define the relation, displaying the source and foreign table and field names.
Download Source code
Taken from vb-helper.com