?
Solved

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

Posted on 2005-05-05
7
Medium Priority
?
1,050 Views
Last Modified: 2008-02-01
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.



0
Comment
Question by:robert
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13933993
After your "rs.AddNew" statement and before your "CurrentDB.Execute" statement, do you have a "rs.Update"?
0
 

Author Comment

by:robert
ID: 13934075
yes I do, but it doesnt seem to have any effect ! :( also before the rs.addnew I have a cnn.beginTrans statement.
0
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 2000 total points
ID: 13934120
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:robert
ID: 13934163
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13934305
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
 

Author Comment

by:robert
ID: 13934324
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 13934335
Glad I could help!
God bless!

Sam
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question