Transfer tables from one MS Access database to another using C#

Question

I have two almost same databases (both are *.mdb), but one of them has few new tables. Now I can only detect tables, that should be imported, using code below:

    public static List<string> GetDBTables(string path)
    {
        List<string> allTables = new List<string>();
        String connect = ("Provider=Microsoft.JET.OLEDB.4.0;data source=" 
                                     + path + ";Persist Security Info=False;");

        OleDbConnection con = new OleDbConnection(connect);
        con.Open();

        DataTable tables = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, 
                                     new object[] { null, null, null, "TABLE" });

        int counter = 1;
        foreach (DataRow row in tables.Rows)
        {
            allTables.Add(row[2].ToString());
            counter++;
        }
        con.Close();

        return allTables;
    }

    var withNewTables = GetDBTables(".\\one.mdb");
    var withoutNewTables = GetDBTables(".\\another.mdb");
    var NotFoundTables = withNewTables.Except(withoutNewTables).ToList();

How can I import these tables in the old database using C #?

HansUp · Accepted Answer · 2013-12-25 17:17:23Z

6

Access SQL offers two features which are useful here.

SELECT <field list> INTO NewTable
FROM table_name IN 'path to other db file'

So I can execute this statement from an OleDb connection to my destination db file, and it will create tblFoo_copy from the data contained in tblFoo in the other db file, NewData.mdb.

SELECT f.* INTO tblFoo_copy
FROM tblFoo AS f IN 'C:\Users\hans\Documents\NewData.mdb';

Build and execute similar statements for each of those tables you want to import.

answered Dec 25, 2013 at 17:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrey Baulin · Accepted Answer · 2013-12-26 00:02:35Z

6

Well, in addition to HansUp answer, I post the implementation of this on C#:

public static void insertTables(string path_from, string path_to, 
                                                            List<string> _tables)
{
    string conString = ("Provider=Microsoft.JET.OLEDB.4.0;data source=" 
                                    + path_to + ";Persist Security Info=False;");
    OleDbConnection dbconn = new OleDbConnection(conString);
    dbconn.Open();
    OleDbCommand dbcommand = new OleDbCommand();

    _tables.ForEach(delegate(String name)
    {
        string selQuery = "SELECT f.* INTO " + name + " FROM " + name 
                                               + " AS f IN '" + path_from + "';";

        dbcommand.CommandText = selQuery;
        dbcommand.CommandType = CommandType.Text;
        dbcommand.Connection = dbconn;
        int result = dbcommand.ExecuteNonQuery();
    });

    dbconn.Close();
}

insertTables(".\\one.mdb", ".\\another.mdb", NotFoundTables);

answered Dec 26, 2013 at 0:02

Andrey Baulin

6792 gold badges12 silver badges22 bronze badges

1 Comment

softburger Over a year ago

Can I use this way if 'another.mdb' has a password? An if it has, how?

Collectives™ on Stack Overflow

Transfer tables from one MS Access database to another using C#

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related