Create list of fields in Access database??

[fastrunr]

Anyone know a quick way to copy or export a list of fields in an Access table to Excel? I'm trying to generate a list of tables/fields in a database, but the table design view doesn't allow copy/paste.

Thanks!
Liz

 

[PHV]

Have a look at the TableDefs and Fields collection in the DAO help file.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fastrunr]

Hmm, what is this DAO help file and how do I access it?

(sorry, can you dumb that down a little?)

 

[pbrodsky]

There are internal tables that contain the information, however, for a quick and dirty solution, the documenter (Tools ^ Analyze ^ Documenter) will produce a report that can be exported (to Excel, for example)

 

[TimeTraveler]

DAO is "Data Access Object".

I'm looking for a SQL statement that will list the field names (column names) in a table in MS Access. For some reason, this seems to be 'hidden' knowledge. I've checked MS online support, Tek-Tips, SQL Unleashed, and my Oracle books, and for some reason this is seemingly off-limits in a SQL database.

 

[jsteph]

TT,
If it were MSDE or SQL server, you could use the syscolumns table, but there is no 'MSysColumns' table (at least not available to the user) for JET tables. There is MSysQueries, which will list query fields, so you can drop your table fields into a query if you you absolutely had to call this via sql.

Otherwise you could still use the dao tabledefs object in a function that returns the list of fields as a single string. For stuff like this you might create a dummy table--Oracle called theirs 'dual', and you could use functions by doing "Select MyFunction from dual". Sql server allows a SELECT without a table, but this is moot because with SQL Server you have the syscolumns at your disposal.
--Jim