469,150 Members | 1,969 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

What's the better method to use when copying data from one table to another?

Hi all! I'm looking for a 'best practices' solution - or at least a more elegant one to my problem.

My application tracks changes made to existing records. So I need to:
1. Begin a transaction
2. Copy the original record to the tracking table.
3. Fetch the autosequence number (key) of the newly inserted record.
4. Use the key of the tracking record to update a related table with linked records (e.g. an order/order details relationship)
5. Update original records with modified data
5. Commit transaction

My issue is the fetching of the newly-inserted record key. I was using a db.Execute("INSERT INTO table (...) SELECT ...") statement to perform the copy; however, this does not allow me to use a recordset to fetch the newly-created key:
Expand|Select|Wrap|Line Numbers
  1. Set rs = db.OpenRecordset("SELECT myID FROM histTable;")
  2. rs.Move 0, rs.LastModified
  3. lLastID = rs![myID]
  4. rs.Close
will not work because the .LastModified will only recognize changes on the current recordset which is apparently outside the scope of the db.Execute call (and so this method always returns the *last* id created).

The other option is to open two recordsets - one the original table, one the historical table set the fields one by one:
Expand|Select|Wrap|Line Numbers
  1. Set rsOrig = db.OpenRecordset("SELECT * FROM origTable WHERE...;")
  2. Set rsHist = db.OpenRecordset("SELECT * FROM histTable WHERE...;")
  3. With rsHist
  4.      .AddNew 
  5.      !field1 = rsOrig.field1
  6.      !field2 = rsOrig.field2
  7.      '...etc
  8.      .Update
At which point I can use LastModified to fetch the new myID value.

I usually prefer to use SQL statements in cases like these - less code, I find it easier to read, etc. Is there a way to do what I need to do without a recordset-to-recordset copy?
Jan 3 '11 #1
2 1164
1,258 Expert 1GB
'best practices' forbid you from using autosequence number as your key. That's a non-starter for what you say you are looking to do.

Are you willing to redesign the database to change that?

However, in cases where I wanted to retrieve an autosequence number in a record I just created, I have successfully done it like this:
Expand|Select|Wrap|Line Numbers
  1. rs.addnew
  2. rs.stuff = stuff ' whatever stuff you need
  3. rs.update
  4. rs.bookmark = rs.lastmodified
  5. NewID = rs!ID
Jan 3 '11 #2
I haven't heard of issues with using an autosequence number as a primary key (as long as it isn't expected to be related to the data itself).

My question was directed at the difference between using the Database.Execute method vs. Recordset.AddNew. AddNew allows the code to retrieve fields (namely autogenerated ones) from the new record immediately but when working with tables with upwards of 60 columns it's not as easily coded as a SQL query that can be quickly created with the Access query window - but then there doesn't seem to be a solid method for retrieving any autogenerated data from the new record.
Jan 5 '11 #3

Post your reply

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

Similar topics

5 posts views Thread by Thomas Lotze | last post: by
2 posts views Thread by Bruce Mogayzel via AccessMonster.com | last post: by
3 posts views Thread by Ctal | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.