Both Access 97 and Access 2000 have a documentation generator under the menu Tools, Analyze. I use it mainly to print out a list of the fields in a table. I use these lists for a variety of things.

(article continues after sponsor spot)

Well, when I first used Access 2000 I found it hard to believe that this documentation feature had become so slow. A posting on a newsgroup got the, "That's the way it is." answer. So, I created my own. Much faster than the one in Access.

Here is how I did it.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Created a table (tblFields) to hold the field names and their types and sizes (ID as autonumber (primary key), TableName as text*50, FieldName as text*50, FieldType as text*20, FieldSize as integer)

Created a form (frmDoc) and put 3 objects on it. (a) text box (strDBName) to type in the name of the database to document, (b) button (cmdDoc) to start the process, and (c) button (cmdExit) to exit.

Put the appropriate code behind the 2 buttons.

Created a report to print out the list of fields (sort on TableName and ID, group by TableName, new page after each table)

Obviously, the key to this faster Access Documentation tool is in the code.

Behind cmdExit

DoCmd.Quit

Behind cmdDoc

Dim fld As Field
Dim tbl As TableDef
Dim rst As Recordset
Dim db As Database
Dim db2 As Database
Dim wrk As Workspace

On Error GoTo error_Print

' Create Microsoft Jet Workspace object.
Set wrk = CreateWorkspace("", "admin", "", dbUseJet)

' Open Database object
Set db = wrk.OpenDatabase(me!strDBName)
Set db2 = CurrentDb

db2.Execute ("delete * from tblFields")

Set rst = db2.OpenRecordset("select * from tblFields")

For Each tbl In db.TableDefs
   If Not left(tbl.Name, 4) = "MSys" Then
   For Each fld In tbl.Fields
      rst.AddNew
      rst!TableName = tbl.Name
      rst!FieldName = fld.Name
      rst!FieldType = basFieldType(fld.Type)
      rst!FieldSize = fld.Size
      rst.Update
   Next fld
   End If
Next tbl
rst.Close

DoCmd.OpenReport "rptDoc", acViewPreview

Exit Sub

error_Print:
   MsgBox Err.Number & " - " & Err.Description

End Sub

'------------------------------------------------------

Function basFieldType(intType As Integer) As String

   Select Case intType
   Case dbBoolean
      basFieldType = "Boolean"
   Case dbByte
      basFieldType = "Byte"
   Case dbInteger
      basFieldType = "Integer"
   Case dbLong
      basFieldType = "Long Integer"
   Case dbCurrency
      basFieldType = "Currency"
   Case dbSingle
      basFieldType = "Single"
   Case dbDouble
      basFieldType = "Double"
   Case dbDate
      basFieldType = "Date"
   Case dbText
      basFieldType = "Text"
   Case dbLongBinary
      basFieldType = "LongBinary"
   Case dbMemo
      basFieldType = "Memo"
   Case dbGUID
      basFieldType = "GUID"
   End Select

End Function

It is left to the reader to expand this to include indexes, and/or convert to ADO. Also, add a common dialogue box feature so you can point and click on the desired database.

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007 Richard W. Killey. All Rights Reserved.