Expand Text Field in Linked accde Database via Access VBA

See more:

MS-Access

VBA

I have a widely distributed Access 2010 application that has split front end and back-end modules (both are ACCDE format). Any changes to the back-end database therefore must be executed within Access VBA code. With the next release I need to expand the size of a text field from 6 to 20 in the linked database. This field (OppAbbrev) is not a key field nor is it involved in any relationships with other tables. I get an error "OppAbbrev.Size not set to 20. Error 0 - " in the function SetPropertyDAO when trying to expand the size of the field.

My code uses the functions SetPropertyDAO and HasProperty (created by Alan Browne) that I have successfully used to modify other properties in the past:

    Set ws = DBEngine.Workspaces(0)
    Set dbs = ws.OpenDatabase(gcstrDatabaseFileSpec, True)
    Set tdf = dbs.TableDefs("Opponent")
    Set fld = tdf.Fields("OppAbbrev")
    Call SetPropertyDAO(fld, "Size", dbText, 20, strErrMsg)
    dbs.Close
    Set dbs = Nothing

The following code is the SetPropertyDAO() function based upon the work of Alan Browne. Note that the HasProperty() function returns true when used in the function. :
Public Function SetPropertyDAO(obj As Object, strPropertyName As String, 
                               intType As Integer, _
                               varValue As Variant, 
                               Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
    'Purpose:   Set a property for an object, creating if necessary.
    'Arguments: obj = the object whose property should be set.
    '           strPropertyName = the name of the property to set.
    '           intType = the type of property (needed for creating)
    '           varValue = the value to set this property to.
    '           strErrMsg = string to append any error message to.

    If HasProperty(obj, strPropertyName) Then
        obj.Properties(strPropertyName) = varValue
    Else
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True

ExitHandler:
    Exit Function
ErrHandler:
    strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _
        ". Error " & Err.Number & " - " & Err.Description & vbCrLf
    Resume ExitHandler
End Function