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
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