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?

Aluminum

Since you refer to SQL, maybe you want to do something similar to SQL Server like Select object_Name(object_ID),definition from Pastries.SQL_Modules where object_Name(object_ID)='custard'. I believe Access doesn't offer this kind of object visibility. – Oto

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?

Inconformity

Upvote for (perhaps somewhat nominal) use of SQL, plus unmistakable evidence of having worked in MS Access far too much. – Oto

Access isn't always the tool you need, but it's often the tool you have. One note on applying this in Access 2013: I had to enable Microsoft ActiveX Data Objects 6.1 Library in Tools -> References to resolve the ADO library. – Tipi

@John Bingham - just got stuck with an AccessDB and had to come look this up - it saved me from some irritation and probably some depressing thoughts too :) Thanks! – Coincidental

Good, compact method, thanks, and tested to work for me. I've proposed a few extra rows to transform this VBA fragment into a sub that enables the developer to get a one-off output on demand ('SomeTable' still needs to be replaced with the correct table name, of course). – Manta

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

Stalwart

Works great. It list out all fields from whichever table you enter. Only confusing part is... it puts result into that tiny textbox. I thought it was not working but it was working. Just needed to copy paste result into Excel or Notepad++ – Mirabel

Good point, Shai. I added a bit of text to the final dialog so it will be less confusing. – Stalwart

Great script but bear in mind there is a 1024 character limit here. If you have a lot of long column names, it will truncate them. My table has 255 columns, it grabbed only about half. – Prothonotary

Thanks @user1003916. I incorporated your suggestion. – Stalwart

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

Entebbe

Not sure why this idea was voted down. The technique works well. – Entebbe

I popped this into a query window with my table names etc. and it says could not find file information_scheme.mdb – Zeb

I am sorry it appears I forgot one essential instruction. You cannot use the Access client to perform this query on an Access Database. Instead, you must use a tool such as DBeaver. I am not sure why but when the query is run in Access all hell breaks loose. When the same query is run in dBeaver you get your results as expected – Entebbe

Please re-read my instructions, I have tried to provide a clear example how to achieve column names from an access database using pure sql. – Entebbe

I didn't down vote you. I was just replying btw :) Looks like a nice tool – Zeb

Answer 4

You can simply use the Documenter tool. Go to Database Tools > Database Documenter, select the table and press OK.

Oto

I can only agree with you there. Felt the answer might be helpful anyway. Probably not the best thinking. – Oto

It is not the best question, I reckon :) – Wellbeloved

While this works, it's probably the least helpful format you can get. – Conah

Answer 5

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;

Tetreault

thanks this is the easisty way – Mensurable

Answer 6

A quick and dirty method involves Excel. Do the following:

1. Open the table in Datasheet view.
2. Export to an Excel file, using the particular procedure for your Access version.
3. Open Excel file (if not already open).
4. Select and copy the first row containing the headers.
5. Add another sheet to the workbook (if none exist).
6. Click A1.
7. Paste Special > Transpose

The Fields will be pasted in a single column. To find out their Field Index number, in Cell B1 type "0", then series fill down to the last row of the field numbers.

In addition, you can sort the column alphabetically, especially for legacy flat files involving dozens of fields. This really saves a lot of time when I'm trying to convert a flatfile to a relational model.

Boater

Then paste into VSCode or similar to add commas, quotes, etc as you need. – Suellensuelo

Answer 7

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.

Pyrethrum

Answer 8

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

Commove

Answer 9

I would like to share this VBA solution, which I did not write, only slightly modified (changed the tableName to use 'SourceTable'). Once it is run you can query the table it creates. It takes advantage of hidden system tables.

