[SOLVED] Set format property of field in linked table

Set format property of field in linked table

I have an Access database as front end and SQL Server as back end. I have a link to SomeTable. I want to set then format property of SomeField to Yes/No and try this

CurrentDb.TableDefs("SomeTable").Fields("SomeField").Properties("Format").Value = "Yes/No"

I get an error:

Property not found

Then I open my linked table in design mode, set the Format property of the field and save the table. Now, the code above runs with no error. It appears that I now have created the property. Is there a way to set the format property in code of a field on a freshly linked table?

Solution

First check if the property exist, and create it if not.

See below.

On Error Resume Next

CurrentDb().TableDefs("SomeTable").Fields("SomeField").Properties("Format").Value = "Yes/No"

'if error, the property doesnt exist
If Err.Number = 3270 Then

    Dim prop As DAO.Property
    Set prop = CurrentDb().TableDefs("SomeTable").CreateProperty("Format")
    prop.Type = dbText
    prop.Value = "Yes/No"
        
    CurrentDb().TableDefs("SomeTable").Properties.Append prop

End If