Seems like it should be simple, right? Access tables have fields and the fields have properties. We all know how to set property values. You just code something like:

Except that it's not that simple.

Your first clue that this may not be a walk in the park is to realize (as Nuria already has since creating the database tables and fields works) that all this information must be stored in the database file itself. Microsoft makes this point in their documentation:

Most of the properties you can set for DAO objects are DAO properties. These properties are defined by the Microsoft Jet database engine … Some properties that you can set for DAO objects are defined by Microsoft Access, and aren't automatically recognized by the Jet database engine. How you set properties for DAO objects depends on whether a property is defined by the Jet database engine or by Microsoft Access.

Roughly translated this says to me, "Sometimes it works. Sometimes it doesn't." It also says, "Don't even try this on anything but Access databases (DAO Jet engine)." A guide to when it works can be found in the table taken from the Access Help pages, DAO object Microsoft Access-defined properties.

Format is one of the properties listed for a DAO Field object. That means it should be possible. So let's get started.

The heart of the process of setting these properties is much like our initial assumption. The code looks like this:

obj.Properties(strName) = varSetting

In the specific case at hand, the object "obj" must point to the specific Access database field that we're targeting. "strName" is the property we want to set. And "varSetting" is the value we want to give to the property. But it still isn't as simple as this. Microsoft notes these exceptions that have to be handled like this:

This means that you're going to have to use some code that does different things depending on whether the property exists in the properties collection known to your VB program. In code, it looks like this.

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

    Dim prp As 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, intType, 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

Note that error code 3270 tells you that the property has not been added to the collection yet. If this error code is encountered, the property is created with the CreateProperty method and then Append is used to add it to the collection the varSetting value you want.

Here's an example showing how to use this function. First, assign an object reference to the database and tabledef in you Access database. Then open the database and Call the function. Using the Microsoft Northwind Traders sample database, it looks like this. The CompanyName field of the Customers table has been used because it doesn't have any Format property in the original Northwind database and it's easy to see if our program has worked or not. You also have to add a reference to the DAO object library.

To avoid making this an article about Visual Basic format strings, the very simple format string to convert a field to upper case (">") is inserted into the Northwind database.

Private Sub Command1_Click()
    Dim dbs As DAO.Database
    Dim tdfNew As TableDef
    Dim strTableName As String
    Dim strFilePath As String

    strFilePath = "{insert the path to your Northwind database}\Nwind.mdb"

    Set dbs = DBEngine(0).OpenDatabase(strFilePath)
    Set tdfNew = dbs.TableDefs("Customers")
    
    SetAccessProperty tdfNew.Fields("CompanyName"), "Format", 10, ">"
End Sub

Here's the Customers table of the Northwind database before running the program.

After running the program, a check of the date of the Nwind.mdb file will show that the file itself has been updated, and the same view of the Customers table shows that the new Format property is in effect.

Hope this answers your question, Nuria!