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

Question

Can you please let me know if it is possible to list all fields name in a MS Access table?

Answer 1

I work in ms access far too much.

The only way I know of to do this, would be using vba, and defining for example a recordset, and looping through the fields.

Eg:

Sub ListFields()

dim rst as new adodb.recordset
rst.open "SELECT * FROM SomeTable", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
' Note: adOpenForwardOnly and adLockReadOnly are the default values '
' for the CursorType and LockType arguments, so they are optional here '
' and are shown only for completeness '

dim ii as integer
dim ss as string
for ii = 0 to rst.fields.count - 1
    ss = ss & "," & rst.fields(ii).name
next ii

Debug.Print ss

End Sub

The string variable ss will contain a comma-delimited list of all the column names in the table named "SomeTable".

With a little reformatting of the logic you should be able to insert this data into another table if you wanted to, then query it out.

Does this help?

Answer 2

This version is easy to run and will paste right into Access. Add this function to a module, run with F5, and copy the result from the inputbox:

Public Function FieldNames() As String

    Dim sTable As String
    Dim rs As DAO.Recordset
    Dim n As Long
    Dim sResult As String

    sTable = InputBox("Name of table?")
    If sTable = "" Then
        Exit Function
    End If

    Set rs = CurrentDb.OpenRecordset(sTable)

    With rs
        For n = 0 To .Fields.Count - 1
            sResult = sResult & .Fields(n).Name & vbCrLf
        Next 'n
        .Close
    End With

    Set rs = Nothing

    InputBox "Result:" & vbCrLf & vbCrLf _
            & "Copy this text (it looks jumbled, but it has one field on each line)", _
            "FieldNames()", sResult

End Function

Alternative Output:

User user1003916 supplied an alternative to the InputBox to overcome the 1024 character limit (I have not tested this yet):

Sub CopyText(Text As String)

    'VBA Macro using late binding to copy text to clipboard.
    'By Justin Kay, 8/15/2014

    Dim MSForms_DataObject As Object
    Set MSForms_DataObject = CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    MSForms_DataObject.SetText Text
    MSForms_DataObject.PutInClipboard
    Set MSForms_DataObject = Nothing

End Sub

Answer 3

UPDATE: TO USE THIS SQL QUERY YOU MUST USE A TOOL SUCH AS DBEAVER. ACCESS CLIENT WILL NOT ALLOW YOU TO QUERY IT'S HIDDEN STRUCTURES.

YIKES! IMO: I can't imagine wanting to dive into the dark underbelly of VBA

How to get Access Table Columns by SQL

SELECT * FROM information_schema.columns 
    WHERE TABLE_NAME="YOUR_TABLE_NAME" 
       AND 
    TABLE_SCHEMA="PUBLIC" 

PS I noticed Access called my Schema "PUBLIC"

Above used an Access 2016 and was tested over ODBC and jdbc:ucanaccess and works like a charm.

Example output