Inserting records using rs.addnew, currentDB.execute(SQL) and transaction processing
Posted on 2005-05-05
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.