Transactions

You can use transactions to bundle two or more database operations so that either they all occur or none of them occur. Transactions are useful in two types of situations: when your application must perform a set of operations as a unit (such as a funds transfer between a savings account and a checking account); and when you have to speed up a set of database operations by wrapping them in a transaction (because writing to a temporary cache in memory is faster than writing to a disk for each element within the transaction group).

Three Connection object methods in ADO support transactions: BeginTrans, CommitTrans, and Rollback. BeginTrans opens a transaction level. Jet supports up to five nested transactions. Once you start a transaction, you can end it with either the CommitTrans method or the Rollback method. You use CommitTrans to complete the database operations. You invoke the Rollback method to end a transaction without performing the database operations launched since the last BeginTrans method.

The procedure below uses three transaction methods for the Connection object. It loops through the records in a database while looking for one of three criteria matches. Once it finds a match, the procedure writes US in a field that Access stores in a data cache rather than to the database file. The procedure invokes the BeginTrans method just before starting the Do loop. At the end of the loop, the procedure asks whether the user wants to commit the changes. If the user replies yes, the sample invokes the CommitTrans method to commit the changes to the database. If the user replies no, the procedure discards the changes by invoking the Rollback method.

Sub changeInTrans() On Error GoTo changeTrap Dim cnn1 As ADODB.Connection Dim rst1 As New ADODB.Recordset Dim iChanges As Long Const conLockedByAnotherUser = -2147217887 'Open recordset based on WebBasedList in current project. Set cnn1 = CurrentProject.Connection rst1.Open "WebBasedList", cnn1, adOpenKeyset, _     adLockPessimistic, adCmdTable      'Loop through all records to find those to update to US. cnn1.BeginTrans Do Until rst1.EOF     If rst1.Fields("Country") = "" _         Or IsNull(rst1.Fields("Country")) = True _         Or rst1.Fields("Country") = "USA" Then         rst1.Fields("Country") = "US"         iChanges = iChanges + 1     End If rst1.MoveNext Loop      'Commit all changes if user says so. 'Roll changes back otherwise. If MsgBox("Are you sure that you want to update" & _     " these " & iChanges & " records?", vbYesNo, _     "Programming MS Access") = vbYes Then     cnn1.CommitTrans Else     cnn1.RollbackTrans End If Exit Sub      changeExit:     Exit Sub changeTrap:     If Err.Number = conLockedByAnotherUser Then         MsgBox "Recordset not available for update.  " & _             "Try again later.", vbCritical, _             "Programming MS Access"     Else         Debug.Print Err.Number; Err.Description     End If     cnn1.RollbackTrans     Resume changeExit      End Sub 

The changeInTrans procedure also invokes the Rollback method if a run-time error occurs—such as when the database is locked by another user. In this situation, it is impossible to edit one or more records. With some databases, performing an operation on a subset of records can corrupt the whole database. If the procedure encounters an error for any reason, it rolls back any pending operations before exiting the procedure. Invoking the Rollback method maintains the integrity of the database file.