schema QUERY to get tables and columns

  • Thread starter Thread starter jade.skaggs
  • Start date Start date
J

jade.skaggs

Hey everyone, I have a rather large DB (200+ tables) and I would like
to write a query that gets the column names and table names side by
side (i intend on copying to excel and using that to help me determine
relationships since there are none setup currently). I found that I
can get all table names using the msysobjects table, but i can not find
any way to get column names. Do MDB's have some sort of information
schema like most DB's have that I can select from?

BTW, the ultimate goal here is to get the info into excel. Using the
documenter has proven to be a terrible option for this since I may need
to sort, search, do whatever with the data, and the 200pg document from
the documenter will only make things more complex.


TIA
 
While I would love to be proven wrong, I don't believe there is any way to
get the field names from a simple query in Access.

The usual approach is to loop through the Fields of the TableDef. Example of
how to do that:
List the names, types, descriptions of fields in a table
at:
http://allenbrowne.com/func-06.html

If you cannot use DAO like that, you could OpenRecordset on the tables in
turn (since you already have the table names), and then loop through the
Fields of the Recordset to get their name, size and data type.

TransferSpreadsheet will export to Excel, with no need to worry about the
field names.

If you want a better documenter, Jeff Contrad has one here:
http://home.bendbroadband.com/conradsystems/accessjunkie/csdtools.html
 
Thanks for the reply. I guess I will need to go with the DAO method.
Thanks for the links!
 
Thanks for the reply. I guess I will need to go with the DAO method.
Thanks for the links!
 
Back
Top