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