Documenting a Table

[Guest]

The Access documenter does not really work for me. I would like to using VBA
print out the field name, field size, field type, field description. Does
anyone know how to do this??

Any help would be appreciated.

 

[Douglas J. Steele]

What does "does not really work" mean? While I'll agree that the documenter
can be verbose, it certainly should be able to show you those 4 things.

 

[Jeff Conrad]

All the tables or just one table?

If you need just one you could try Tools | Analyze | Documentor.
That will print out the fields and a whole bunch of other information as well.
You could do all the tables as well, just be careful which options you select.

As another option I created an Access Add-In called the "John Viescas Table Documentor" that will
list each table and its field properties in an easy-to-read format. It is even easier to use than
the built-in Access Documentor and creates a slick report right in your database that you can just
open again any time right from the Database Window. You could customize the report even more to your
liking if you desire.

Once installed all you have to do is Tools | Add-Ins | John Viescas Table Documentor. Poof! After a
few seconds a slick report is displayed on the screen. In a nut shell the Add-In creates a new key
table in your database, loops through each table (including linked ones) grabbing the information
needed, writes all this information to the key table, creates the report entirely in code, saves it,
and then displays it on the screen for you. That report can be opened again at any time since it is
saved as a new report object in your database. Easy as pie!

I am awaiting word back from John about the possibility of having him post it on his web site where
anyone can download it. In the meantime I do not believe he would have a problem if I sent you a
copy. If you do wish to have a copy please let me know where you would like it sent to (please mung
the e-mail address so you won't get spammed). There is one version for Access 97 and one for 2000
which works with 2000, 2002, and 2003.

 

[Roger Carlson]

David Hare-Scott has also written a simple table documenter. You can
download it here:
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hare-Scott,David

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Of course, if you REALLY want to do it yourself, you could do something like
the following:
'-------------------------------------------
Sub ReadTableAndFieldDescriptions()
On Error GoTo Err_ReadTableAndFieldDescriptions

Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Dim NoDescription As Boolean

Set db = CurrentDb
For Each tbl In db.TableDefs
NoDescription = False
Set prp = tbl.Properties("description")
If NoDescription Then
Debug.Print "Table: " & tbl.Name
Else
Debug.Print "Table: " & tbl.Name & " Desc: " & prp.Value
End If
For Each fld In tbl.Fields
NoDescription = False
Set prp = fld.Properties("description")
If NoDescription Then
Debug.Print "Field: " & fld.Name & " Type: " & _
fld.Type & " Size: " & fld.Size
Else
Debug.Print "Field: " & fld.Name & " Type: " & _
fld.Type & " Size: " & fld.Size & " Desc: " & prp.Value
End If
Next
Next

Exit_ReadTableAndFieldDescriptions:
db.Close
Exit Sub

Err_ReadTableAndFieldDescriptions:
If Err.Number = 3270 Then
NoDescription = True
Resume Next
Else
MsgBox Err.Description
Resume Exit_ReadTableAndFieldDescriptions
End If
End Sub
'-------------------------------------------

Now this function just prints it to the Immediate Window, so you'd have to
modify it for printing or storing in a table or whatever. It also requires
a reference to DAO.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

my question's what does 'mung the email address mean' because i would like to
have one of these myself?

thank you in advance for any help.

 

[Jeff Conrad]

Responded to another post by Ted in different thread.