I came across an interesting problem the other day, and I thought I would share the solution.  I have a software that is publicly sold, that uses a VB.NET front end with an access back end.   I realize that I need to make an update, and that part of this update needs to involve me changing the field properties for a specific field.  Changing from an integer to a double is no problem, but I needed to change the 'Format' for the field from NULL to a percent.  There are no SQL commands that can do this as one might think.  UPDATE Table Update Column will allow you to change from int to double, but you can't touch the 'Format' of the field through this.  After you do a little investigation as I did, you will realize that you must do this directly in the database file itself!

The microsoft documentation has the following to say about this:

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.

The solution below is taken from an About.com article, that article can be found in its entirety HERE. This was a great solution to a difficult problem, and I hope this might help someone out in the future.

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:

When you set a property that's defined by Microsoft Access, but applies to a DAO object, the Jet database engine doesn't automatically recognize the property as a valid property. The first time you set the property, you must create the property and append it to the Properties collection of the object to which it applies. Once the property is in the Properties collection, it can be set in the same manner as any DAO property.

If the property is set for the first time in the user interface, it's automatically added to the Properties collection, and you can set it normally.

When writing procedures to set properties defined by Microsoft Access, you should include error-handling code to verify that the property you are setting already exists in the Properties collection.

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.