query to get table and field information

[Graham]

Hi

I'd like to be able to create a query in MS Access (Office XP version) that
has the following output:
- list of tables: table name, primary key, etc
- list of fields: field name, type, size, etc

I realise that the analysis tool does this, but the output into Word is not
easily usuable - as part of the design process I'd like to be able to
manipulate the output in Excel and Visio, so a query would be ideal.

Please advise me on how to do this. I'm reasonably experienced at creating
straight-forward queries, but I'm a programming novice, willing to try, but
please don't assume I'll fill in any gaps!

Many thanks.

 

[Allen Browne]

You can get a list of the tables with this query:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] IN (1,4,6)) AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*"))
ORDER BY MsysObjects.Name;

You can list the fields programmatically as shown in this code:
http://allenbrowne.com/func-06.html

Or if you want a better documenter, Jeff Conrad has one here:
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html

 

[David F. Cox]

Way back in the 70's I worked on an IBM DBOMP database that had been built
to include a data dictionary. I am disappointed that Access has not yet got
a proper exposed one.

David F. Cox