ListFields() Part 3: Field Details

In part 2 of this series, we added an option to create fully-qualified field names:

In part two, we're adding an optional String parameter to the ListFields routine.  When passed, the parameter will get prepended to each field name.

That satisfied a particular use case quite well.  I used the routine for a few more months until I started to notice something.  I was able to list a table's field names in the immediate window–which eliminated guesswork about what I had named a particular field–but I kept having to switch back to the application window to check other details about the field:

• Whether or not the field was required
• The field's description
• The field's data type
• The length of text fields

I decided I wanted to be able to see that information in the immediate window, too.  So, I added a second optional parameter that I could use to tell the routine to display the details of the table's fields.

The Approach

As before, we will loop through the fields in the table.  This time though, each field will appear on its own line.  We'll include an asterisk if a field is required.  We'll list the field's data type.  For text fields, we'll also include the length of the field.  And then we'll finish with the field's description, if one is provided.

The DAO Field object has a Type property but it returns an enumerated type (i.e., a long integer) which is not human readable.  To work around this, we'll use my DbTypeToString function to convert the numeric value into something useful.

Subroutine Changes

Here are the changes we are making in this second iteration of the ListFields routine:‌

1. Add argument: Optional ShowDetail As Boolean = False
2. Wrap the existing code that outputs the field name to the immediate window in the Else portion of an If..Then...Else block
3. Add the following lines of code to the Then portion of the above If...Then...Else block
   Debug.Print IIf(Flds(i).Required, "*", " "); NewItem, _
           DbTypeToString(Flds(i).Type) & _
           IIf(Flds(i).Type = dbText, " (" & Flds(i).Size & ")", Null)
4. Change the line...
   Debug.Print FldList & vbCrLf
   ...to...
   If Len(FldList) > 0 Then Debug.Print FldList & vbCrLf

The Full Code

The code below includes required helper functions that I've written about in the past:

• Come Together (the Conc() function)
• Converting the DataTypeEnum Values to Equivalent VBA Types (the DbTypeToString() function)

The code also requires a reference to the DAO library, which is checked by default on newer versions of Access.

The code below can be copied and pasted into a blank standard module to get a fully-working solution that you can easily integrate into your projects:

' ----------------------------------------------------------------
' Procedure : ListFields
' Date      : 12/6/2022 - 12/9/2022
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/listfields-part3/
' Purpose   : Print a comma-delimited list of fields to the
'               immediate window to reduce cognitive load from
'               context switching between code and application windows.
' Part 2    - Add optional Prefix argument to fully qualify field names
' Part 3    - Add optional ShowDetail argument to display field details
' ----------------------------------------------------------------
Sub ListFields(TblName As String, _
               Optional Prefix As Variant = Null, _
               Optional ShowDetail As Boolean = False)
    Const MaxLineLength As Integer = 80

    Dim Db As DAO.Database
    Set Db = CurrentDb

    Dim Flds As DAO.Fields
    Set Flds = Db.TableDefs(TblName).Fields
    
    'Loop through all the fields in the table
    Dim i As Integer, FldList As String
    For i = 0 To Flds.Count - 1
        Dim NewItem As String
        NewItem = Conc(Prefix, Flds(i).Name, ".")
        
        If ShowDetail Then
            Debug.Print IIf(Flds(i).Required, "*", " "); NewItem, _
                        DbTypeToString(Flds(i).Type) & _
                        IIf(Flds(i).Type = dbText, " (" & Flds(i).Size & ")", Null)
        Else
            'If the next item will exceed the max line length,
            '   then print the line and clear the FldList for the next line
            If Len(FldList) + Len(", ") + Len(NewItem) > MaxLineLength Then
                Debug.Print FldList
                FldList = vbNullString
            End If
            FldList = Conc(FldList, NewItem)
        End If
    Next i
    
    'Print the last line to the immediate window
    If Len(FldList) > 0 Then Debug.Print FldList & vbCrLf
End Sub


'---------------------------------------------------------------------------------------
' Procedure : Conc
' Author    : Mike Wolfe
' Date      : 1/23/2009 - 4/1/2015
' Source    : https://nolongerset.com/come-together/
' Purpose   : Concatenates two strings
' Notes     : Eliminates the need to strip off the leading/trailing delimiter when
'               building a string list
'---------------------------------------------------------------------------------------
'
Private Function Conc(StartText As Variant, NextVal As Variant, _
                      Optional Delimiter As String = ", ") As String
    If Len(Nz(StartText)) = 0 Then
        Conc = Nz(NextVal)
    ElseIf Len(Nz(NextVal)) = 0 Then
        Conc = StartText
    Else
        Conc = StartText & Delimiter & NextVal
    End If
End Function

' Source    : https://nolongerset.com/converting-datatypeenum-values/
Private Function DbTypeToString(DbType As DataTypeEnum) As String
    Select Case DbType
    Case dbAttachment: DbTypeToString = "Attachment"
    Case dbBigInt: DbTypeToString = "Big Int"
    Case dbBinary: DbTypeToString = "Binary"
    Case dbBoolean: DbTypeToString = "Boolean"
    Case dbByte: DbTypeToString = "Byte"
    Case dbChar: DbTypeToString = "Char"
    Case dbComplexByte: DbTypeToString = "Complex Byte"
    Case dbComplexDecimal: DbTypeToString = "Complex Decimal"
    Case dbComplexDouble: DbTypeToString = "Complex Double"
    Case dbComplexGUID: DbTypeToString = "Complex GUID"
    Case dbComplexInteger: DbTypeToString = "Complex Integer"
    Case dbComplexLong: DbTypeToString = "Complex Long"
    Case dbComplexSingle: DbTypeToString = "Complex Single"
    Case dbComplexText: DbTypeToString = "Complex Text"
    Case dbCurrency: DbTypeToString = "Currency"
    Case dbDate: DbTypeToString = "Date"
    Case dbDecimal: DbTypeToString = "Decimal"
    Case dbDouble: DbTypeToString = "Double"
    Case dbFloat: DbTypeToString = "Float"
    Case dbGUID: DbTypeToString = "GUID"
    Case dbInteger: DbTypeToString = "Integer"
    Case dbLong: DbTypeToString = "Long"
    Case dbLongBinary: DbTypeToString = "Long Binary"
    Case dbMemo: DbTypeToString = "Memo"
    Case dbNumeric: DbTypeToString = "Numeric"
    Case dbSingle: DbTypeToString = "Single"
    Case dbText: DbTypeToString = "Text"
    Case dbTime: DbTypeToString = "Time"
    Case dbTimeStamp: DbTypeToString = "TimeStamp"
    Case dbVarBinary: DbTypeToString = "VarBinary"
    Case Else: DbTypeToString = "Unknown"
    End Select
End Function

Sample Usage‌

Cover image created with Microsoft Designer