query to show table structures in database

[MrTibbs]

Access '97.
Is there a way of querying a given database and reporting basic details (fieldname, size, type, index mode) regarding the structure of the tables?
The Documenter can do it but gives too much information, isn't available except in the Developer edition AFAIK, and doesn't lend itself to easy export.

 

[MrTibbs]

When I searched further I found an answer in the tables forum. http://www.access-programmers.co.uk/ubb/Forum2/HTML/000896.html

[This message has been edited by MrTibbs (edited 01-30-2002).]

 

[MrTibbs]

Link above broken ...

The link above refers to a broken link that gives me a 404 error from
http://snow.he.net/cgi-bin/error404

http://www.access-programmers.co.uk/ubb/Forum2/HTML/000896.html

Should the link be broken? How do I re-find the information?

 

[The_Doc_Man]

If you can't find any other way to do this, look at the code in the Help Files that talks about the TableDefs collection.

Basically, the database contains a TableDefs collection. Each element of the collection is a TableDef (no "s"). The TableDef is itself a collection of field definitions, FieldDefs, plus its other properties. Each FieldDef includes field name, size, format, etc.

There is also a QueryDefs collection with FieldDefs in it analogous to TableDefs.

Finally, there is a Relations table that lists the defined relationships between fields in separate tables.

If you create a recordset and use some simple VBA looping code to visit each definition you want, you can get exactly what you wanted.