Transactions & Calling outside procedures that affect data (1 Viewer)

[Insane_ai]

I need clarification on how transactions work. I have read: How to use Transactions http://msdn.microsoft.com/en-us/library/bb243806(v=office.12).aspx. I understand the steps involved but I would like clarification if possible.

I have a routine that updates a master price book entry that changes the old record then creates a new records in succession. After this is completed, I call another routine to propagate those changes to a live price book with manipulation for discounts / pricing tiers etc.

Each function operates without the transaction. I would like to use a transaction to ensure this succeeds or fails as a whole.

If I call a routine from within another routine will the original transaction capture the events in the second routine? If not, I am prepared to incorporate the second routine into the first to guarantee functionality.

This is my first attempt at using transactions. All Tables affected are native to the database.

Example of what I would like to do:
Transaction1
Do stuff
Call propagation routine that affects data related to "do stuff"
Commit Transaction

Thanks in advance.

 

[DCrake]

Effectively what is happening is that Access is committing to memory at least one amendment to a recordet in your database as per batch procesing. Then at the end of the exercise you can tell acces that you are happy to make those changes, thus commit transaction.

As you are using recordets and not physical tables it can do alot in memory as opposed to doing physical read/writes then when you confirm your intention it performs 1 read/write commitment to the table. Similar what happens when you run an update query Access says.. "you are about to update 200 record(s) are you sure you want to continue...?"

Yes commits the read/write
No rollsback the read/write

 

[Insane_ai]

Thank you for your Response DCrake.

Through through trial and error, I found that I can execute the code as desired with the outside procedure included within one transaction. I had to make a change in the execution methods I was using.

Original code was DoCmd.RunSQL
With the transactions, I used db.Execute strSQL, dbFailonError

I think I will be spending some time in msdn. If anyone knows of another good reference, please provide a link.