Access 2003 VB Create Field in Table Conversion Error 3421

I am trying to add a field to a table using code.  This is for a series of excel tables that must be imported into access.  I need all the fields in order for the rest of the queries to run properly.

All the variables are declared, and if I run a immediate window check they are all coming through just fine.

The problem is the strDataType (declared Public variable).  If I type the value in directly (i.e., dbDouble, dbText, etc) everything runs just fine, but when I substitute strDataType it fails.  Why is it reading the string "strDataType" instead of the value of the string??

Error code returns:

Create Field Function Error 3421
Data type conversion error

CreateField Error: TableName: SCC_IN_EXCEL
FieldName: blnImport
strDataType: dbBoolean
'These variables are defined elsewhere, but are transferred over just fine.

    strTableName = "SCC_IN_EXCEL"
    strNewFieldName = "blnImport"
    strDataType = "dbBoolean"
    Call CreateField(strTableName, strNewFieldName)

Function CreateField( _
      ByVal TableName As String, _
      ByVal FieldName As String) _
      As Boolean

   On Error GoTo errhandler

   Dim Db As DAO.Database
   Dim fld As DAO.Field
   Dim tdf As DAO.TableDef

   Set Db = Application.CurrentDb
   Set tdf = Db.TableDefs(TableName)

   ' First create a field
   Set fld = tdf.CreateField((FieldName), strDataType)

'As noted in the notes, if I change this to 
'Set fld = tdf.CreateField ((FieldName), dbBoolean)
'then it runs just fine

   With tdf.Fields
      .Append fld
   End With
   CreateField = True

   Set fld = Nothing
   Set tdf = Nothing
   Set Db = Nothing

'   MsgBox "Create Field Complete"
   Exit Function

   CreateField = False

   With Err
      MsgBox "Create Field Function Error " & .Number & vbCrLf & .Description, _
            vbOKOnly Or vbCritical, "CreateField"
       MsgBox "CreateField Error: TableName: " & TableName & _
        Chr(13) & " FieldName: " & FieldName & _
        Chr(13) & "strDataType: " & strDataType
   End With

   Resume ExitHere
End Function

Open in new window

Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

peter57rConnect With a Mentor Commented:
Remove the quotes from
strDataType = "dbBoolean"

You are passing a string into the command, it has to be a datatype constant.
Gino5555Author Commented:
Knew it was something simple like that.  Thanks!
All Courses

From novice to tech pro — start learning today.