the Manx Man's MS Access Pages
to subscribe - click here

A Faster Table Documenter (019)

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

Well, when I first used Access 2000 I found it hard to believe that this 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.  (Access 2000 version, with DAO references turned on)

  1. 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)
  2. 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.
  3. Put the appropriate code behind the 2 buttons.
  4. Create 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 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.

e-mail the manx man index of tips click here for a laugh profit from the web

tip # 019  ||  previous tip  ||  next tip