Copy data from all tables in Access 2003

Introduction

When you are involved in migrating databases, we end up at some point in time finding an appropriate deployment strategy and identifying a cut over mechanism. The case which I am referring here involves migration of all Access 2003 databases to Access 2010. Once the database is converted to Access 2010, there is a wait period only after which users will be actually moving to the Office 2010 environment. During this cut over time, all the database updates would happen only on the Access 2003 database, and we need a mechanism to migrate all the data from Access 2003 to 2010.

Background

You may think that database conversion / migration can happen after users have moved to Office 2010. But the catch is, in a live environment, we have to make sure things work perfectly before they are actually migrated. So you have a test environment where you test the compatibility of the target environment and only then migrate your users to that environment. When the number of databases become huge (in my case it exceeded 5K) this process becomes tedious. Suggesting to adopt SQL Sever or other large capable databases could only remain a suggestion and takes a long route in actual business environments.

Using the code

I have developed a small C# based application for this. This code part opens an Access 2003 database, loops through all the tables in the source, looks for the corresponding table in the destination database (I do certainly miss some conditional checks here at this point of time), and then copies the data over to the destination Access 2010 database:

private void MigrateData()     {
    //Connection string for the Source Access 2003 database
    string sourceConnStr = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source=" +
            txtAccess2003.Text + ";";

    //Connection string for the destination Access 2010 database
    string destinationConnStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data source=" +
        txtAccess2010.Text + ";Persist Security Info=False;"
    //Establish connection to source database
    System.Data.OleDb.OleDbConnection sourceConn = 
      new System.Data.OleDb.OleDbConnection(sourceConnStr);
    sourceConn.Open();

    //Establish connection to destination database
    System.Data.OleDb.OleDbConnection destinationConn = 
      new System.Data.OleDb.OleDbConnection(destinationConnStr);
    destinationConn.Open();
    //Get the list of all tables present in soure database
    DataTable sourceSchemaTable = null;
    sourceSchemaTable = 
        sourceConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
        new object[] { null, null, null, "TABLE" });

    int tableCounter = 0;

    //Loop through all the tables present in source databse to fetch the records
    for (tableCounter = 0; tableCounter < sourceSchemaTable.Rows.Count; tableCounter++)
    {
        string sourceTableName = string.Empty;

        //Fetch the source table name
        sourceTableName = 
          (sourceSchemaTable.Rows[tableCounter]["TABLE_NAME"].ToString());

        string sourceDataPull = "Select * from " + sourceTableName;

        string destinationDataPurge = "Delete from " + sourceTableName;

        //Read all the data from source Access 2003 database
        System.Data.OleDb.OleDbCommand cmdSourceDataPull = 
          new System.Data.OleDb.OleDbCommand(sourceDataPull, sourceConn);
        System.Data.OleDb.OleDbDataReader sourceReader = cmdSourceDataPull.ExecuteReader();

        //Clean up the existing records from Access 2010 database.
        //You may have to put some extra logic / validations here.
        System.Data.OleDb.OleDbCommand cmdDestinationDataPurge = 
          new System.Data.OleDb.OleDbCommand(destinationDataPurge, destinationConn);
        cmdDestinationDataPurge.ExecuteNonQuery();
        //Insert all the data from source to destination database
        while (sourceReader.Read())
        {
            string destinationDataFeed = "Insert into " + 
                     sourceTableName + " values (";

            for (int columnSeed = 0; columnSeed < sourceReader.FieldCount; columnSeed++)
            {
                destinationDataFeed += "'" + 
                  Convert.ToString(sourceReader[columnSeed]) + "'";

                if (columnSeed < sourceReader.FieldCount - 1)
                    destinationDataFeed += ",";
            }

            destinationDataFeed += ")";

            System.Data.OleDb.OleDbCommand cmdDestinationDataFeed = 
              new System.Data.OleDb.OleDbCommand(destinationDataFeed, destinationConn);
            cmdDestinationDataFeed.ExecuteNonQuery();
        }
    }
    //Close the connections
    destinationConn.Close();
    sourceConn.Close();

    MessageBox.Show("ETL Process Completed. Please verify the data.");
}