Problem of transferring data from one table to another in the same DB

[angusfreefa]

Dear All,

I am facing a problem of transferring data between 2 tables within the same database.

I set up 2 tables. The first table is the permanent table (oos_table) for saving records. the other table is a temp table (oos_table_temp) for storing records which all data would be deleted after the data transferred to the permanent table.

below is the code I am currently using.

Dim strNewTable As String
Dim strOldTable As String
strNewTable = "oos_table"  
strOldTable = "oos_table_temp"  
 
DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTable, strNewTable, False
 

the above code works in the first time and can transfer all data in the temp table to the permanent one.

But when doing the second time, the previously data in the permanent table will be replaced by the new temp table data.

The problem is, I want to keep te previous data in the permanent table and add the data in the temp table to it. not replacing the existing data.

Could anyone help with this?

Thanks a million.

[orangeCat]

Suggest you look at SQL statement

INSERT INTO oos_table
SELECT *
FROM oos_table_temp

You'll also need SQL to :

DELETE
FROM oos_table_temp

after you have moved the data from oos_table_temp to oos_table

[topher23]

@orangeCat
orangeCat's got the solution, but that doesn't tell you why what you're doing now doesn't work.

The TransferDatabase method doesn't copy the data, it copies the table. So, if you already have a table with the new table name you specify in TransferDatabase, it will be overwritten by the other table.

Think of it like a Word document. If you create 2 Word documents, then save the second document with the first document's name, it doesn't append the second document's text to the first document, it just deletes the first document and saves the second document with the new name. That's what you're doing with the TransferDatabase method.

So, what you need to do is use an Append query. An Append query can take the data from your temp table and add it to the permanent table by appending the new data to the existing data in the table.

The first bit of SQL OrangeCat gave you is the Append Query. The second is a delete query to remove everything from the temp table after it is appended to the permanent table.

[angusfreefa]

Thanks orangeCat for helping and topher23 for your detail explaination. These helps a lot.