BeginTrans, CommitTrans, Rollback Methods Example (MDB)
The following example changes the job title of all sales representatives in an Employees table. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved by using the Update method. The example also shows how to maintain a flag to properly manage errors that might occur during a transaction.
One or more table pages remain locked while the user decides whether or not to accept the changes. Consequently, this technique isn't recommended but is shown only as an example.
Sub ChangeTitle()
Dim wsp As Workspace, dbs As Database, rst As Recordset
Dim strName As String, strMessage As String, _
strPrompt As String
Dim fInTrans As Boolean
On Error GoTo ChangeTitleErr
fInTrans = False
strPrompt = "Change title to Account Executive?"
' Return reference to default Workspace object.
Set wsp = DBEngine.Workspaces(0)
' Return reference to current database.
Set dbs = CurrentDb
' Create table-type Recordset object.
Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
' Start of transaction.
wsp.BeginTrans
fInTrans = True
rst.MoveFirst
Do Until rst.EOF
If rst!Title = "Sales Representative" Then
strName = rst!LastName & ", " & rst!FirstName
strMessage = "Employee: " & strName & vbCrLf & vbCrLf
If MsgBox(strMessage & strPrompt, vbQuestion + _
vbYesNo, "Change Job Title") = vbYes Then
' Enable editing.
rst.Edit
rst!Title = "Account Executive"
' Save changes.
rst.Update
End If
End If
' Move to next record.
rst.MoveNext
Loop
If MsgBox("Save all changes?", vbQuestion + vbYesNo, _
" Save Changes") = vbYes Then
wsp.CommitTrans ' Commit changes.
Else
wsp.Rollback ' Undo changes.
End If
ChangeTitleExit:
rst.Close
Set dbs = Nothing
Set wsp = Nothing
Exit Sub
ChangeTitleErr:
MsgBox "Error!"
If fInTrans Then
wsp.Rollback
End If
Resume ChangeTitleExit
End Sub