Create Table for Access using OleDbParameter

Question

I'm currently rebuilding tables from SQL Server to Access via C#.

For that I'm getting the data types used in SQL Server and mapping them to OleDbType objects.

Unfortunately, every time I'm trying to execute my statement for Access an exception will be thrown that there is a syntax error in my "Create Table" - statement. I'm guessing this is because I just add the mapped data types as text and not as OleDbParameters.

Is there a way to create OleDbParameter - objects containing the column name and datatype for "Create Table" - statements?

String accessConnectionString = "Provider=Microsoft.JET.OLEDB.4.0;Data 
Source=" + filepath;

using (OleDbConnection accessConnection = new OleDbConnection(accessConnectionString))
{
     ADOX.Catalog cat = new ADOX.Catalog();
     cat.Create(accessConnectionString);
     OleDbCommand oleCommand = new OleDbCommand();
     oleCommand.Connection = accessConnection;
     oleCommand.CommandType = CommandType.Text;
     accessConnection.Open();

     String columnsCommandText = "(";

     for (int i = 0; i < reader.GetSchemaTable().Rows.Count; i++) // reader contains data from SQL Server 
     {
         var column = reader.GetName(i); // name of attribute
         SqlDbType type = (SqlDbType)(int)reader.GetSchemaTable().Rows[i]["ProviderType"]; // data type
         var accessType = SQLAccessMapper.MapDataTypes(type);
         columnsCommandText +=  " " + column + " " + accessType + ",";
     }

     columnsCommandText = columnsCommandText.Remove(columnsCommandText.Length - 1);
     columnsCommandText += ")";

     oleCommand.CommandText = "CREATE TABLE " + tablename + columnsCommandText;

     oleCommand.ExecuteNonQuery(); // Exception

Mapper:

static class SQLAccessMapper
{
    public static OleDbType MapDataTypes(SqlDbType sqlDataType)
    {
        switch (sqlDataType)
        {
            case SqlDbType.Int:
                return OleDbType.Integer;
            case SqlDbType.SmallInt:
                return OleDbType.SmallInt;
            case SqlDbType.TinyInt:
                return OleDbType.TinyInt;
            case SqlDbType.Decimal:
                return OleDbType.Decimal;
            case SqlDbType.Float:         
            case SqlDbType.Real:
                return OleDbType.Single;

            case SqlDbType.BigInt:
                return OleDbType.BigInt;
            case SqlDbType.Char:
                return OleDbType.Char;
            case SqlDbType.NChar:
                return OleDbType.WChar;
            case SqlDbType.NText:                
            case SqlDbType.NVarChar:
            case SqlDbType.Text:
                return OleDbType.VarWChar;

            case SqlDbType.VarChar:
                return OleDbType.VarChar;
            case SqlDbType.Time:
                return OleDbType.DBTime;

            case SqlDbType.Date:
                return OleDbType.DBDate;

            case SqlDbType.DateTime:
            case SqlDbType.DateTime2:                  
            case SqlDbType.DateTimeOffset:                   
            case SqlDbType.SmallDateTime:
            case SqlDbType.Timestamp:
                return OleDbType.DBTimeStamp;

            case SqlDbType.Binary:
                return OleDbType.Binary;
            case SqlDbType.VarBinary:
                return OleDbType.VarBinary;

            case SqlDbType.Money:   
            case SqlDbType.SmallMoney:
                return OleDbType.Currency;

            case SqlDbType.Bit:
                return OleDbType.Boolean;
            default: return OleDbType.Error;
        }

    }

}

Example Create Table statement:

CREATE TABLE GrTable(
GrtId Integer, 
CaseId Integer, 
GrDrg VarChar, 
GrDrgText VarWChar, 
Mdc VarChar, 
MdcText VarWChar, 
GrPartition VarChar, 
Baserate Decimal, 
LosUsed Integer, 
Htp Integer, 
PricePerDay Decimal, 
Ltp Integer, 
LtpPricePerDay Decimal, 
AverLos Decimal, 
AverlosPricePerDay Decimal, 
Eff Decimal,
Std Decimal,
Adj Decimal, 
Gst VarChar, 
Pccl Integer,
PriceEff Decimal,
PriceStd Decimal, 
PriceAdj Decimal, 
DaysExcHtp Integer,
DaysBelowLtp Integer, 
DaysBelowAverLos Integer, 
TotalPrice Decimal,
BaseratePeriod VarChar)

Answer 1

Your main problem is in the MapDataTypes. You should return the string expected by the MS-Access engine and not rely on the automatic conversion of an the general enum SqlDbType to a string as you do now.

For example. For a column of type VarWChar you need to pass the string "TEXT" followed by the size of the field, while, for a field of type integer, you need the string "INT"

public static string MapDataTypes(SqlDbType sqlDataType)
{
    switch (sqlDataType)
    {
        case SqlDbType.Int:
            return "INT";
        case SqlDbType.VarChar:
            return "TEXT(80)";

        ... AND SO ON FOR EVERY POSSIBLE TYPE

   }
}

This, of course, introduces the size problem for the TEXT fields, but you can retrieve it from the same GetSchemaTable used to find the type in the column named ColumnSize and pass this size to the MapDataTypes method-

public static string MapDataTypes(SqlDbType sqlDataType, int size)
{
    switch (sqlDataType)
    {
        case SqlDbType.Int:
            return "INT";
        case SqlDbType.VarChar:
            return "TEXT(" + size + )";
        .....


   }
}

You can find some allowed types at this SQL Data Types