Documenting Your Database

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

Casa de Bender