Table and Fields Report

[herrhepcat]

I just finished the data-structure on a HUGE database (119 tables). One of my tasks is to document the tables, fields, and relationships of the DB. There are really too many tables to make an ER diagram sensible without delivering a poster. I had Access run the "Documentor" for all of the tables and wound up with a 500 page document that had way more information than I need. I can get the tables and relationships from the MSysObjects and MSysRelationships tables to create my own report, but, where do I get the field names?

-Micah A Rousey
Database Administrator

 

[Steve101]

Here's some sample code that iterates through the tabledefs collection, displaying some field level properties. Adapt it as required.

Hope this helps,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Steve101]

oops; silly me, I forgot to include the code. Here it is:

Function IterateThruTables()
'-------------------------------------------------
'Iterate through all non system tables, displaying
'properties of each field.
'-------------------------------------------------
Set db = CurrentDb
For Each t In db.TableDefs
'--------------------
'bypass system tables
'--------------------
If Left(t.Name, 4) <> &quot;MSys&quot; Then
Set td = db.TableDefs(t.Name)
For Each F In td.Fields
MsgBox t.Name & &quot; &quot; & F.Name
For Each att In td.Fields
MsgBox att.Name & &quot; &quot; & _
att.Type & &quot; &quot; & _
att.Size
Next att
Next F
End If
Next t
db.Close
End Function

Hope this helps,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[MichaelRed]

note that the above (obviously) uses DAO, and NEEDS the allocation (DIM) statements for the database and recordsets to explicitly include the &quot;DAO.&quot; prefix in Ms. A 2K and later. Also, for the later versions, you need to include the DAO 3.6 lib in references.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Steve101]

Good qualification Michael. I must admit to copping out with the declarations sometime's, but the cost is obviously clarity, and lack of portability between versions. I think this will help to set me on the straight and narrow.

Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)