By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,275 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

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

P: 11
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.
Expand|Select|Wrap|Line Numbers
  1. Dim strNewTable As String
  2. Dim strOldTable As String
  3. strNewTable = "oos_table"  
  4. strOldTable = "oos_table_temp"  
  5.  
  6. DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, strOldTable, strNewTable, False
  7.  
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.
Nov 20 '09 #1

✓ answered by 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.

Share this Question
Share on Google+
3 Replies


P: 83
Suggest you look at SQL statement
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO oos_table
  2. SELECT *
  3. FROM oos_table_temp
You'll also need SQL to :
Expand|Select|Wrap|Line Numbers
  1. DELETE
  2. FROM oos_table_temp
after you have moved the data from oos_table_temp to oos_table
Nov 20 '09 #2

topher23
Expert 100+
P: 234
@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.
Nov 20 '09 #3

P: 11
Thanks orangeCat for helping and topher23 for your detail explaination. These helps a lot.
Nov 23 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.