In the past, I've often used Access databases to prototype applications that would later be ported to SQL Server. An Access database can easily be included in version control database, making reverting to previous versions easy.

While I now use SQL Express for this purpose I thought I would share a routine that I had written for enumerating the tables and fields of an access database, something like a database dictionary. Routines that do this sort of enumeration are very common for SQL Server, and less so for Access.

To use the EnumerateDatabase function listed here below:

1. Create a new module in your access database, then paste the code into the module.
2. Once the code is in the module, bring up the immediate window by pressing <CTRL> <G>
3. In the immediate window, type EnumerateDatabase and press <ENTER>
   

You'll see the querydefs, tabledefs and fields of each tabledef listed to the immediate window.

The function could easily be modified to put the information into a table for reporting.

Option Compare Database   'Use database order for string comparisons

Sub EnumerateDatabase()
    On Error Resume Next

    Dim MyDatabase As Database
    
    Set MyDatabase = DBEngine.Workspaces(0).Databases(0)

    ' Enumerate query definitions.
    If MyDatabase.QueryDefs.Count > 0 Then
        Debug.Print "QueryDefs"
        Dim iQueryDef As Integer
        For iQueryDef = 0 To MyDatabase.QueryDefs.Count - 1
            Debug.Print "  "; MyDatabase.QueryDefs(iQueryDef).Name
        Next iQueryDef
    End If
    Debug.Print
        
    ' Enumerate table definitions.
    If MyDatabase.TableDefs.Count > 0 Then
        Dim iTable As Integer
        Dim iField As Integer
        
        Debug.Print "TableDefs"
        Debug.Print "Name, DateCreated"
        For iTable = 0 To MyDatabase.TableDefs.Count - 1
            If InStr(1, MyDatabase.TableDefs(iTable).Name, "MSys") = 0 Then
                Debug.Print
                Debug.Print "  "; MyDatabase.TableDefs(iTable).Name;
                Debug.Print ", "; MyDatabase.TableDefs(iTable).DateCreated
                Debug.Print "   Field Name, Type, Size, Required, Desc"
                Debug.Print "   ----------------------------"
                For iField = 0 To MyDatabase.TableDefs(iTable).Fields.Count - 1
                    Debug.Print "   "; MyDatabase.TableDefs(iTable).Fields(iField).Name;
                    Debug.Print " "; whattype(MyDatabase.TableDefs(iTable).Fields(iField).Properties![Type]);
                    Debug.Print " "; MyDatabase.TableDefs(iTable).Fields(iField).Properties![Size];
                    Debug.Print " "; MyDatabase.TableDefs(iTable).Fields(iField).Properties![Required];
                    Debug.Print " "; MyDatabase.TableDefs(iTable).Fields(iField).Properties![Description];
                    Debug.Print
                Next iField
            End If
        Next iTable
    End If
    Debug.Print
    
    ' Enumerate relationships.
    If MyDatabase.Relations.Count > 0 Then
        Dim iRelationship As Integer
        Debug.Print "Relation: Name, Table, ForeignTable"
        For iRelationship = 0 To MyDatabase.Relations.Count - 1
            Debug.Print "  "; MyDatabase.Relations(iRelationship).Name;
            Debug.Print ", "; MyDatabase.Relations(iRelationship).Table;
            Debug.Print ", "; MyDatabase.Relations(iRelationship).ForeignTable
        Next iRelationship
        Debug.Print
    End If
    
    ' Enumerate built-in properties of MyDatabase.
    Debug.Print "MyDatabase.Name: "; MyDatabase.Name
    Debug.Print "MyDatabase.CollatingOrder: "; MyDatabase.CollatingOrder
    Debug.Print "MyDatabase.Connect: "; MyDatabase.Connect
    Debug.Print "MyDatabase.QueryTimeout: "; MyDatabase.QueryTimeout
    Debug.Print "MyDatabase.Transactions: "; MyDatabase.Transactions
    Debug.Print "MyDatabase.Updatable: "; MyDatabase.Updatable
    Debug.Print

    MyDatabase.Close    ' File remains on disk.

End Sub

Function whattype(n As Variant) As String
    'function returns name of type corresponding to n

    Select Case n
        Case DB_DATE
            whattype = "Date"
        Case DB_TEXT
            whattype = "Text"
        Case DB_MEMO
            whattype = "Memo"
        Case DB_BOOLEAN
            whattype = "Boolean"
        Case DB_INTEGER
            whattype = "Integer"
        Case DB_LONG
            whattype = "Long"
        Case DB_CURRENCY
            whattype = "Currency"
        Case DB_SINGLE
            whattype = "Single"
        Case DB_DOUBLE
            whattype = "Double"
        Case DB_BYTE
            whattype = "Byte"
        Case DB_LONGBINARY
            whattype = "Long Binary"
        Case Else
            whattype = "Unknown type"
    End Select

End Function

You may be interested in building upon the above routine to perform tasks like automatically creating tables, migrating Access database schema to other DBMS or the like. If so, this short list of Access system objects may prove useful: