Solution:
DataTable schemaTable;
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data source=" + "Test1.mdb";
conn.Open();
cmd.Connection = conn;
string templetDataTable = Test2.mdb";
string clientDataTable = "c:\Test1.mdb";
string templetBackupDataTable = "c:\TestBackup.mdb";
if (File.Exists(templetDataTable))
{ //**********MDB Data Migration**************//
schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
new Object[] { null, null, null, "TABLE" });
//1. Copy Existing Data from Clientfolder to the New Templet
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
query = "INSERT INTO " + schemaTable.Rows[i].ItemArray[2].ToString() + " IN '" + templetDataTable + "' SELECT * FROM " + schemaTable.Rows[i].ItemArray[2].ToString() + "";
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
try
{
cmdresults = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
txtErrorDetails.Visible = true;
txtErrorDetails.Text = ex.ToString();
errorLogger = new StreamWriter(@"ErrorLog" + DateTime.Now.ToString("ddMMyyyyMMhhss") + ".Log");
errorLogger.WriteLine(txtErrorDetails.Text + " \n Error While Updating Table.."
+ schemaTable.Rows[i].ItemArray[2].ToString() + "{" + ex.ToString() + "}");
errorLogger.Flush();
continue;
}
}
conn.Close();
try
{
//2. Move Client MDB to seperate folder
File.Move(clientDataTable, templetBackupDataTable);
//3. Move Updated templet MDB to Client application data folder
File.Move(templetDataTable, clientDataTable);
//4. Delete old Client MDB
// File.Delete(templetBackupDataTable);
}