Querying the Structure of Tables in a Database

[Liz]

Is there a way to query the table structures in a database ? One of the
things I need to do is compare Tables A, B, and C and I'd like to be able to
do this without visually inspecting and comparing each field in a back and
forth process ...

Is there a tables collection ? Can you write code something like:

For Each Table in DB
For Each Field in Table
Print [Fieldname]
Next
Next

Ideally, I would like to output all the field names and attributes into a
Master Table ... this is all easy in SQL Server because of the system tables
but I'm lost in Access ....

Thanks in advance for any help ....

L

 

[Allen Browne]

You certainly can, Liz.

The collection is named TableDefs.
Each TableDef has a collection of Fields.
Each Field has Properties.

For an example of how to loop through the Fields of a TableDef, see:
http://allenbrowne.com/func-06.html

To get at the TableDefs in another mdb so you can compare, them, use
OpenDatabase.

Depending on what you need to achieve, you may need to compare the
Properties of each Field against the Properties of the matching Field in the
other database, to detect differences. The Indexes and Relations collections
might also be important.

 

[Tom Wickerath]

Hi Liz,

In addition to the information that Allen provided to you, you might be
interested in downloading and installing a free add-in that will help you
generate this type of documentation:

CSD Tools
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html


If you want a heavy-duty application (not free) try this tool from FMS:

Total Access Detective
http://www.fmsinc.com/products/detective/index.html

It can even compare differences in the data between two tables.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________