Inserting records using rs.addnew, currentDB.execute(SQL) and transaction processing

Hi there,

I am developing a program to process data from an IMPORT table. My data needs to be updated on many tables and some of the tables have one-to-many relationships.

I am using transaction processing to ensure that the data only gets committed to the DB if all the updates have been successful. The processing works fine while I am in Update mode. Problem is when i am in Addnew mode I get problems. I am using a mix of rs.addnew for my parent table [T1] and then currentDB.execute (sSQL) where sSQL is an insert statement for my child table [T2].

I have table [T1].addnew then in a separate routine I construct a sSQL INSERT statement to add multiple records to child table [T2]. The problem seems to be that when you use currentDB.execute(sSQL) it tries to commit the records straight to the DB and the problem seems to be that it doesnt "see" my new primary key from [T1]. I think it would work if I opened up [T2] as a recordset and added the records using Addnew on that table too but I don't want to it that way as i have created a generic function to do the insert for many child tables I dont want to have to hardcode a routine now for each table. Is there a way around my problem ? Any help much appreciated, its urgent, hence 500 points.

Regards
Robert.



robertsoftware developerAsked:
Who is Participating?
 
will_scarlet7Connect With a Mentor Commented:
Why are you using "cnn.BeginTrans"?

I do not have experience using it, but assuming that you are using it to enable the rollback of the transaction should any part of it fail, I would think that you are shooting yourself in the foot by combining the "rs.AddNew" and "CurrentDB.Execute" since the transaction being monitored only applies to the connection object "cnn" and terefore will not monitor the append query. It also may be responsible for the PKID not yet being read by the append query since the transaction has not been completed.
0
 
will_scarlet7Commented:
After your "rs.AddNew" statement and before your "CurrentDB.Execute" statement, do you have a "rs.Update"?
0
 
robertsoftware developerAuthor Commented:
yes I do, but it doesnt seem to have any effect ! :( also before the rs.addnew I have a cnn.beginTrans statement.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
robertsoftware developerAuthor Commented:
umm yeah I think you're right but is there another way of implementing my requirement ? I was thinking of maybe creating a second rs2.addnew statement using parameterised rs.fields(strField1).value statements. Do you think that would work ?

0
 
will_scarlet7Commented:
The method you are proposing sounds like it should work. Like I said above, using "BeginTrans" is a bit outside of my scope of experience, but if you would post your code I might be able to help you work through it.
0
 
robertsoftware developerAuthor Commented:
thank you mr will_scarlet7, I think I can manage now. The BeginTrans and CommitTrans is not great shakes actually. Like you said it allows you to rollback the processing if there has been a problem during the updates. You can rs.update several recordsets and if any fail you can cnn.rollbacktrans and it will cancel all the updates. But like you said I was shooting myself in the foot by mixing rs.addnew with currentdb.execute. Im coverting my routines now to use this second method.

Regards
Robert.
0
 
will_scarlet7Commented:
Glad I could help!
God bless!

Sam
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.