Copy records from one MS Access database to another?

Question

I have two MS Access databases (with identical table structures), and I'd like to use a SQL statement (programatically in VB.NET) to copy records from one to the other. Both databases are locally stored, in the same directory (and this will always be the case).

Suggestions?

Thanks!

Answer 1

Your best bet is to open two database connections and do something like this:

select * from tableA in dbA;
foreach row 
    insert into tableA row in dbB

Answer 2

You can use an Odbc connection and the ExecuteReader/ExecuteNonQuery methods to migrate data from one to the other.

Setup two connections, one to each Access database. With the first one execute a select to get the data you need, then formulate the data into an insert that you can run using ExecuteNonQuery to insert to the other.

Depending on the needs, you could also use a DataSet, but I don't typically use them.