Easy Access to Field Descriptions

This is Part 5 in a series of articles discussing field comments on linked tables.

Part 5 of this series was going to be on a different topic.  Namely, I was going to show how to take column descriptions from a SQL Server-linked TableDef object and use those to update the column descriptions on the source SQL Server table. I decided to bump that article to Part 6.  Here's why.

As I was writing the code, I realized that I needed to loop through each column of the TableDef object and extract its description.  The problem is that the DAO field object does not have a Description property.  Rather, the field object has a Properties collection, of which "Description" may or may not be an item in that collection.

Furthermore, the Properties collection has no Exists method to check for the existence of a particular property.  Our only option, then, is to use an On Error Resume Next statement, attempt to access the property, and then see what happens.

There are several places throughout the Access and Office object model where using On Error Resume Next is the only option.  When I bump into such a situation, I prefer to isolate it as much as possible from the rest of my code.  So that's what I did here.

The Code: ExtractFieldComments()

The code below requires a reference to the Microsoft Scripting Runtime, as it uses early binding to the Dictionary object.  

I prefer early binding to late binding for the Dictionary object especially because the Key, Item arguments of its Add method are positionally opposite those of the Collection's Add method.  

For the same reason, I also try to make it a habit to use named arguments when calling the Add method for both Collections and Dictionaries.

' ----------------------------------------------------------------
' Purpose   : Extract the field comments from a TableDef into a Dictionary.
' Date      : 5/29/2021
' Notes     - The column names are the keys.
'           - The column comments are the values.
'           - Every column is extracted and included in the Dictionary.
'           - Columns with no comment return a null string.
'           - Keys and values are both typed as String.
' ----------------------------------------------------------------
Function ExtractFieldComments(td As DAO.TableDef) As Dictionary
    Dim Dict As New Dictionary
    
    Dim Fld As DAO.Field
    For Each Fld In td.Fields
        Dim Comment As String
        Comment = vbNullString        
        
        On Error Resume Next
        Comment = Fld.Properties("Description")
        On Error GoTo 0
        
        Dict.Add Key:=Fld.Name, Item:=Comment
    Next Fld
    
    Set ExtractFieldComments = Dict
End Function

As you can see, there's not much to this function.  That is very much by design.  As I wrote above, I try to isolate any code that must rely on the On Error Resume Next statement as much as possible.

Sample usage

Here's a procedure to demonstrate the usage of this function:

Sub TestFieldCommentExtraction(TblName As String)
    Dim FldComments As Dictionary
    Set FldComments = ExtractFieldComments(CurrentDb.TableDefs(TblName))
    
    Dim Key As Variant
    For Each Key In FldComments.Keys
        Dim Comment As String
        Comment = FldComments.Item(Key)
        
        Debug.Print Key, Comment
    Next Key
    
End Sub

Here's some sample output:

Image by Snowinspired from Pixabay