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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Remove the quotes from
strDataType = "dbBoolean"

You are passing a string into the command, it has to be a datatype constant.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gino5555Author Commented:
Knew it was something simple like that.  Thanks!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.