Before you distribute your database to users, you may want to document the objects in your database; the underlying structure, or schema, of your application; and any Data Access Objects (DAO) objects, methods, and properties. This is sometimes called mapping the database. When you have a complete map of your database, it’s easier to modify the database and to write applications that manipulate the data.
If you just need information about application objects that appear in the user interface, you can use the Documenter (Tools menu, Analyze submenu) to produce a report on those objects. However, to obtain information about the DAO objects, properties, and methods that can’t be manipulated by way of the user interface, you must use Visual Basic.
See Also For more information on the Documenter, search the Help index for “Documenter.”
Because DAO objects are organized in a hierarchy of nested containers, you can map the database by walking through the hierarchy with a series of nested For...Each loops. Each element in the collection can be accessed with a statement block with the following syntax:
For Each object In collection
statements ' Map the properties of the object.
Next object
See Also For information on individual properties that are exposed when you map a database, search the Help index for the name of the property.
The following code maps the current database. For brevity, the Attributes property is fully mapped only for each TableDef object. If you want to map attributes of the other objects, simply use the code for the TableDef object attributes as a model and insert the appropriate statements. This example assumes that the user running this code has at least Read Design permission on all tables in the database.
Sub MapDatabase()
Dim dbs As Database, tdf As TableDef, fld As Field
Dim idx As Index, rel As Relation
Dim intX As Integer
Set dbs = CurrentDb
' Map the Database properties.
Debug.Print "DATABASE"
Debug.Print "Name: ", dbs.Name
Debug.Print "Connect string: ", dbs.Connect
Debug.Print "Transactions supported?: ", dbs.Transactions
Debug.Print "Updatable?: ", dbs.Updatable
Debug.Print "Sort order: ", dbs.CollatingOrder
Debug.Print "Query time-out: ", dbs.QueryTimeout
' Map the TableDef objects.
Debug.Print "TABLEDEFS"
For Each tdf in dbs.TableDefs
Debug.Print "Name: ", tdf.Name
Debug.Print "Name: ", tdf.DateCreated,
Debug.Print "Name: ", tdf.LastUpdated,
If tdf.Updatable = True Then
Debug.Print "Updatable",
Else
Debug.Print "Not Updatable",
End If
' Show the TableDef Attributes.
Debug.Print Hex$(tdf.Attributes)
If (tdf.Attributes And dbSystemObject) <> 0 Then
Debug.Print "System object"
End If
If (tdf.Attributes And dbAttachedTable) <> 0 Then
Debug.Print "Linked table"
End If
If (tdf.Attributes And dbAttachedODBC) <> 0 Then
Debug.Print "Linked ODBC table"
End If
If (tdf.Attributes And dbAttachExclusive)<> 0 Then
Debug.Print "Linked table opened in exclusive mode"
End If
' Map Fields for each TableDef object.
Debug.Print "FIELDS"
For Each fld in tdf.Fields
Debug.Print "Name: ", fld.Name
Debug.Print "Type: ", fld.Type
Debug.Print "Size: ", fld.Size
Debug.Print "Attribute Bits: ", Hex$(fld.Attributes)
Debug.Print "Collating Order: ", fld.CollatingOrder
Debug.Print "Ordinal Position: ", fld.OrdinalPosition
Debug.Print "Source Field: ", fld.SourceField
Debug.Print "Source Table: ", fld.SourceTable
' Show the Field Attributes here.
Debug.Print Hex$(fld.Attributes)
If (fld.Attributes And dbSystemObject) <> 0 Then
Debug.Print "System Object"
End If
Next fld ' Get the next Field in the TableDef object.
' Map Indexes for each TableDef object.
Debug.Print "INDEXES"
For Each idx in tdf.Indexes
' Set the Index variable.
Set idx = tdf.Indexes(intX)
Debug.Print "Name: ", idx.Name
Debug.Print "Clustered: ", idx.Clustered
Debug.Print "Foreign: ", idx.Foreign
Debug.Print "IgnoreNulls: ", idx.IgnoreNulls
Debug.Print "Primary: ", idx.Primary
Debug.Print "Unique: ", idx.Unique
Debug.Print "Required: ", idx.Required
' Map the Fields of the Index.
For Each fld in idx.Fields
Debug.Print "Name: ", fld.Name
Next fld ' Get the next Field in the Index.
Next idx ' Get the next Index in the TableDef object.
Next tdf ' Get next TableDef in the Database.
' Map the Relation objects.
Debug.Print "RELATIONS"
For Each rel in dbs.Relations
Debug.Print "Name: ", rel.Name
Debug.Print "Attributes: ", rel.Attributes
Debug.Print "Table: ", rel.Table
Debug.Print "ForeignTable: ", rel.ForeignTable
' Map the Fields of the Relation objects.
For Each fld in rel.Fields
Debug.Print "Name: ", fld.Name
Debug.Print "ForeignName: ", fld.ForeignName
Next fld ' Get the next Field in the Relation object.
Next rel ' Get next Relation object in the Database.
End Sub