How to copy multiple tables from an access database to another?

[aldrean]

Assuming that I had 10 tables in my first access database and for some reasons I only want to to copy those 5 tables to my second database, how can i possibly accomplish this task.Thank you in advance.

[jmcilhinney]

Are you talking about data only or schema too?

[aldrean]

both data and schema jm...

[jmcilhinney]

Well, the schema is no mean feat. You're going to have to read the schema of each of your existing tables and build a CREATE TABLE statement from that. It's not a trivial thing but you first need to make sure you understand the makeup of a CREATE TABLE statement so you know what you need to look for in the existing tables.

[aldrean]

I came to an idea that since the two databases are identical, I deleted my destination table first then INSERT it with that from the other database.Here's my code.

Dim myTrans As New OleDbCommand("DELETE * FROM [TRANSACTIONS]", myConnection)
            Dim myTransDetails As New OleDbCommand("DELETE * FROM [TRANSACTIONDETAIL]", myConnection)
            
            myConnection.Open()

            myTrans.ExecuteNonQuery()
            myTransDetails.ExecuteNonQuery()
           
            myTrans.CommandText = "INSERT INTO [TRANSACTIONS] SELECT * FROM [MS ACCESS; DATABASE=\\leah\SharedDocs\myDB.mdb;].[TRANSACTIONS]"
            myTransDetails.CommandText = "INSERT INTO [TRANSACTIONDETAIL] SELECT * FROM [MS ACCESS; DATABASE=\\leah\SharedDocs\myDB.mdb;].[TRANSACTIONDETAIL]"
           
            myTrans.ExecuteNonQuery()
            myTransDetails.ExecuteNonQuery()
           
            myConnection.Close()

The whole thing are doing fine, except that I had notice that the size of my destination database(where myConnection points) gets bigger compared to my source database(myDB.mdb) eventhough the have very the same data after the code execution.
I tried deleting and inserting 7 tables with an average 1000 records each and the performance is quite ok for me(about 3 seconds to complete) but I noticed that my new destination database ballooned to 85MB while my source database was just 13MB and inspecting both DB's would show that they had very identical data.Where are the excess MB's came from?Had I miss something here?thanks.

[jmcilhinney]

Deleting records from an Access database doesn't actually remove any data from the file. You'd have to compact the database file using Access.

Also, if you want to delete all records in a table without the ability to rollback you can use a TRUNCATE statement, which is more efficient than DELETE *. Having said that, I'm not 100% sure that Access supports TRUNCATE.

Finally, you posted earlier that you wanted to transfer the schema too, now it seems that your target database already contains the schema. I'm glad I didn't go into detail describing how to do it. Please try to be precise with your questions.