How to List Field's Name in table in Access Using SQL

This SQL works in Access 2016 for Queries, not Tables, but might be useful.

SELECT MSysObjects.Name AS QueryName, 
       IIf(IsNull([Name1]),
           Right([Expression],Len([Expression])-InStr(1,[Expression],".")),[name1])
           AS FieldName
  FROM MSysQueries INNER JOIN MSysObjects 
    ON MSysQueries.ObjectId = MSysObjects.Id
 WHERE MSysQueries.Attribute=6;

This is not SQL, but this question is the top Google result for someone like me who just needs to list out the field names needed for a query to select since Access does not support "* except foo, bar" for getting 99% of a table.

Answer adapted from a social.msdn.com answer by Patrick Wood, Access MVP https://social.msdn.microsoft.com/Forums/office/en-US/1fe5546b-db3f-4e17-9bf8-04f4dee233b7/how-to-list-all-the-field-names-in-a-specified-table?forum=accessdev

Change tablename to your name in the module. This Function should be at the global level:

Function ListTdfFields()
    ' NOT doing DIMs, since then you must enable/attach ADODB or DAO
    ' Dim db As ADO.Database
    Set db = CurrentDb
    tablename = "tblProductLicense"  ' <=== YOUR TABLE NAME HERE
    Set tdf = db.TableDefs(tablename)
    For Each fld In tdf.Fields
        Debug.Print tablename; ".["; fld.Name; "]," ; 
        ' remove ending ; for 1 line per field
    Next
    Debug.Print ""
    Set tdf = Nothing
    Set db = Nothing
End Function

Then add a macro RunCode ListTdfFields() and run it. Output will be sent to the Immediate window of the VBA design view for the module.

There are already some good answers but I decided to add my own twist. Hopefully, they are self-explanatory.

Usage:

• getFieldNames(TableName:="Table1",IncludeBrackets:=True,Delimiter:=vbNewLine,CopyToClipboard:=True)
• getFieldNames(TableName:="Table1",IncludeBrackets:=True,CopyToClipboard:=True)
• getFieldNames(TableName:="Table1",IncludeBrackets:=True)
• getFieldNames(TableName:="Table1")

I use this to build an array of field names:

• Chr(34) & getFieldNames(TableName:="Table1",IncludeBrackets:=False, Delimiter:= Chr(34) & "," & Chr(34)) & Chr(34)

Function getFieldNames(ByVal TableName As String, Optional ByVal IncludeBrackets As Boolean, Optional ByVal Delimiter As String = ", ", Optional ByVal CopyToClipboard As Boolean) As String
    Dim rs As DAO.Recordset

    On Error Resume Next
    Set rs = CurrentDb.OpenRecordset(TableName)
    On Error GoTo 0

    If rs Is Nothing Then Exit Function

    Dim results() As String
    ReDim results(rs.Fields.Count - 1)

    Dim n As Long
    For n = 0 To rs.Fields.Count - 1
        results(n) = rs.Fields(n).Name
    Next
    rs.Close

    Dim result As String
    If IncludeBrackets Then
        result = "[" & Join(results, "]" & Delimiter & "[") & "]"
    Else
        result = Join(results, Delimiter)
    End If


    If CopyToClipboard Then
        With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
            .SetText result
            .PutInClipboard
        End With
    End If

    getFieldNames = result
End Function

Build query:

SELECT Table_Name.*
FROM Table_Name
WHERE False;

Export to Excel You will have each field name in one row without any data. If you select the row and copy, you can paste special>transpose and have them all in a single column.

I came here searching for the same requirement and after refering to this thread, drafted below code for my requirement. The Field names form the source table will be added to an array and later the Field names are assigned to the second table. Just sharing it here, this migh help someone later..

Public Sub FieldName_Change()
Dim intNumberOfFields, intX As Integer
Dim txtTableName,txttmpTableName txtFieldName() As String

intNumberOfFields = GetFieldNames(txtTableName, txtFieldName())
For intX = 1 To intNumberOfFields
  CurrentDb.TableDefs(txttmpTableName).Fields("F" & intX).Name = txtFieldName(intX)
Next intX
End Sub


Public Function GetFieldNames(ByVal txtTableName As String, ByRef txtFiledName() As String) As Integer
Dim rs As DAO.Recordset
Dim n As Long
Dim sResult As String

Set rs = CurrentDb.OpenRecordset(txtTableName)
ReDim txtFiledName(rs.Fields.Count)
With rs
    For n = 0 To .Fields.Count - 1
        txtFiledName(n + 1) = .Fields(n).Name
    Next n
    .Close
    GetFieldNames = n
End With
Set rs = Nothing
End Function

Not sure why no-one came up with the following, I had the same issue and none of the above answers either worked or were suitable (No VB,thank you). This is simple Java (error checking etc omitted)

String query="SELECT TOP 1 * FROM mytable";
ResultSet rs=connectionSQL.createStatement().executeQuery(query);
ResultSetMetaData rsm=rs.getMetaData();
int colcount=rsm.getColumnCount();
for(int f=1; f<=colcount; f++) {
    String coltype=rsm.getColumnTypeName(f);
    String colname=rsm.getColumnName(f);
    .... (whatever)
}

So there's your columNames and the data types. Works like a charm in MSACCESS