Documenting tables in an Access database

Recently I published an article on documenting queries in your database. This article will help you to document your table fields and indexes.

There are six functions used in this tutorial; two each to create and populate the documenter tables, one to control them, and a utility function to check for the presence of the documenter tables.

You can download a sample database containing the code and a simple menu form. To use, import the form and the module into your database, open the form and click the Document Tables button.

• Download the sample here.

Creating the documenter tables

There are two tables for this technique; ztblTableFields and ztblIndexes. They can be created using the following scripts:

''==================================================================
''CreateztblTableFields function.
''Description: Creates a table to hold field names and
''properties. First tries to delete the table, to
''prevent an error when the SQL statement runs.
''Created 27 Nov 2007 by Denis Wright.
''Updated 21 Dec 2007 by Denis Wright.
''Updated 22 Apr 2008 by Denis Wright -- added Description field
''==================================================================
Function Create_ztblTableFields()
  Dim sSQL As String

  DoCmd.SetWarnings False

  On Error Resume Next
  DoCmd.RunSQL "DROP TABLE ztblTableFields"
  On Error GoTo 0

  sSQL = "CREATE TABLE ztblTableFields " _
    & "( TableFieldID COUNTER, " _
    & "TableName TEXT(50), " _
    & "FieldName TEXT(50), " _
    & "FieldType LONG, " _
    & "FieldRequired INTEGER, " _
    & "FieldDefault TEXT(50), " _
    & "FieldDescription TEXT(255) )"

  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True

End Function

''===================================================================
''CreateztblIndexes function.
''Description: Creates a table to hold field names and
''index properties. First tries to delete the table, to
''prevent an error when the SQL statement runs.
''Created 27 Nov 2007 by Denis Wright.
''Updated 22 Apr 2007 by Denis Wright -- Added IndexIgnoreNulls field
''===================================================================
Function Create_ztblIndexes()
  Dim sSQL As String

  DoCmd.SetWarnings False

  On Error Resume Next
  DoCmd.RunSQL "DROP TABLE ztblIndexes"
  On Error GoTo 0

  sSQL = "CREATE TABLE ztblIndexes " _
    & "( TableIndexID COUNTER, " _
    & "TableName TEXT(50), " _
    & "IndexName TEXT(50), " _
    & "IndexRequired INTEGER, " _
    & "IndexUnique INTEGER, " _
    & "IndexPrimary INTEGER, " _
    & "IndexForeign INTEGER, " _
    & "IndexIgnoreNulls INTEGER )"

  DoCmd.RunSQL sSQL
  DoCmd.SetWarnings True

End Function

These functions use DDL to create the tables and their fields. As their structures are very similar, I will just describe the first one.

1. If the table already exists, delete it using a DROP statement. If the table doesn't already exist, this will cause an error, so the On Error Resume Next line tells VBA to ignore the error. On Error GoTo 0 turns the error handling back on again. 
2. Using a CREATE TABLE statement, create the table and define the fields. The DDL data types are shown below. Note the following:
   1. All of the field definitions are enclosed in a single set of parentheses. There is a space between these parentheses and the text.
   2. Each field definition has the syntax FieldName FIELDTYPE(Optional Size). If you insist on using spaces in your field names (not a good practice), you will need to enclose those field names in square brackets.
3. Run the SQL to create the table
4. DoCmd.SetWarnings is used to toggle data warnings off, then on again once the routine has completed.

Data types used in DDL statements

Field Type	DDL Data Type
Text	TEXT(50)
Memo	MEMO
Byte	BYTE
Integer	INTEGER
Long Integer	LONG
AutoNumber	COUNTER
Single	SINGLE
Double	DOUBLE
Currency	CURRENCY
Replication ID	GUID
Date/Time	DATETIME
Yes/No	YESNO
Long Binary (OLE Object)	LONGBINARY
Binary	BINARY(50)

Populating table fields and indexes

The two functions below populate the documenter tables. Each is passed the name of an existing table, and loops through the fields or indexes of that table, writing the properties of each field / index to a new record in the corresponding documenter table.

The On Error Resume Next line in ListTableFields causes the code to skip properties that do not exist for that field.

''====================================================================
''ListTableFields function
''Takes: The name of a table to be processed.
''Returns: The name and selected properties of the table's fields.
''Created: 27 Nov 2007 by Denis Wright
''Updated: 22 Apr 2008 by Denis Wright -- added Description property
''====================================================================
Function ListTableFields(strTableName As String)
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim rst As DAO.Recordset
  Dim fld As DAO.Field

  Set dbs = CurrentDb()

  'define the tabledef and write field properties to ztblTableFields
  Set tdf = dbs.TableDefs(strTableName)
  Set rst = dbs.TableDefs("ztblTableFields").OpenRecordset
  On Error Resume Next
  For Each fld In tdf.Fields
    rst.AddNew
    rst!TableName = tdf.Name
    rst!FieldName = fld.Name
    rst!FieldType = fld.Type
    rst!FieldSize = fld.FieldSize
    rst!FieldRequired = fld.Required
    rst!FieldDefault = fld.DefaultValue
    rst!FieldUpdatable = fld.DataUpdatable
    rst!FieldDescription = fld.Properties("Description")
    rst.Update
  Next fld

  rst.Close
  Set rst = Nothing
  Set tdf = Nothing
  Set dbs = Nothing

  On Error GoTo 0
