Microsoft Office 2000/Visual Basic Programmer's Guide

Using Transactions in Your Solutions

To provide transactions when you use ADO with the Microsoft Jet 4.0 provider, you use the BeginTrans, CommitTrans and RollbackTrans methods of the Connection object. The basic syntax is shown in the following table.

The following example changes the job title of all sales associates in the Employees table of the Northwind database. After the BeginTrans method starts a transaction that isolates all of the changes made to the Employees table, you can choose to use the CommitTrans method to save the changes, or you can use the RollbackTrans method to undo changes that you saved within the transaction by using the Update method.

Sub ChangeTitle()
   Dim cnn          As ADODB.Connection
   Dim strConnect   As String
   Dim strSQL       As String

   Const conFilePath As String = "C:\Program Files\Microsoft " _
      & "Office\Office\Samples\Northwind.mdb"

   ' Format connection string.
   strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conFilePath

   ' Open Connection object.
   Set cnn = New ADODB.Connection
   cnn.Open strConnect

   ' Format SQL statement to update records.
   strSQL = "UPDATE Employees SET Title = " _
      & "'Sales Associate' WHERE Title = 'Sales Representative'"

   ' Begin transaction.
   cnn.BeginTrans

   ' Execute SQL statement.
   cnn.Execute strSQL

   ' Prompt user to save changes or roll back.
   ' Note that this is being done for demonstration
   ' purposes. In an actual solution, you shouldn't 
   ' leave a transaction waiting for a user's response
   ' because all affected records are locked until
   ' the user responds.
   If MsgBox("Save all changes?", vbQuestion + vbYesNo) = vbYes Then
      ' Commit transaction.
      cnn.CommitTrans
   Else
      ' Roll back transaction.
      cnn.RollbackTrans
   End If
   
   ' Close Connection object and destroy object variable.
   cnn.Close
   Set cnn = Nothing
End Sub

The ChangeTitle procedure can be found in the Transactions module of the MultiuserDatabase.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH16 subfolder on the Office 2000 Developer CD-ROM.

Note   In DAO, because the scope of transactions is determined by the Workspace object, transactions are global to the workspace, not to a specific database or set of records. If you perform operations on more than one database or set of records within a DAO Workspace object's transaction, the CommitTrans and RollbackTrans methods affect all the objects that were changed while using that connection during the transaction. However, this concern doesn't apply when using transactions against an ADO Connection object with an Access database, because an ADO Connection object can open only one database at a time. It is possible to create a transaction that spans multiple Connection objects when using ADO with the Microsoft Transaction Server transaction processor, but the Microsoft Jet 4.0 OLE DB Provider doesn't support this functionality.

Another common use of transactions in multiuser environments is to make sure users don't see an incomplete view of shared data as it's being changed. For example, assume your solution is running code that is updating data, and another user is simultaneously running a report on that data. If you don't wrap your updates in a transaction, the user running the report could receive inconsistent data if some records have been updated by your code, and some have not been. If you wrap your updates in a transaction, however, the other user can't receive inconsistent data because all records are updated at once.

Try not to keep a transaction open too long. All locks that result from your edits with the transaction are kept in place until the transaction is committed or rolled back. This can diminish the concurrency of your multiuser solution. If your transaction needs to process many records, break up the operation into a series of smaller transactions.