Building a Data Dictionary in Access 97

A great deal of information can be stored in table definitions of Access databases. This information can be easily extracted to build documentation for a database or a data dictionary for a query builder. In this article, we demonstrate how to navigate Access Database tabledef structures to build a data dictionary for an Access application.

Access allows a developer to store descriptions of fields in the table design with the field definition. Other useful information that can be stored are the

caption (which is the default label for the field),
rowsource for fields that will be edited and displayed using listboxes and combo boxes
field data type
field default value
validation rule

and many more.

This information is then used by Access form wizards to draw the appropriate control when a designer drags a field onto the form layout. It is also used when a user edits records in datasheet mode to provide the appropriate control.

This information can also be extremely handy if one wants to create documentation that details the definitions of each field or to build a data dictionary for a query builder.

Outlined below is a subroutine that navigates a tabledef and dumps the field names, captions, descriptions, data types of each field in the table into another table that we will call the data dictionary table. The data dictionary table should have the following fields:

Table - Text (100)
Field - Text (100)
Display - Text (100)
Encapsulator - Text (3)
Description - Memo
LookupSQL -Text (255)

Public Sub GenerateDataItemsForTable(aTableName As String, aDataDictionaryTable As String)
'***       Usage: GenerateDataItemsForTable "MyTable", "MyDataDictionaryTable"  ***
'**********************************************************************************

'***-- aTableName -  Table to grab field info from
'***-- aDataDictionaryTable - Data dictionary to put items in most confirm
'***-- to a particular structure

    Dim tdf As TableDef, fldCur As Field, colTdf As TableDefs
    Dim rstDatadict As Recordset
    Dim i As Integer, j As Integer, k As Integer
    
    Set rstDatadict = CurrentDb.OpenRecordset(aDataDictionaryTable)
    
    
    Set colTdf = CurrentDb.TableDefs
    
    Set tdf = colTdf(aTableName)
'***-- Put data items in table

    For i = 0 To tdf.Fields.Count - 1
    
            Set fldCur = tdf.Fields(i)
                
            rstDatadict.AddNew
                rstDatadict![Table] = tdf.Name
                rstDatadict![Field] = "[" & tdf.Name & "]" & ".[" & fldCur.Name & "]"
                rstDatadict![Display] = fldCur.Name
                    
            '*** --SQL encapsulator for a date type 8 is # and string type 10 is single quote
            '*** --all remaining numbers are nothing
                rstDatadict![encapsulator] = Switch(fldCur.Type = 10, "'", _
                                                    fldCur.Type = 8, "#", True, "")
                rstDatadict!DataType = Switch(fldCur.Type = 1, "True/False", _
                                              fldCur.Type = 10, "Text", _
                                              fldCur.Type = 8, "Date", True, "Number")
                                        
                For j = 0 To tdf.Fields(i).Properties.Count - 1
                    If fldCur.Properties(j).Name = "Description" Then
                        rstDatadict![Description] = fldCur.Properties(j).Value
                    End If
                        
                    If fldCur.Properties(j).Name = "Caption" Then
                        rstDatadict![Display] = fldCur.Properties(j).Value
                    End If
                        
                    If fldCur.Properties(j).Name = "Rowsource" Then
                        rstDatadict![LookupSQL] = fldCur.Properties(j).Value
                    End If
                Next j
                
            rstDatadict.Update
    
    Next i
        
End Sub

This subroutine navigates through each field definition that a tabledef comprises. Each field has a property bag which the subroutine also navigates through. Some of these properties exist only if they have been set by a designer. For example if no Rowsource is set for a field, then the field definition will not have a "Rowsource" property in its property bag. This routine only adds a couple of these to the data dictionary table. Others can be added. Note that the routine loops through each property but selectively targets some for addition to the dictionary table. To see a listing of other properties, one can put a Debug.Print fldCur.Properties(j).Name inside the For j loop.