Click here to Skip to main content
12,074,308 members (64,522 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Posted 4-Jun-12 21:24pm
Comments
digimanus 5-Jun-12 9:06am
   
why not via SSMS Sql Server management Studio ?
digimanus 5-Jun-12 9:08am
   
why not create a connection to sql server (sqlConnection class) that calls a stored Procedure that reads and insert the MDB Acces file via OpenRowSet command in SQL?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160208.1 | Last Updated 5 Jun 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100