End Function

''====================================================================
''ListTableIndexes function
''Takes: The name of a table to be processed.
''Returns: The name and selected properties of the table's indexes.
''Created: 27 Nov 2007 by Denis Wright
''====================================================================
Function ListTableIndexes(strTableName As String)
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim rst As DAO.Recordset
  Dim idx As DAO.Index

  Set dbs = CurrentDb()

  'define the tabledef and write field properties to ztblTableFields
  Set tdf = dbs.TableDefs(strTableName)
  Set rst = dbs.TableDefs("ztblIndexes").OpenRecordset
  For Each idx In tdf.Indexes
    rst.AddNew
    rst!TableName = tdf.Name
    rst!IndexName = idx.Name
    rst!IndexRequired = idx.Required
    rst!IndexUnique = idx.Unique
    rst!IndexPrimary = idx.Primary
    rst!IndexForeign = idx.Foreign
    rst!IndexIgnoreNulls = idx.IgnoreNulls
    rst.Update
  Next idx

  rst.Close
  Set rst = Nothing
  Set tdf = Nothing
  Set dbs = Nothing
End Function

Controlling the flow

This is the function that controls the others. It does the following:

1. Check for the two documenter tables. If they exist they are cleared; otherwise they are created.
2. Loop through the TableDefs collection, ignoring temporary and system tables. Each table name is passed to the two functions that document the table's fields and indexes.

''=======================================================================
''DocumentTables function.
''Description: Checks to see if the two documenter tables exist. If not,
''they are created; otherwise they are cleared.
''The function then loops through the TableDefs collection and documents
''all tables except for system and temporary tables.
''Created: 27 Nov 2007 by Denis Wright.
''Modified: 21 Dec 2007 -- turn off warnings
''=======================================================================
Function DocumentTables()
  Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef

  DoCmd.SetWarnings False

  'check that the documentor tables exist; if not, create them.
  'otherwise, remove contents of documenter tables before re-populating
  If Not TableExists("ztblTableFields") Then
    Call Create_ztblTableFields
  Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM ztblTableFields"
    DoCmd.SetWarnings True
  End If

  If Not TableExists("ztblIndexes") Then
    Call Create_ztblIndexes
  Else
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM ztblIndexes"
    DoCmd.SetWarnings True
  End If

  Set dbs = CurrentDb()

  'exclude non-system and temp tables, otherwise list field and index properties
  For Each tdf In dbs.TableDefs
    If Left(tdf.Name, 4) <> "Msys" And Left(tdf.Name, 4) <> "~TMP" Then
      Call ListTableFields(tdf.Name)
      Call ListTableIndexes(tdf.Name)
    End If
  Next tdf
  DoCmd.SetWarnings True

  Set dbs = Nothing
End Function

Checking that a table exists

To check whether or not a table exists, you can search for it in the TableDefs collection and look for the result of the Err function. Here is one way to do that:

''==========================================================
''TableExists function
''Takes: The name of a table whose existence we want to test.
''Returns: True if table is found, False otherwise.
''Description: Test that a table exists by trying to assign
''a TableDef to it.
''If the table has not been found, an error is generated and the
''TableExists function returns False; otherwise it is True.
''Created: 26 Nov 2007 by Denis Wright.
''Modified: 7 July 2010.
''==========================================================
Function TableExists(strTable As String) As Boolean
  Dim tdf As DAO.TableDef

  TableExists = True

  On Error GoTo Err_Handle
  Set tdf = CurrentDb.TableDefs(strTable)

Err_Exit:
  Set tdf = Nothing
Exit Function

Err_Handle:
  If Err.Number = 3265 Then 'table not found
    'exit without error message; the caller function
    'will create the table
  Else
    MsgBox "Error " & Err.Number & "; " & Err.Description
  End If
  TableExists = False
  Resume Err_Exit

End Function

The code does the following:

1. Define a TableDef, and try to set that object whose existence you are trying to check
2. In the error handling code, look for the Err.Number returned by this operation.
   1. If the value is 0 (i.e. the table exists), set the function value to True.
   2. For any other value the function returns False.

Using the documenter

Paste all 6 functions into a new module and call it basDocumentation. Or, if you have already created that module for the query documenter, add this code to the existing module.

Run the DocumentTables function by placing your cursor in the code and pressing the F5 key. Alternatively, run it from a button on a form by adding this code to the button's Click event:

  basDocumentation.DocumentTables

If you are unsure how to create event code in Access, see this tip.

Alternatively, download the sample database, import the module and the form into your database, and run the code by pressing the Document Tables button on the  form.

  Home | About | Links | Contact | Excel | Access