DMW logo
tel 01732 833085
tonbridge · kent · UK

How to List Fields in Access Database Tables

How to make lists of the names and properties of fields (columns) in the tables of a Microsoft® Access database.

Last updated on 2020-08-07 by David Wallis.


Considerations

I’ll use VBA for creating the lists. VBA can examine many of the properties of a field, including the two we’ll focus on, namely name and data type.

Microsoft Access currently allows for up to 255 fields in a single table, and up to 64 characters in a field name.

Invoice table design

When programming for information about fields, we’ll need to take into account just how much information you want to list and how many fields there are in any one table.

In the following sections I’ll develop a series of routines leading up to one that should satisfy the most demanding call for field information.


Initial Set-Up

To accommodate your VBA code that will perform the listing, I recommend creating a module specifically for it:

  1. In your database, open its Visual Basic Editor (VBE)
  2. From the Insert menu, chose Module
  3. Save the module giving it the name modFieldNames
  4. Select this entire block of code —

    #If VBA7 Then

    Private Declare PtrSafe Function OpenClipboard Lib "User32" (ByVal hWnd As LongPtr) As LongPtr

    Private Declare PtrSafe Function EmptyClipboard Lib "User32" () As LongPtr

    Private Declare PtrSafe Function CloseClipboard Lib "User32" () As LongPtr

    Private Declare PtrSafe Function IsClipboardFormatAvailable Lib "User32" (ByVal wFormat As LongPtr) As LongPtr

    Private Declare PtrSafe Function GetClipboardData Lib "User32" (ByVal wFormat As LongPtr) As LongPtr

    Private Declare PtrSafe Function SetClipboardData Lib "User32" (ByVal wFormat As LongPtr, ByVal hMem As LongPtr) As LongPtr

    Private Declare PtrSafe Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As LongPtr

    Private Declare PtrSafe Function GlobalLock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr

    Private Declare PtrSafe Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As LongPtr) As LongPtr

    Private Declare PtrSafe Function GlobalSize Lib "kernel32" (ByVal hMem As LongPtr) As Long

    Private Declare PtrSafe Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Any, ByVal lpString2 As Any) As LongPtr

    #Else

    Private Declare Function OpenClipboard Lib "user32.dll" (ByVal hWnd As Long) As Long

    Private Declare Function EmptyClipboard Lib "user32.dll" () As Long

    Private Declare Function CloseClipboard Lib "user32.dll" () As Long

    Private Declare Function IsClipboardFormatAvailable Lib "user32.dll" (ByVal wFormat As Long) As Long

    Private Declare Function GetClipboardData Lib "user32.dll" (ByVal wFormat As Long) As Long

    Private Declare Function SetClipboardData Lib "user32.dll" (ByVal wFormat As Long, ByVal hMem As Long) As Long

    Private Declare Function GlobalAlloc Lib "kernel32.dll" (ByVal wFlags As Long, ByVal dwBytes As Long) As Long

    Private Declare Function GlobalLock Lib "kernel32.dll" (ByVal hMem As Long) As Long

    Private Declare Function GlobalUnlock Lib "kernel32.dll" (ByVal hMem As Long) As Long

    Private Declare Function GlobalSize Lib "kernel32" (ByVal hMem As Long) As Long

    Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyW" (ByVal lpString1 As Long, ByVal lpString2 As Long) As Long

    #End If

    Public Sub SetClipboard(sUniText As String)

    #If VBA7 Then

    Dim iStrPtr As LongPtr

    Dim iLock As LongPtr

    #Else

    Dim iStrPtr As Long

    Dim iLock As Long

    #End If

    Dim iLen As Long

    Const GMEM_MOVEABLE As Long = &H2

    Const GMEM_ZEROINIT As Long = &H40

    Const CF_UNICODETEXT As Long = &HD

    OpenClipboard 0&

    EmptyClipboard

    iLen = LenB(sUniText) + 2&

    iStrPtr = GlobalAlloc(GMEM_MOVEABLE Or GMEM_ZEROINIT, iLen)

    iLock = GlobalLock(iStrPtr)

    lstrcpy iLock, StrPtr(sUniText)

    GlobalUnlock iStrPtr

    SetClipboardData CF_UNICODETEXT, iStrPtr

    CloseClipboard

    End Sub

  5. Copy your selection and Paste it into your modFieldNames module.
  6. In the VBE, open the Debug menu and click the Compile command. This will check that all the code has settled in satisfactorily.

What you have now is a module that will be the container for all your code. Once complete, you can import it into any database in which you want to investigate table structure.

Clipboard

The block of code you’ve pasted from above provides a way of copying strings of text — in our case, strings of field names and properties — to Windows’ Clipboard, as you’ll see in the VBA procedures described below.

From Clipboard, you can Paste to wherever you want the field-name information.

The procedures below are extended to include pasting into Notepad.


Simple Listing of Field Names

