none
How to list all the field names in a specified table?

    Question

  • Does anyone know what the SQL is to list all the headers (field names) in a specified table? Is there an easier way to do this with Access 2007?
    Wednesday, November 23, 2011 1:48 AM

Answers

  • Does anyone know what the SQL is to list all the headers (field names) in a specified table? Is there an easier way to do this with Access 2007?
    You can do it with code. Put the following code in a standard module and change "MyTableName" to the name of your table and it will list all of the fields in your table in the Immediate window.
    Sub ListTdfFields()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
    
        Set db = CurrentDb
    
        Set tdf = db.TableDefs("MyTableName")
    
        For Each fld In tdf.Fields
            Debug.Print fld.Name
        Next
    
        Set tdf = Nothing
        Set db = Nothing
    End Sub


    Patrick Wood, Access MVP
    Gaining Access http://gainingaccess.net/
    • Marked as answer by S.e.p.y Wednesday, November 23, 2011 2:41 AM
    Wednesday, November 23, 2011 2:19 AM
  • As far as I am aware this can't be done in SQL in Access. Depending on how you need the field names returned and how you intend to use them there are other ways to get the field names.

    If you need to allow a user to select a field name on a form you can use a combo or list box and set the row source to the table or query that you want the fields returned from and set the row source type to field list.

    You could also use a VBA function in a form, report, or module to return an array or string of field names. The following example will return the list of field names as a comma delimited string given a table or query name as the argument.

    Public Function EnumerateFields(ByVal TableOrQueryName As String) As String
        Dim oRst As DAO.Recordset
        Dim sFields As String
        Dim x As Integer
        
        Set oRst = CurrentDb.OpenRecordset("Select * from " & TableOrQueryName & " Where 1<>1;")
        For x = 0 To oRst.Fields.Count - 1
            sFields = sFields & oRst.Fields(x).Name & ", "
        Next x
        Set oRst = Nothing
        EnumerateFields = Left(sFields, Len(sFields)-2)    'Drop the trailing comma
    End Function
    

    If you can give more details on how you will use it I can suggest a better solution. Hopefully this will get you started.


    Eric Williams
    • Marked as answer by S.e.p.y Wednesday, November 23, 2011 2:42 AM
    Wednesday, November 23, 2011 2:19 AM

All replies

  • Does anyone know what the SQL is to list all the headers (field names) in a specified table? Is there an easier way to do this with Access 2007?
    You can do it with code. Put the following code in a standard module and change "MyTableName" to the name of your table and it will list all of the fields in your table in the Immediate window.
    Sub ListTdfFields()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Dim fld As DAO.Field
    
        Set db = CurrentDb
    
        Set tdf = db.TableDefs("MyTableName")
    
        For Each fld In tdf.Fields
            Debug.Print fld.Name
        Next
    
        Set tdf = Nothing
        Set db = Nothing
    End Sub


    Patrick Wood, Access MVP
    Gaining Access http://gainingaccess.net/
    • Marked as answer by S.e.p.y Wednesday, November 23, 2011 2:41 AM
    Wednesday, November 23, 2011 2:19 AM
  • As far as I am aware this can't be done in SQL in Access. Depending on how you need the field names returned and how you intend to use them there are other ways to get the field names.

    If you need to allow a user to select a field name on a form you can use a combo or list box and set the row source to the table or query that you want the fields returned from and set the row source type to field list.

    You could also use a VBA function in a form, report, or module to return an array or string of field names. The following example will return the list of field names as a comma delimited string given a table or query name as the argument.

    Public Function EnumerateFields(ByVal TableOrQueryName As String) As String
        Dim oRst As DAO.Recordset
        Dim sFields As String
        Dim x As Integer
        
        Set oRst = CurrentDb.OpenRecordset("Select * from " & TableOrQueryName & " Where 1<>1;")
        For x = 0 To oRst.Fields.Count - 1
            sFields = sFields & oRst.Fields(x).Name & ", "
        Next x
        Set oRst = Nothing
        EnumerateFields = Left(sFields, Len(sFields)-2)    'Drop the trailing comma
    End Function
    

    If you can give more details on how you will use it I can suggest a better solution. Hopefully this will get you started.


    Eric Williams
    • Marked as answer by S.e.p.y Wednesday, November 23, 2011 2:42 AM
    Wednesday, November 23, 2011 2:19 AM
  • Both awesome replies! Thanks a bunch!
    Wednesday, November 23, 2011 2:42 AM
  • You are very welcome! Thank you for responding to let us know that your need was met. It helps others to know that the solutions work.

     


    Patrick Wood, Access MVP
    Gaining Access http://gainingaccess.net/
    Wednesday, November 23, 2011 4:16 AM