Query to list MS Access columns

Posted on 2011-05-09
Last Modified: 2012-05-11
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.  

Question by:MainFrameDBA
    LVL 77

    Accepted Solution

    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.

    LVL 42

    Assisted Solution

    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
    LVL 74

    Assisted Solution

    by:Jeffrey Coachman

    FWIW, this would have worked as well:

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now