query to output table design and field details

[Don Grainger]

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!

Sorry if this gets posted twice - I tried posting in my own name but my
posting didn't appear, so I've asked a mate to post for me!

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