Information on optimizing ADO transactions?

[LPurvis]

The example mentioned is an implementation of binding forms to recordsets in order to wrap the processes in a transaction.
While originally putting it together some time ago - there were one or two pitfalls which caused the process to be less than robust (you have to assign the recordset at just the right moment etc).
And it has the potential to become self design locking (i.e. as if another user were also editing the design) if you edit the code (particularly in the case of using ADO - due to the provider used) after use. (But then - so does much ADO work wherever you use the default Access provider connection).

And the example itself is left deliberately unpolished. For example the transactions are started and ended by manual button clicks - to leave each action obvious.
And the Rollbacks also don't necessarily render the effects well onscreen - some tidying for consistency is required.

Hence my stating next to the download link that it's not "comprehensive or clean" .
But hopefully makes some begin to think about it.
For those willing to scroll down past the pretend obfuscation there's a discussion on the methodology at this Experts Exchange thread (as indicated there I'd long since made my example by then).

 

[Banana]

A quick look, and I can see that it had same problems as my earlier experiments did, especially not always refreshing the recordset correctly after rollback or locked record (bizarrely enough, this locking only happened on *some* records, even thought those weren't edited at all. I never figured that one out and long ago gave up on that)

Here is what I use for my transaction. I'm assuming this will require MySQL or anything else that allows you to disable transaction as an option in connection, but at least you can look at the implementation. The linked tables are still there, but I also imported the tables used in their sample World database. If you want to try this with MySQL, just download a MySQL server, MyODBC, and the world database (You will have to scroll down a bit to get the link and the guide instructions as well. Also, you will need to ALTER TABLE xxx ENGINE=InnoDB; each table to support transaction.

Click to download the sample mdb (The file was too big to be attached, even with zipping; the actual link to download it is tiny, on bottom right corner under the ads and says "Save file to your PC")