Copy one access database into another database with C#

Is it possible to copy 开发者_如何学Pythonprogrammatically all the tables in one database into another database that might already contain tables (and if there is any repeated name throw an exception of course)? This implies creating the tables in the destination database with the proper structures and with the same name as on the source database.

I found a few similar questions but none of them have this particular need.

I'm using the Jet engine.

Found a solution:

string query = "SELECT * INTO [dest_table] FROM [source_table] IN '" + sourceDataBaseFileName + "'";
using (OleDbCommand sqlCeCommand = new OleDbCommand(query, DbConnection))
{
    sqlCeCommand.ExecuteNonQuery();
}

Do this for every table in the source. source_table and dest_table can be the same name. The DbConnection is the destination database.

I realize this is an old question. Perhaps this answer will help someone else.

Manipulating the COM interface of Microsoft Access seems to provide most of what you are looking for.

• Add a reference to the "Microsoft Access xx.x Object Library"
• Create and instantiate a object of type "Microsoft.Office.Interop.Access.Application"
• Call OpenCurrentDatabase with a parm for your source database
• Call DoCmd.CopyObject with the parms for your target database, the name of the target table, object type (AcObjectType.acTable), and the name of your source table
• Call CloseCurrentDatabase

In VB.Net, it's something like this:

acdb = New Microsoft.Office.Interop.Access.Application
acdb.OpenCurrentDatabase("source db")
acdb.DoCmd.CopyObject("target db", "target table name", Microsoft.Office.Interop.Access.AcObjectType.acTable, "source table name")
acdb.CloseCurrentDatabase()

I verified the tables are copied correctly, with proper column types and indexes and keys. However, it seems to overwrite the target table if it already exists without any warnings or exceptions.

See this MSDN link for details.

When I researching how to do this, I misunderstood what I read, as I thought this was DAO. It's been a very long time since I've used DAO, so I didn't realize the errors in my ways. Thank you Gord Thompson for setting me straight.

This does require MSACCESS being installed on the machine this is running on. The more complicated approach would be to use ADOX, which also has its own drawbacks IMHO.

This is with Office 2010. Obviously YMMV with other versions.

If only some day Microsoft started using .Net for Office as they tell the rest of the world to do.