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.
 

 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy