Query to list MS Access columns

Is it possible to query MS Access and create a list of columns for linked tables?  I am able to list linked tables via

SELECT Name      FROM MSysObjects     WHERE Connect Is Not Null      and      Name like 'ID*'       ORDER BY Name;

That provides the names of the linked tables, but what about the column names?  I was hoping column names were stored in MSysDb, but trying to query that table causes an error.  

MainFrameDBAAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
It's not possible to do that in a query.

You can set the rowsoure of a combobox to the relevant table/query and set the rowsource type to Field List  - that will then give you a lsit of the names.

Apart from that you have to use VBA to read the query def.

0
 
dqmqConnect With a Mentor Commented:
The column names are accessible in the tabledef object:
Dim db As Database
Dim td As TableDef
Dim fd As Field
Set db = CurrentDb()
Set td = db.TableDefs("YourTable")  <--change to linked table name

For Each fd In td.Fields
 MsgBox fd.Name
Next
0
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:

FWIW, this would have worked as well:

SELECT Name      
FROM MSysObjects    
WHERE Type=6      and      Name like 'ID*'      
ORDER BY Name
0
All Courses

From novice to tech pro — start learning today.