Writing Code to Create Your Own Documentation
Most of the time, the options the Database Documenter provides are sufficient. At times, you won't like the format the Database Documenter selectsor, more importantly, you might want to document properties of the database objects not available through the user interface. In these situations, you can choose to enumerate the database objects using code and output them to a custom report format.
Using ADOX (ADO Extensions for DDL and Security), you can enumerate any of the objects in your database. Listing 29.1 shows an example.
Listing 29.1 Using ADOX to Enumerate the Table Objects in a Database
Sub EnumerateTables()
Dim conn As New Connection
Dim adoCat As New ADOX.Catalog
Dim adoTbl As New ADOX.Table
Dim strSQL As String
DoCmd.SetWarnings False
Set conn = CurrentProject.Connection
adoCat.ActiveConnection = conn
For Each adoTbl In adoCat.tables
If adoTbl.Type = "Table" Then
strSQL = "INSERT INTO tblTableDoc" _
& "(TableName, DateCreated, LastModified) " _
& "Values ("" & adoTbl.Name & "", #" _
& adoTbl.DateCreated & "#, #" _
& adoTbl.DateModified & "#) "
conn.Execute strSQL
End If
Next adoTbl
DoCmd.SetWarnings True
End Sub
NOTE
For the code in Listing 29.1 to run, you must first set a reference (via Tools, References) to the Microsoft ADO Ext 2.7 for DDL and Security library.
| The EnumerateTables routine, located in the basDocument module of CHAP29EX.MDB on your sample code CD-ROM, documents various information about the tables in the database. It uses the ADOX catalog and table objects and a For...Each loop to loop through all the table definitions in the database. For each table in the database, it determines whether the table's type property is set to "Table", indicating that it is a standard table (as opposed to a system table or a query). It then uses the Execute method of the Connection object to execute a SQL statement, inserting all the requested information about the table definition into a table called tblTableDoc. You can use this table as the foundation for a report. Of course, when you use appropriate For...Each loops and properties, along with the ADOX object model, you can obtain any information about any of the objects in the database using the same technique. |