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

[bixm]

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("INS ERT INTO table (...) SELECT ...") statement to perform the copy; however, this does not allow me to use a recordset to fetch the newly-created key:

Set rs = db.OpenRecordset("SELECT myID FROM histTable;")
rs.Move 0, rs.LastModified
lLastID = rs![myID]
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:

Set rsOrig = db.OpenRecordset("SELECT * FROM origTable WHERE...;")
Set rsHist = db.OpenRecordset("SELECT * FROM histTable WHERE...;")
With rsHist
     .AddNew 
     !field1 = rsOrig.field1
     !field2 = rsOrig.field2
     '...etc
     .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?

[jimatqsi]

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

rs.addnew
rs.stuff = stuff ' whatever stuff you need
rs.update
rs.bookmark = rs.lastmodified
NewID = rs!ID

Jim

[bixm]

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.Execut e method vs. Recordset.AddNe w. 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.