paulette33
Technical User
Hi- I have posted this under Microsoft Access Modules(VBA Coding) forum, but I'm not sure if that was the correct forum as I am asking more of a best practice/theory type question.
I have a form that the user will enter data. Upon clicking the save button, the data entered onto the form needs to update 2 tables. The relationship between the 2 tables is a 1 to many. Therefore, the data needs to be stored into the main table first. Then using that primary key, the second table is updated with 1 to many records. I have code that goes through to add data. Currently that is setup with transaction locking within the "addData" function. Here's my question, if somewhere in saving data to the second table an error occurs, that error will rollback, however I really want the entire transaction to rollback, i.e. record saved in main table.
What is the best way to approach this. I can think of 2 ways: 1) Delete the records from both tables if there is an error 2)set up the transaction locking to wrap around the entire transaction.
With option #2, if there referential integrity between the 2 tables and the transaction has not been commited, then technically the primary record would not be written to the table right? So if that is the case, would this option work?
With option #1, the primary table is set to auto number. So, if I delete the records, then that primary key would no longer be available and the record once corrected would be saved to the next number, right? So, if this is the better option, should I change the field from an auto number to just a numeric?
Any other thoughts on how to approach this?
Sorry, for the long posting...and thanks in advance!
I have a form that the user will enter data. Upon clicking the save button, the data entered onto the form needs to update 2 tables. The relationship between the 2 tables is a 1 to many. Therefore, the data needs to be stored into the main table first. Then using that primary key, the second table is updated with 1 to many records. I have code that goes through to add data. Currently that is setup with transaction locking within the "addData" function. Here's my question, if somewhere in saving data to the second table an error occurs, that error will rollback, however I really want the entire transaction to rollback, i.e. record saved in main table.
What is the best way to approach this. I can think of 2 ways: 1) Delete the records from both tables if there is an error 2)set up the transaction locking to wrap around the entire transaction.
With option #2, if there referential integrity between the 2 tables and the transaction has not been commited, then technically the primary record would not be written to the table right? So if that is the case, would this option work?
With option #1, the primary table is set to auto number. So, if I delete the records, then that primary key would no longer be available and the record once corrected would be saved to the next number, right? So, if this is the better option, should I change the field from an auto number to just a numeric?
Any other thoughts on how to approach this?
Sorry, for the long posting...and thanks in advance!
