Add Table/Field Descriptions with VBA

[terryw]

I am defining tables and their fields in code and appending them to my database. Is there a way to add table and field descriptions also through code?

 

[VicRauch]

Terry, I looked into this and can not find a way of adding Description text with VBA. I have not heard of it being done in the past either. But I do like hearing about someone that likes to document their work. Keep it up!

 

[SydneyGeek]

Hi Terry,

As Vic said, I don't think it's doable. This post shows the syntax for the ALTER TABLE statement, and changing field descriptions is not in that list.

It may be possible to use DAO to loop through the TableDefs collection, find the required field and modify it there, but SQL doesn't appear to have the answer.

Denis

 

[VicRauch]

I looked for Description within the DAO TableDefs and did not find it. I think I maybe should have said what all I did try, but now I can't remember will enough to make the list. Sorry.

 

[Andrew Fergus]

I too have been searching for a solution to this but without success. It seems logical that you should be able to, but obviously not...

Andrew

 

[Ahnold]

You can use CreateProperty method. Just make the first argument the name of a built-in property. In this case "Description".

Sub CreateTable()
    Dim myDb As Database
    Dim myTb As TableDef
    Dim myF As Field
    Dim myP As Property
    
    Set myDb = CurrentDb
    Set myTb = myDb.CreateTableDef("Employees")
    With myTb
        .Fields.Append .CreateField("ID", dbLong)
        .Fields.Append .CreateField("Name", dbText, 25)
    End With
    myDb.TableDefs.Append myTb
    myDb.TableDefs.Refresh
    Set myF = myTb.Fields("ID")
    With myF
        Set myP = .CreateProperty("Description", dbText, "This is Employee ID")
        .Properties.Append myP
    End With
    Set myF = myTb.Fields("Name")
    With myF
        Set myP = .CreateProperty("Description", dbText, "This is Employee Name")
        .Properties.Append myP
    End With
End Sub

 

[terryw]

Thanks everyone. I did some digging and finally found the solution. Here is a function that will create the property if it does not exist.

The key is that your object must physically exist before you can perform this function to change its property. For example, if you want to change a field property, you must create and append the new field first. I was stuck on that for a while.

Sub MyCode()
    SetAccessProperty fld, "Description", 10, "This is my Description"
    SetAccessProperty fld, "Caption", 10, "This is my Caption"
End Sub

Function SetAccessProperty(obj As Object, _
    strName As String, intType As Integer, _
    varSetting As Variant) As Boolean

    Dim prp As DAO.Property
    Const conPropNotFound As Integer = 3270
  
    On Error GoTo ErrorSetAccessProperty
    obj.Properties(strName) = varSetting
    obj.Properties.Refresh
    SetAccessProperty = True
  
ExitSetAccessProperty:
    Exit Function
  
ErrorSetAccessProperty:
    If Err = conPropNotFound Then
      Set prp = obj.CreateProperty(strName, 10, varSetting)
      obj.Properties.Append prp
      obj.Properties.Refresh
      SetAccessProperty = True
      Resume ExitSetAccessProperty
    Else
      MsgBox Err & ": " & vbCrLf & Err.Description
      SetAccessProperty = False
      Resume ExitSetAccessProperty
    End If
  End Function

Thanks again.