Convert Sql tables to Access .mdb

Question

i am converting datset table to access .mdb file but it take more than hour.how we can convert sql table to .mdb file quickly. 

 

 

public void DataSetToAccess(DataSet dataSet)

{

try

{

//Create an empty Access file that we will fill with data from the data set

ADOX.Catalog catalog = new ADOX.Catalog();

catalog.Create(accessConnectionString);

//Create an Access connection and a command that we'll use

OleDbConnection accessConnection = new OleDbConnection(accessConnectionString);

OleDbCommand command = new OleDbCommand();

command.Connection = accessConnection;

command.CommandType = CommandType.Text;

accessConnection.Open();

int i = 0;

//This loop creates the structure of the database

foreach (DataTable table in dataSet.Tables)

{

String columnsCommandText = "(";

foreach (DataColumn column in table.Columns)

{

String columnName = column.ColumnName;

String dataTypeName = column.DataType.Name;

String sqlDataTypeName = SqlDbType.Text.ToString();

columnsCommandText += "[" + columnName + "] " + sqlDataTypeName + ",";

}

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

columnsCommandText += ")";

string cellValue = "";

if (table.Rows.Count > 0)

{

cellValue = table.Rows[0][0].ToString();

}

else

{

i = i + 1;

cellValue = "E" + i;

}

command.CommandText = "CREATE TABLE " + cellValue + columnsCommandText;

command.ExecuteNonQuery();

}

int r = 0;

foreach (DataTable table in dataSet.Tables)

{

string cellValue1 = "";

foreach (DataRow row in table.Rows)

{

if (table.Rows.Count > 0)

{

cellValue1 = table.Rows[0][0].ToString();

// table.Columns.Remove("table");

}

else

{

r = r + 1;

cellValue1 = "E" + r;

}

String commandText = "INSERT INTO " + cellValue1 + " VALUES (";

foreach (var item in row.ItemArray)

{

string Result2 = string.Join(" ", item.ToString().Split(' ').Select(x => x.Trim('\'')));

commandText += "'" + Result2 + "',";

}

commandText = commandText.Remove(commandText.Length - 1);

commandText += ")";

command.CommandText = commandText;

command.ExecuteNonQuery();

}

}

accessConnection.Close();

}

catch (Exception ex)

{

throw ex;

}