Commit/Rollback - transaction processing question...

[Brad Pears]

I have some batch procedures that involve making changes to multiple tables.
I want to make sure that I am able to "rollback" the changes if something
fails during the updates.

I have never used transaction processing withinVBA/Access and the help isn;t
very good. Could someone forward me some sample code that deals with
beginning/starting a transaction, ending the transaction and commit/rollback
the trasnaction?

I am using DAO with an Access .mdb file.

Thanks,

Brad

 

[Albert D. Kallal]

There is not much to this.


Dim strSql As String
Dim rst As DAO.Recordset

BeginTrans

strSql = "update tblCustomer set City = 'New York' where City = 'NY'"
CurrentDb.Execute strSql

strSql = "update tblCustomer set City = 'LA' where City = 'Los
Angles'"
CurrentDb.Execute strSql

If MsgBox("Do all the zillions of updates you did?", vbYesNo) = vbYes
Then

CommitTrans

Else

Rollback

End If


Note that the above code could also include dao code. In fact, any code that
updates the data can be rolled back. So:


BeingTrans

strSql = "select tblCustomer Where City = 'Edmon'"

Set rst = CurrentDb.OpenRecordset(strSql)
While rst.EOF = False
rst!City = "Edmonton"
rst.Update
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

strSql = "update tblCustomer set City = 'LA' where City = 'Los
Angles'"
CurrentDb.Execute strSql

' now ask if all the code, and the sql dupates should be committed?

If MsgBox("Do all the zillions of updates you did?", vbYesNo) = vbYes
Then

CommitTrans

Else

Rollback

End If


Note THAT FORMS UPDATES CAN NOT BE ROLLED BACK! In other words, you cannot
wrap forms and the user interface into a trans action!. ONLY CODE that
updates the data can use a transaction, not a standard form. So, you can't
have a user edit a form, move around and edit, and then roll back. It is
only CODE that can be wrapped in a transaction. You can most certainly use
forms to prompt, and ask the user for update stuff, but bound forms to a
data table can NOT be wrapped in a transaction.


Here is some more sample code, lifted from a97 help:




Sub BeginTransX()

Dim strName As String
Dim strMessage As String
Dim wrkDefault As Workspace
Dim dbsNorthwind As Database
Dim rstEmployees As Recordset

' Get default Workspace.
Set wrkDefault = DBEngine.Workspaces(0)
Set dbsNorthwind = OpenDatabase("Northwind.mdb")
Set rstEmployees = _
dbsNorthwind.OpenRecordset("Employees")

' Start of outer transaction.
wrkDefault.BeginTrans
' Start of main transaction.
wrkDefault.BeginTrans

With rstEmployees

' Loop through recordset and ask user if she wants to
' change the title for a specified employee.
Do Until .EOF
If !Title = "Sales Representative" Then
strName = !LastName & ", " & !FirstName
strMessage = "Employee: " & strName & vbCr & _
"Change title to Account Executive?"

' Change the title for the specified employee.
If MsgBox(strMessage, vbYesNo) = vbYes Then
.Edit

!Title = "Account Executive"
.Update
End If
End If

.MoveNext
Loop

' Ask if the user wants to commit to all the changes
' made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
wrkDefault.CommitTrans
Else
wrkDefault.Rollback
End If

' Print current data in recordset.
.MoveFirst
Do While Not .EOF
Debug.Print !LastName & ", " & !FirstName & _

" - " & !Title
.MoveNext
Loop

' Roll back any changes made by the user since this is
' a demonstration.
wrkDefault.Rollback
.Close
End With

dbsNorthwind.Close

 

[Steve]

Albert,

If your code was creating a bunch of new records rather than updating existing
records, could you still rollback all the new records?

And if the primary key was autonumber and you rolled back, when you next entered
a new record, would the autonumber be 1 more than the last existing autonumber
or 1 more than the last autonumber for the records you rolled back?

Thanks!

Steve

 

[Albert D. Kallal]

Albert,

If your code was creating a bunch of new records rather than updating existing
records, could you still rollback all the new records?

Yes, you could.

And if the primary key was autonumber and you rolled back, when you next entered
a new record, would the autonumber be 1 more than the last existing autonumber
or 1 more than the last autonumber for the records you rolled back?

I don't know the above answer. I would bet that it would be still at the
"highest" autonumber used (ie: there would be a gap). On the other hand, it
should not matter. Who cares, or ever even looks at a autonumber? I mean, do
you care what disk sector the data is being stored on? AutoNumbers are
simply internal numbers, and they cannot have any external useful meaning in
ms-access. If you need some numbering scheme for your application, you can
not, and should not use AutoNumbers, as they cannot reliability be given ANY
meaning at all.

So, for the 2nd answer, it really does not matter one hoot, as autonumbers
are only internal house keeping numbers that can have no meaning to humans.

Anytime a new record is create, and the user starts editing, and then does a
un-do, then autonumber is skipped (it has been incremented). Further,
anytime you compact a database, then the highest autonumber used is re-set,
and then some autonumbers will be used again. Thus, as mentioned, you can't
use these numbers for human consumption, as they change, and often do not
increment one at a time.

For that above tip on autonumber, and few others..check out:

http://www.mvps.org/access/tencommandments.htm