C# creating table in Access database and “Data type mismatch” error

It is easy to create Access database tables in code  in C#. But sometimes you might end with error

Data type mismatch

The problem of the mismatch in DataColumn type is due to the OleDbType assigned.

Adding new column takes for second parameter the datatype. There are many types available to cover different data connections

Append(object Item, DataTypeEnum Type = DataTypeEnum.adVarWChar, int DefinedSize = 0);

 public enum DataTypeEnum
    {
        adEmpty = 0,
        adSmallInt = 2,
        adInteger = 3,
        adSingle = 4,
        adDouble = 5,
        adCurrency = 6,
        adDate = 7,
        adBSTR = 8,
        adIDispatch = 9,
        adError = 10,
        adBoolean = 11,
        adVariant = 12,
        adIUnknown = 13,
        adDecimal = 14,
        adTinyInt = 16,
        adUnsignedTinyInt = 17,
        adUnsignedSmallInt = 18,
        adUnsignedInt = 19,
        adBigInt = 20,
        adUnsignedBigInt = 21,
        adFileTime = 64,
        adGUID = 72,
        adBinary = 128,
        adChar = 129,
        adWChar = 130,
        adNumeric = 131,
        adUserDefined = 132,
        adDBDate = 133,
        adDBTime = 134,
        adDBTimeStamp = 135,
        adChapter = 136,
        adPropVariant = 138,
        adVarNumeric = 139,
        adVarChar = 200,
        adLongVarChar = 201,
        adVarWChar = 202,
        adLongVarWChar = 203,
        adVarBinary = 204,
        adLongVarBinary = 205
    }

For types available see

Microsoft Access Tips for Serious Users

ADO Data Types

So in current case replacing  ADOX.DataTypeEnum.adBigInt with ADOX.DataTypeEnum.adInteger will do the trick.

table.Columns.Append(“ApiID”, ADOX.DataTypeEnum.adInteger);