473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

3
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
  9.  
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 1293
jimatqsi
1,271 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
  6.  
Jim
Jan 3 '11 #2
bixm
3
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

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

Similar topics

1
by: vishal | last post by:
hi i am using get method to pass data from one form to another and my value may contain & symbol. so when this is case the value after & sign is truncated which is logically true. so what...
5
by: Thomas Lotze | last post by:
Hi, another question: What's the most efficient way of copying data between two file-like objects? f1.write(f2.read()) doesn't seem to me as efficient as it might be, as a string containing...
3
by: Jack | last post by:
I am currently trying to work on a program and I found it more convient to have a tempory object which would hold a copy of data loaded from a file so I wouldn't have to work with the overhead of...
3
by: Agnes | last post by:
Now, i need to query some data from InvoiceTable and then put into one dataset. my way is using store procedure , 1.by passing date,company code to the store procedure) 2. use datarow to loop the...
2
by: Bruce Mogayzel via AccessMonster.com | last post by:
What is the syntax to lookup data in another database? Thanks Bruce -- Message posted via http://www.accessmonster.com
3
by: Ctal | last post by:
I have an app that populates several data tables on load. Each of these are bound to a datagrid. Above each datagrid I have several text boxes that display the data for the active row. There are...
5
by: mark_aok | last post by:
Hi all, I have a situation where I have a split database. At the back end, I need to - create a new table (I will call it newTable) with the exact fields, and relationships as another table...
1
by: Max2006 | last post by:
Hi, I am truing to find a pattern for my Business Logic Layer to be able to work fine win ObjectDataSource's Update method. The challenge is ObjectDataSource is not able to work with an...
0
by: \Ji Zhou [MSFT]\ | last post by:
Hello Ashutosh, I see your points. As to your two new concerns, I am give the detailed comments in the following. 1. Is it possible to achieve this result without calling Update on the route...
1
by: boyjook | last post by:
Design a suitable record structure for storing, as a serial file: The data required to retrieve each stored document. The means to optionally add comments.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.