Set Table Column Descriptions in MS Access VBA

Public Function SetTableColumnDescriptions(sTable As String) As Boolean
    On Error Resume Next
    
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    Dim strValue As String
    Dim strOut As String
            
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(sTable)
    
    ' Loop through all the fields (columns)
    For Each fld In tdf.Fields
        ' Grab the field name and add spaces to make it human readable
        strValue = fld.Name
        ' See the AddSpacesToName function below.  (see download file)
        strValue = AddSpacesToName(strValue)
        
        ' Attempt to read and set the property
        If fld.Properties("Description") = "" Then
            fld.Properties("Description") = strValue
        End If
        
        ' If the property didn't exist, there will have been an error and
        ' it needs to be added.
        If Err.Number = 0 Then
            ' No problem.  Property existed and the value was set.
        ElseIf Err.Number = 3270 Then
            ' This error means the property was not found.
            ' We need to create it.
            Err.Clear
            
            Set prp = fld.CreateProperty("Description", dbText, strValue)
            fld.Properties.Append prp
            
            If Err.Number <> 0 Then
                MsgBox Err.Description, vbExclamation, "Error"
            End If
        Else
            ' Not sure what the error was.  Report to user.
            MsgBox Err.Description, vbExclamation, "Error"
        End If
    Next
    
    Set prp = Nothing
    Set tdf = Nothing
    Set dbs = Nothing
End Function

This example gets a little tricky, because the way it works is that when no value for Description was previously assigned, the property doesn't actually exist. I saw one example on the newsgroup that was more elegant than this, but it works as shown above, to simply attempt to set the property and if it errs out with Err.Number = 3270, then you know the property needs to be created.

To create a property, you need to call the Field.CreateProperty method when setting your property object and then append it to the Field.Properties collection, as shown above. I'm using an additional function, which code is not shown anywhere in this article, but is available in the download.