Sub GetField2Description()
'************************************************* *********
'Purpose: 1) Deletes and recreates a table (tblFields)
' 2) Queries table MSysObjects to return names of
' all tables in the database
' 3) Populates tblFields
'Coded by: raskew
'Inputs: From debug window:
' Call GetField2Description
'Output: See tblFields
'************************************************* *********
Dim db As DAO.Database, td As TableDef
Dim rs As Recordset, rs2 As Recordset
Dim Test As String, NameHold As String
Dim typehold As String, SizeHold As String
Dim fielddescription As String, tName As String
Dim n As Long, i As Long
Dim fld As Field, strSQL As String
n = 0
Set db = CurrentDb
' Trap for any errors.
On Error Resume Next
tName = "tblFields"
'Does table "tblFields" exist? If true, delete it;
DoCmd.SetWarnings False
DoCmd.DeleteObject acTable, "tblFields"
DoCmd.SetWarnings True
'End If
'Create new tblTable
    db.Execute     "CREATE TABLE tblFields(Object TEXT (55), FieldName TEXT (55),     FieldType TEXT (20), FieldSize Long, FieldAttributes Long, FldDescription TEXT (20));"
strSQL = "SELECT MSysObjects.Name, MSysObjects.Type From MsysObjects WHERE"
strSQL = strSQL + "((MSysObjects.Type)=1)"
strSQL = strSQL + "ORDER BY MSysObjects.Name;"
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
' Get number of records in recordset
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
End If
Set rs2 = db.OpenRecordset("tblFields")
For i = 0 To n - 1
fielddescription = " "
Set td = db.TableDefs(i)
'Skip over any MSys objects
If Left(rs!Name, 4) <> "MSys" And Left(rs!Name, 1) <> "~" Then
NameHold = rs!Name
On Error Resume Next
For Each fld In td.Fields
tableName = fld.SourceTable

fielddescription = fld.Name
typehold = FieldType(fld.Type)
SizeHold = fld.Size
rs2.AddNew
rs2!Object = tableName
rs2!FieldName = fielddescription
rs2!FieldType = typehold
rs2!FieldSize = SizeHold
rs2!FieldAttributes = fld.Attributes
rs2!FldDescription = fld.Properties("description")
rs2.Update
Next fld
Resume Next
End If
rs.MoveNext
Next i
rs.Close
rs2.Close
db.Close
End Sub
Function FieldType(intType As Integer) As String
Select Case intType
Case dbBoolean
FieldType = "dbBoolean" '1
Case dbByte
FieldType = "dbByte" '2
Case dbInteger
FieldType = "dbInteger" '3
Case dbLong
FieldType = "dbLong" '4
Case dbCurrency
FieldType = "dbCurrency" '5
Case dbSingle
FieldType = "dbSingle" '6
Case dbDouble
FieldType = "dbDouble" '7
Case dbDate
FieldType = "dbDate" '8
Case dbBinary
FieldType = "dbBinary" '9
Case dbText
FieldType = "dbText" '10
Case dbLongBinary
FieldType = "dbLongBinary" '11
Case dbMemo
FieldType = "dbMemo" '12
Case dbGUID
FieldType = "dbGUID" '15
End Select
End Function

Zeb

Answer 10

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.

Gibbsite

I tried essentially this approach before checking Stack Overflow. Unfortunately, someone (who shall remain nameless) had set captions on many of the fields, which turns out to defeat copy and paste. Only the cosmetic version comes through to the clipboard. – Manta

Answer 11

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

Hygrometer

Answer 12

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

Dogeatdog

Answer 13

I like Late binding --> as it make my subroutines more portable. But I use this on Access/Excel 365 without issues

