Table Field properties in ADOX

[David Thorpe]

All my attempts to set the Caption property of a field in an ADOX table have failed. Clearly I am missing something, but cannot figure out what it is.

I understand from Access Help that this property must be "created and appended to the Properties collection of the object to which it applies". Help also tells me that I "must correctly specify its Type before appending it and that the Type property can be found in the Settings section of the Help topic (for Caption)".

I have successfully created a table, added columns and modified various properties included in the list of standard ADOX properties of a field. Caption is not listed amongst these standard properties, but I assume it can be added as Help lists it as a property of Field. Can anyone show me how?

 

[Nouba]

I think, there is no way to set the Caption property of a field with ADOX. Use instead good old DAO.

Sub SetFieldCaption( _
                    Tablename As String, _
                    Fieldname As String, _
                    Caption As String)

  Dim fd                As DAO.Field

  On Error GoTo ErrorHandler

  Set fd = DBEngine(0)(0)(Tablename)(Fieldname)

  fd.Properties("Caption") = Caption

ExitHere:
  Set fd = Nothing
  Exit Sub

ErrorHandler:
  If Err = 3270 Then
    fd.Properties.Append fd.CreateProperty("Caption", dbText, Caption)
    Resume Next
  Else
    MsgBox "Error: " & Error$ & "(" & Err & ")"
    Resume ExitHere
  End If
End Sub