Click here to Skip to main content
14,733,013 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have to create a console application which can import two tables and two queries from a source to destination MS Access MDB file.

For example: I have two tables A1 & A2 in an Access file, Test1.mdb, at location, c:\Test.mdb. On running the application, these two tables (A1 & A2) should get 'IMPORTED' from c:\Test.mdb into a new location: d:\Test1.mdb.

I specifically want importing because on writing Create Table queries, all the properties defined manually for a table's column get converted into the default set of properties in the new table, thus all property settings get lost ...

I want to replicate the table structure in the destination file as exactly I had defined in the source table...

Need your Help...

Thanx and regards

Ashish
Posted
Updated 15-Feb-10 12:39pm
v2
Comments
dinu_prapandi84 28-Dec-13 6:40am
   
thanks

Please be more specific with what you need help with.

Do you know how to create a Windows application with C#?

Do you know how to use the data access classes?

What kind of help do you need?
   
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);
                }
   
v2
Comments
ArvindTomar 16-Mar-16 4:24am
   
Code work properly but i want to use not in condition to insert data one table to another it is not work .

can any one help me

my insert query is below

string query = "INSERT INTO " + schemaTable.Rows[29].ItemArray[2].ToString() + " IN '" + templetDataTable + "' SELECT * FROM " + schemaTable.Rows[4].ItemArray[2].ToString() +" WHERE PARANAME NOT IN (SELECT PARANAME FROM " + schemaTable.Rows[4].ItemArray[2].ToString() + ")" + "";

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900