MS Access to MS Access data transfer without overwriting

April 29, 2023

Quick question, assuming that I have two access databases with tables that have exactly the same headers, how would I go about appending data from DATABASE1_TABLE_A to DATABASE2_TABLE_B using VBA (executed from DATABASE1). I tried the

DoCmd.CopyObject "DATABASE2_DESTINATION", "TABLE_B", acTable, "TABLE_A"

which works, however it overwrites existing DATABASE2_TABLE_B rather than appending data to it. Is there another command which allows appending data from one access database to the other without overwriting existing data?

In theory I could complete

DoCmd.TransferDatabase acImport, "Microsoft Access", DATABASE2_DESTINATION", acTable, "TABLE_B", "RANDOM_DB_NAME", False

Then merge my table with the table transfered and then execute DoCmd.CopyObject but it seems like it’s a very inefficienc method to go about this problem.

V/R

>Solution :

If you have two tables with the same structure you could use the following code to do the job

Sub copyData()

    Dim db As database  ' add reference to DAO library
    Set db = CurrentDb

    Dim sSQL As String
    Dim dbName As String
    dbName = "Path\TargetDB.accdb"

    sSQL = "INSERT INTO tblDest IN '" & _
        dbName & _
        "'SELECT tblData.* FROM tblData;"

    db.Execute sSQL

End Sub