commit/rollback facilities?

[hstijnen]

Hi,
Does MS-Access 2000 has any commit and especially Rollback facilities? E.g.
I have a form, apply some updates, and then when closing the form, I want to
commit or Undo/Rollback the changes. Is that possible?

Thanks for help

Henk

 

[Allen Browne]

When you are working with the data programmatically, you can commit or
rollback an entire transaction. See help on BeginTrans.

Bound forms use their own transactions (e.g. for rolling back a deletion),
but these are not exposed.

In Access 2000 and later, it is actually possible to open a transaction, and
then open a recordset inside that transaction, and then assign it to the
Recordset of the form so that the bound form's records are operating inside
your transaction. Then you can commit or rollback before closing the form.

In practice, however there are some problems and limitations with that
approach. It becomes very clumsy in a multi-user environment when multiple
users are holding transactions open for extended periods of time and can
rollback over the top of each other. Additionally, it is not possible to do
this with subforms that have a LinkMasterFields/LinkChildFields since Access
reloads the subform whenever the main form changes record, and so the
assignment of the recordset does not survive. On top of that, my experiments
suggested that this was not as stable as Access normally is.

So, in practice, my answer would be, No. You cannot do that short of copying
the records into a temp table, editing them there, and then sorting out any
multi-user conflicts yourself.

 

[Albert D.Kallal]

You can undo changes to a record made in a form by simply going

me.undo

As mentioned, there is also the begin trans, and commit, but they do not
apply to forms, but only code that you write.

 

[hstijnen]

Thanks, Albert,

Am I right that this implies that on the moment I leave a record in a form
(by mouseclick or cursor), the changes in that record are committed? Is there
a function to test for changes?

 

[Allen Browne]

Correct. The changes are committed as soon as you move to another record.
They are also committed if you requery the form, change its RecordSource,
apply filtering, change sorting, close the form, close Access, press
Shift+Enter, choose Save Record on the Records menu, move into a different
subform, and so on.

Use the BeforeUpdate event of the form to perform any tests or validation
needed before the record is saved.

 

[Albert D.Kallal]

Thanks, Albert,

Am I right that this implies that on the moment I leave a record in a form
(by mouseclick or cursor), the changes in that record are committed? Is
there
a function to test for changes?

Yes, you can check the Dirty property.

if me.Dirty = True then
' changes have been made...
else
' changes have not been made
end if

You can also use the dirty proeryt to force a disk write...

if me.Dirty = True then
me.Dirty = false ' force data to disk
end if

So, to prompt the user to write data to disk, you could put the following
code in the before update event..


If Me.Dirty = True Then

If MsgBox("save data?", vbQuestion + vbYesNo, "Save") <> vbYes Then
Me.Undo
End If
End If

The above means if you navigate to another record, and don't change
anything, then the user would not be prompted to save. If you edit
something, and then try to move to anther record, the before update event
fires. We simply execute a me.undo to discard the changes...

Note that the before update event also has a cancel event, but that would
also stop the record navigation from occurring, and keep the user on the
current record if we set cancel = true.

By the way, since the before update event DOES NOT fire unless the record is
edited, then we actually don't need the dirty test...

The following code would suffice....

If MsgBox("save data?", vbQuestion + vbYesNo, "Save") <> vbYes Then
Me.Undo
End If