How do I implement transactions in Access?

Question

Hello

I am stuck implementing a transaction/rollaback feature on an access form.

Here's the stripped down outline of what I've done:

In the form's Load handler, I start the transaction

dbEngine.BeginTrans
gInTransaction = true

Then, there's a "Cancel" button, whose Click handler goes like

dbEngine.Rollback
gInTransaction = false
doCmd.close acForm, "frmName"

Finally, the form's Unload handler has:

if gInTransaction then
    dbEngine.CommitTrans
    gInTransaction = false ' just in case
end if

Now, there seem to be no effect on the form regarding the rollback. Pressing the "Cancel" button does not seem to rollback anything.

I also tried replacing dbEngine with dbEngine.workspaces(0), with no effect either.

So, the question is: how do I implement transaction in Access?

Thanks for any pointer into the right direction, Rene

Answer 1

I agree with Shahkalpesh the updates to the form will not be part of the transaction. You could get your form to do these by instead of using a bound form use an unbound form so you have control over when the IO is done.

You could do this a few ways but my preferred method is to load the details into the form onload and then have a save button that fire a sub that saves those details back to the DB. I Also normally setup a public variable call bDirty and change it to true when ever a control is undated that way you can warn the user if they try closing the form before saving changes

Answer 2

I do not think you can implement transactions on a form, where updates are made on a recordset (with beforeUpdate and afterUpdate events), while transaction is linked to the execution of an INSERT, UPDATE or DELETE command sent to the database.

EDIT: if your idea is to be able to manage all changes made to a continous form at once, you have 2 different solutions:

1. The first one is to attach a disconnected ADODB recordset to your form, and call the 'batchUpdate' method once all your changes have been made. Though I haven't check the doc, I guess you'll be able to catch exceptions that can happen at this stage through the connection object.
2. The second one, which is the one we implement in our applications, is to have the client work on a copy of the original data. On the client side, we keep track of all inserts, deletes and updates made on the form. Once the user validates its changes, the client generates 'on the fly' a bunch of SQL instructions corresponding to the changes made, and send them to the database. It is then very easy to send these instructions in a transaction (either one transaction per line or one for all changes). It took us a while to fine-tune this solution, but it was worth the job. Form validation is now a unique function used on every form of our apps. The function even allow 'one form to multiple tables' validation.

Answer 3

I posted a code example for how to use transactions in Access a little over a week ago, but it was not designed for working with data edited in a bound form. Basically, with bound forms, you don't have the same control you do via other interfaces. This is both a feature and a drawback, depending on what you're trying to do.

Answer 4

Researching these transactions, I found this link promising:

http://support.microsoft.com/kb/248011

Yet, there seem to be some other issues with it.