How to List Fields in Access Database Tables

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

Last updated on 2021-10-21 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.

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.

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:

Here’s how to execute the function:

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

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

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 sDmwFieldNames() will provide information about the data type (Chr(44) is a comma):

Running the modified sDmwFieldNames() gives this result:

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:

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

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

Your Support for DMW TIPS

Please support this website by making a donation to help keep it free of advertising and to help towards cost of time spent adding new content.

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

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