469,295 Members | 1,968 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,295 developers. It's quick & easy.

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

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.

3 2337
orangeCat
83 64KB
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
234 Expert 100+
@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
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.

Similar topics

11 posts views Thread by Abhishek | last post: by
2 posts views Thread by Rani | last post: by
15 posts views Thread by http://www.visual-basic-data-mining.net/forum | last post: by
5 posts views Thread by meetalps | last post: by
OuTCasT
reply views Thread by OuTCasT | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.