An ADO Transaction
Let's look at an ADO example of a transaction that uses a few of the optional constants described above for opening our recordset. In this example, we examine the syntax for using a transaction. We just append a "" to each of the more than 8,500 titles in the Titles table. But if there is an error anywhere, the entire enchilada is rolled back and the original table is returned to its fresh as the new-driven snow state (mmm, enchilada's in the snow!). The transaction is sandwiched between the .BeginTrans and .CommitTrans method calls on the connection object:
Dim myConnection As ADODB.Connection
Dim myRecordset As ADODB.Recordset
Set myConnection = New ADODB.Connection
Set myRecordset = New ADODB.Recordset
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb"
'—Open the connection --
myConnection.Open
'Determine if we conected.
If myConnection.State = adStateOpen Then
myRecordset.Open "SELECT * FROM TITLES", myConnection, _
adOpenDynamic, adLockOptimistic, adCmdTable
Else
MsgBox "The connection could not be made."
myConnection.Close
Exit Sub
End If
'—just to be sure --
myRecordset.MoveFirst
On Error GoTo transError
'—here is the top of the transaction sandwich --
myConnection.BeginTrans
While Not myRecordset.EOF
mcounter = mcounter + 1
myRecordset!Title = myRecordset!Title & "" 'so we don't really change it
myRecordset.Update
myRecordset.MoveNext
Wend
'—if we got here ok, then everything is written at once
myConnection.CommitTrans
myRecordset.Close
myConnection.Close
Exit Sub
transError:
myConnection.RollBack
myRecordset.Close
myConnection.Close
MsgBox Err.Description
How It Works
After the connection is established, this example begins a transaction. The data changed in this transaction is either all committed at the end of the transaction, or it is all rolled back to the pre-transaction state. Let's take a look at the important parts of the code.
After we dim and initialize our connection and recordset objects, we build our connection string as we have been doing so far. The connection is then opened by invoking the .Open method of the connection object:
myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=c:\BegDB\Biblio.mdb"
'—Open the connection --
myConnection.Open
Then, as a precaution, we check out the .State property of the connection. Recall the general syntax of opening an ADO recordset:
adoRecordset.Open Source, ActiveConnection, CursorType, LockType, Options
Well, in our example, if the connection is open, we pass the .Open method of the recordset an SQL query string as the Source. It is requesting all of the records for the Titles table as the first parameter. Next, we pass in the **ActiveConnection**connection object, myConnection. Next, we specify the CursorType as an adOpenDynamic cursor for the recordset. This type of cursor permits all movement backwards and forwards, allows the user to make changes to the current record, and even will dynamically update if another user updates any records in the database that are included in our recordset. We will actually see any additions or deletions as they occur by others! We then specify the LockType as adLockOptimistic. This locks record by record and only occurs when the **.Update **method is called. Since we know this is a table, we pass in the Options parameter as adCmdTable. Now ADO does not have to spend time figuring out what it is going after. We have fully specified the type of recordset we want by passing each parameter.
'Determine if we connected.
If myConnection.State = adStateOpen Then
myRecordset.Open "SELECT * FROM TITLES", myConnection, _
adOpenDynamic, adLockOptimistic,adCmdTable
Else
MsgBox "The connection could not be made."
myConnection.Close
Exit Sub
End If
Once our recordset is opened, we want to perform an edit / update on each record in the recordset. Edit / Update is very expensive in terms of processing time. So we sandwich our edit / updates inside a transaction. When the .BeginTrans method is called, everything until the .Commit or .RollBack method of the connection object is reached:
'—here is the top of the transaction sandwich --
myConnection.BeginTrans
Now we do our processing. We loop through the recordset as we usually would. With ADO, no .Edit method is required as it is in DAO. Remember when we discussed ADO being slimmed down by having many redundant methods removed? Well, this is one of them. By simply changing the data, an Edit is assumed. So an Edit still occurs, but it is implicit. We don't have to explicitly call the method. So we modify (edit) each record and then call the .Update method. However, since we are in the transaction sandwich, the results are written to a temporary file. They are not yet committed to the database:
While Not myRecordset.EOF
mcounter = mcounter + 1
myRecordset!Title = myRecordset!Title & "" 'so we don't really change it
myRecordset.Update
myRecordset.MoveNext
Wend
If everything goes as planned, when we exit the loop we then call the .CommitTrans method of the connection object. Now, all of the changes are written to the data source at one time. So we are not hitting the disk for each record, writing the changes, then moving to the next record. The changes are kept, where possible, in memory and then blasted all at once to the data source. So instead of doing - say - 700 writes (one for each record), we only do a single bulk write which is much, much faster.
As I said, this technique can be used when you are not 'technically' performing a transaction. In other words, when you are not hitting two tables that require both to be changed simultaneously. When you only need to update a single table as in our simple example, this will be orders of magnitude faster than updating each individual record. Then we close the recordset and the connection when we are finished and exit the sub:
'—if we got here ok, then everything is written at once
myConnection.CommitTrans
myRecordset.Close
myConnection.Close
If we were going to perform the same task on several records such as appending an "" to each title, it would be much faster to just use the .Execute method and use SQL. But we wanted to show an example of the transaction method that you might be able to use in your every day programming.
If we run into a snag, an error is generated. Since we have an active error handler, our VB code jumps to the transError label, which is the start of the error handler. Here we roll back everything that has occurred up to this point. The .RollBack method will bring the system back to the point of the .BeginTrans method. None of the records in the transaction sandwich will be changed. We then close the recordset and connection and display a description of the error from the global Err error object:
transError:
myConnection.RollBack
myRecordset.Close
myConnection.Close
MsgBox Err.Description