Sub AccessTblDataFieldNames()
'-----------------------------------------------------------------------------
'The macro opens the Historian.accdb database and
'   Captures the Field Names
'
'
'This example uses Late Binding whereby we use the
'   CreateObject() function to define Connection Provider and Connection
'
'Using Late Binding allows your code to run on systems where a specific version
'    of the library may not be installed, as it allows the code to dynamically bind
'    to the library at run-time.
'----------------------------------------------------------------------------

    Dim strPath As String 'Path
    Dim strConnProv As String 'Provider
    Dim strConn As String 'Connection String
    Dim Conn As Object 'Connection
    Dim rsQry As Object 'RecordSet
    Dim strQry As String    'SQL Query
    Dim sht As Worksheet ' We define the name of the worksheet we will copy to
    Dim ii As Integer
    Dim iCnt As Integer
    Dim sFieldName(250) As String ' We will capture the field names in this array
    
    'Specify the file path of the accdb file.
    strPath = ThisWorkbook.Path & "\Associated Files\" & "HistorianData.accdb"
    
    strConnProv = "Microsoft.ACE.OLEDB.12.0;"
    strConn = "Provider =" & strConnProv & "Data Source =" & strPath
    
    'Open the Connection
    Set Conn = CreateObject("ADODB.Connection")
    Conn.Open strConn
    
    'We will define what data is extracted from the Database via the SQL Query
    strQry = "SELECT * FROM Data;"
    Set rsQry = CreateObject("ADODB.Recordset")
    rsQry.Open strQry, Conn
    
    'Lets pick up the Field/Column Names and the number of Fields/Columns in iCnt
    iCnt = 1
    For ii = 0 To rsQry.Fields.Count - 1
         sFieldName(iCnt) = rsQry.Fields(ii).Name
        iCnt = iCnt + 1
    Next ii
    
    Set Conn = Nothing
    Set rsQry = Nothing
    
    ' Write the field names and their corresponding indexes to an Excel worksheet.
    Set sht = ActiveWorkbook.Sheets("Aux")
    sht.Activate
    sht.Range("I1").Select
    For ii = 1 To rsQry.Fields.Count
        ActiveCell.Offset(ii, 0) = sFieldName(ii)
        ActiveCell.Offset(ii, 1) = ii
    Next ii

End Sub

Trichotomy

Answer 14

Seems like this task was easier in older days. Likely this answer is highly version-dependent. It works for me in a quick test against an Access 2007 DB:

select 
Specs.SpecName AS TableName,
Columns.FieldName
from
MSysIMEXColumns Columns
inner join MSysIMEXSpecs Specs on Specs.SpecID = Columns.SpecID
order by
Specs.SpecName,
Columns.FieldName

Commodity

Ah, I was probably fooled by my results since I have import specs set up on the tables I was looking at. Like I said, seems like this task was easier in olden days. Oh well. – Commodity

This only lists Import Link Specs and not table fields at all. – Vagrancy

Answer 15

Give this a go...

    private void Button_OldDDLDump_Click(object sender, EventArgs e)
    {
        string _cnstr = "connectionstringhere";
        OleDbConnection _cn = new OleDbConnection(_cnstr);
        try
        {
            _cn.Open();
            System.Data.DataTable _dt = null;            
            try
            {
                _dt = _cn.GetSchema("tables");
                m_msghelper.AppendArray( DataTableHelper.DataTableToString(_dt) );                               
            }
            catch (Exception _ex)
            {
                System.Diagnostics.Debug.WriteLine(_ex.ToString());
            }
            finally
            {
                _dt.Dispose();
            }
        }
        catch (Exception _ex)
        {
            System.Diagnostics.Debug.WriteLine(_ex.ToString());
        }
        finally
        {
            _cn.Close();
        }
    }

Helper method to dump the database structure to a string array here..

public static class DataTableHelper
{
    public static string[] DataTableToString( System.Data.DataTable dt )
    {
        List<string> _retval = new List<string>();

        foreach (System.Data.DataRow row in dt.Rows)
        {
            foreach (System.Data.DataColumn col in dt.Columns)
            {
                _retval.Add( string.Format("{0} = {1}", col.ColumnName, row[col]) );
            }
            _retval.Add( "============================");
        }
        return _retval.ToArray();
    }
}

Antoniettaantonin

Answer 16

select column_name from information_schema.columns where table_name='table'

Daveen

This does not work. This would work for MS SQL or perhaps MySQL, but not Access. Would be awesome if this would work though. – Lysenko