Faster Documentation for MS Access Tables

DatabaseLessons.com

'Serving the Microsoft® Access
Community since 1997'

News/Blog

Tables

Queries

Forms

Reports

Modules

Miscellaneous

Subscribe

Free Samples

Videos

Services

Links

About Us

Faster Documentation for MS Access Tables

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. (Access 2000 version, with DAO references turned on)

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.

(p.s. I will be posting a sample database with a working "Access Tables Documentation" generator in the near future. Please subscribe to the e-zine to be kept up to date on this and other news.)

Happy Coding

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.