I am attempting to add a field to a backend database and I can’t figure out why this code does not work.
Please show me the error of my ways.
Public Function AddField(strTable As String, strField As String, _
strType As String, Optional strDatabase As String, _
Optional intLength As Integer) As Boolean
On Error GoTo Err_AddField
Dim dbDatabase As DAO.Database
Dim tblTable As DAO.TableDef
Dim fldNewField As DAO.Field
If strDatabase = “” Then
Set dbDatabase = CurrentDb
Else
If FileExist(strDatabase) Then
Set dbDatabase = OpenDatabase(strDatabase)
Else
MsgBox “Database file does not exist?”
End If
End If
Set tblTable = dbDatabase.CreateTableDef(strTable)
Select Case strType
Case “Text”
‘ make sure the size has not been left at zero.
If intLength = 0 Then intLength = 50
Set fldNewField = tblTable.CreateField(strField, dbText, intLength)
Case “Memo”
Set fldNewField = tblTable.CreateField(strField, dbMemo)
Case “Long”
Set fldNewField = tblTable.CreateField(strField, dbLong)
Case “AutoNumber”
Set fldNewField = tblTable.CreateField(strField, dbLong)
With fldNewField
‘ Appending dbAutoIncrField to Attributes
‘ tells Jet that it’s an Autonumber field
.Attributes = .Attributes Or dbAutoIncrField
End With
Case “Integer”
Set fldNewField = tblTable.CreateField(strField, dbInteger)
Case “Double”
Set fldNewField = tblTable.CreateField(strField, dbDouble)
Case “Single”
Set fldNewField = tblTable.CreateField(strField, dbSingle)
Case “Date”
Set fldNewField = tblTable.CreateField(strField, dbDate)
Case “Currency”
Set fldNewField = tblTable.CreateField(strField, dbCurrency)
Case “Boolean”
Set fldNewField = tblTable.CreateField(strField, dbBoolean)
Case “Binary”
Set fldNewField = tblTable.CreateField(strField, dbBinary)
Case “OLEObject”
Set fldNewField = tblTable.CreateField(strField, dbLongBinary)
Case Else
‘ warn the user and skip to the next field
MsgBox “Unrecognised Data Type for field creation”
GoTo SkipFieldAppend
End Select
tblTable.Fields.Append fldNewField
AddField = True