This is a VBA sub procedure that assembles a list of field names, which it then copies to Window’s Clipboard and pastes from there into Notepad:

Sub dmwFieldNames()

On Error GoTo errHandler

Dim tblName$

Dim rs As DAO.Recordset

Dim list$, n%

Dim msg$

' Get name of table from user

tblName$ = InputBox("Table name?", "FIELD DETAILS")

If tblName$ = "" Then Exit Sub

' Create recordset of named table

Set rs = CurrentDb.OpenRecordset(tblName$)

' Create list of field names

list$ = "FIELDS - [" & tblName$ & "]" & vbNewLine

With rs

For n% = 0 To .Fields.Count - 1

list$ = list$ & _

.Fields(n%).Name & _

vbNewLine

Next n%

End With

' Paste field name info into Clipboard

Call SetClipboard(list$)

' Open new instance of Notepad

Shell "notepad.exe", vbNormalFocus

' Paste field info into Notepad

SendKeys "^v"

procDone:

On Error Resume Next

rs.Close

Set rs = Nothing

Exit Sub

errHandler:

msg$ = "Details of Error" & _

vbNewLine & vbNewLine & _

"Error Number: " & Err.Number & _

vbNewLine & _

"Error Source: dmwFieldNames()" & _

vbNewLine & _

"Error Description: " & Err.Description

MsgBox msg$, vbCritical, "ERROR TRAPPING"

Resume procDone

End Sub

Here’s how to execute the function:

  1. In your database, activate its VBE
  2. Input Call dmwFieldNames into the VBE’s Immediate Window
  3. Press Enter.

This is the result of the function given the simple table named tblInvoice to examine:

Notepad text


More Than Just Field Names

In addition to the names of the fields in a table, a useful bit of information is the data type assigned to each field. The VBA Type property provides the code to get at the data type.

This modification to the list$ statement in dmwFieldNames() will provide information about the data type (Chr(44) is a comma):

' Create list of field names

list$ = "FIELDS - [" & tblName$ & "]" & vbNewLine

With rs

For n% = 0 To .Fields.Count - 1

list$ = list$ & _

.Fields(n%).Name & _

Chr(44) & _

.Fields(n%).Type & _

vbNewLine

Next n%

End With

Running the modified dmwFieldNames() gives this result:

Notepad text

The numerals identify data types. For those who have a memory better than mine, numerals may be enough. But I need them explained. Hence the next topic.


Putting a Name to a Number

VBA itself does not include the syntax for data types expressed in words. So, here’s a function that takes VBA’s Type numeral and interprets it to suit me:

Function dmwFieldType(iType%) As String

On Error GoTo errHandler

Dim fldType$

Select Case iType%

Case 1

fldType$ = "Yes/No"

Case 2

fldType$ = "Byte"

Case 3

fldType$ = "Integer"

Case 4

fldType$ = "Long"

Case 5

fldType$ = "Currency"

Case 6

fldType$ = "Single"

Case 7

fldType$ = "Double"

Case 8

fldType$ = "Date/Time"

Case 9

fldType$ = "Binary"

Case 10

fldType$ = "Short Text"

Case 11

fldType$ = "Long Binary"

Case 12

fldType$ = "Long Text"

Case 15

fldType$ = "GUID"

Case 16

fldType$ = "Large Number"

Case 101

fldType$ = "Attachment"

Case Else

fldType$ = "NOT RECOGNISED"

End Select

procDone:

dmwFieldType = fldType$

Exit Function

errHandler:

fldType$ = "NOT KNOWN"

Resume procDone

End Function

Extending the list$ statement in dmwFieldNames() will provide the additional information about the data type (Chr(44) is a comma):

' Create list of field names

list$ = "FIELDS - [" & tblName$ & "]" & vbNewLine

With rs

For n% = 0 To .Fields.Count - 1

list$ = list$ & _

.Fields(n%).Name & _

Chr(44) & _

.Fields(n%).Type & _

Chr(44) & _

dmwFieldType(.Fields(n%).Type) & _

vbNewLine

Next n%

End With

Running the modified dmwFieldNames() sub procedure yields this result:

Notepad text


Your Support for dmw TIPS

If you’re going to make use of Tips in any of your professional or commercial work, then please consider making a payment in recognition of the time spent publishing this website, keeping it free of advertising.

To make a contribution by PayPal in GBP (£ sterling) —

To make a contribution by PayPal in USD ($ US) —

To say how much you want to pay and to receive
a tax invoice for a GBP (£ sterling) contribution —

Invoice

Thanks, in anticipation.

SSL Cerification

“Finally found your site and now I’m able to get a list of all tables in my Access 365 database. Could you put out a sample that shows how to list all columns in a table? Actually it would be really great if you could show a sample that list all columns for all non-system tables. Sorry for asking for so much but if you could put the output in a table that would be great also. So appreciative for anything you can help with.”

JM (August 2020)