Copy data from MS Access mdb file to SQL Express mdf file

See more:

VS2008

C#

SQL-Server

, +

Hi,
i had coded a utility to copy tables with data from one source mdb to a target mdb.
Now my project needs to copy data from a mdb file to a SQL Express database into a mdf file. My previous code was as follows:

DataTable schemaTable;
           OleDbConnection conn = new OleDbConnection();
           OleDbCommand cmd = new OleDbCommand();
           conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;" +
                  @"Data source=" + Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "Source.mdb";
           conn.Open();
           cmd.Connection = conn;

           string templetDataTable = AppDomain.CurrentDomain.BaseDirectory + @"\\Target.mdb";
           string clientDataTable = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData) + "Source.mdb";
           string templetBackupDataTable = AppDomain.CurrentDomain.BaseDirectory + @"\\SourceBackup.mdb";
           if (File.Exists(templetDataTable))
           {   //**********MDB Data Migration**************//
               schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                            new Object[] { null, null, null, "TABLE" });

               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();

But this code is not going to work as sql database need to establish connection.
Can anyone please suggest as how can i modify the existing code to do the same?


Regards
honeyashu