Begin/Commit Transactions

[JimS]

I'm a little conflicted. I just got my head around using bound forms to "do
things the way the tool developer intended...." Now I want to do a two-table
update (linked tables FrontEnd/BackEnd.) The form is bound to the transaction
table, and that's worked well for me. I use docmd.gotorecord,,acnewrec, fill
the fields, then requery to force a post to the transaction table.
Simultaneously (therin lies the rub), I update the inventory balance to
reflect the transaction qty. I do this with a docmd.runsql update statement.

If one of these fails for some reason, I need the entire sequence to
rollback.

I've read the (sketchy) help files on Begin/Rollback transaction, but

1. Seems to work only on local tables...
2. Can't really seem to see how it would be in control of the two tables
being updated in two different ways.

I think if I go with all ADO, I can figure it out, but doesn't that fly in
the face of the bound table methodology which works best with the docmd.
stuff and already has recordsets in place, etc?

Can someone point me to examples, or white papers that would clear up my
mental fog? Or at least make it as clear as my elderly mind can grasp...

Thanks in advance!

 

[Albert D. Kallal]

I've read the (sketchy) help files on Begin/Rollback transaction, but

1. Seems to work only on local tables...

Incorrect. Transactions work just fine and dandy on linked tables (at least
to a mdb back end).

However, transactions only apply to your processing code. So, if you are
running your payroll update routing, and halfway through, a problem occurs,
or even at the end if the run if the balance is incorrect, you can roll back
the whole mess.

However, if a user opens up a form..edits all day long from 8 am to 5 pm,
and then closes the form, there is NOT a way to roll that back.

So, transactions and rollback only applies to processing code your write,
not the user interface.

If you want to dump the changes to the current record, the user simply goes
edit->undo.

You can consider creating recordset in code, and then binding that
reocrdset to the form. However, for sub-forms, this will not work. so, you
wind up doing a lot of coding...

So, in a simple sentience:

transactions, and roll back applies to code (reocrdset code) that you
write..not the graphical interface. So, if you user opens up a form, or
table...and edits it, that general editing is not able to be wrapped in a
transaction. It would be great feature if you could wrap all user editing
for the day in a transaction.

So, if you want the user to bail, have them use edit->undo (or in code to
dump the current record editing, you can go:

me.Undo

 

[JimS]

OK, I think you've validated what I thought. In order to do this (I'm only
worried about one transaction pair at a time, not many..), the "Save" button
on my form should do an ADO connect, populate the two recordsets as
appropriate, start the transaction, update both recordsets, then commit the
transactions, then close the connection, etc. On error, I'd rollback the
transaction.
The recordset is populated from unbound controls in the header of the
continuous form. The only issue I've had with this strategy is the refresh of
the form's own transaction table recordset, which sometimes lags, confusing
the user.