|< Day Day Up >|
Using Transactions to Commit Groups of Records or Not
Access doesn't support transaction processing directly. However, ADO's Connection object does, so you can benefit from transaction processing even though Access doesn't directly support the process.
Transaction processing involves treating multiple updates to multiple records as one process instead of several. If one update fails, they all fail. If one update is made, they're all made. This type of control is important to business processes where changing one value can have far-reaching results. For instance, let's suppose you commit an order, update the inventory numbers, and update an accounting application for billing purposes, accordingly. Now, what happens if the customer calls to cancel part or even all of the order? Not only must you clear the order from the customer's account, you must also return the items to the inventory. If you do one without the other, the balance sheet will be off somewhere and it might take a long time to find the error.
Not only does transaction processing protect the validity of your data from obvious logic errors, such as in the previous scenario, but transaction processing can also protect your data in the event of a network or power failure. Where Access is concerned, this issue can be touch-and-go. ADO's transaction processing can't be solely trusted in this area, but it's better than nothing. (The technical issues that come into play are well outside the scope of this book.)
You use three methods to implement transaction processing, all three belonging to the Connection object:
In the previous section, you used the Update method to change the CA values in the Clients table to "California," but only one at a time. Now, let's include a loop that changes all the CA values in the table, but wrap the process in a transaction so either all or none of the changes are committed. Enter the following procedure into a standard module or use Chapter 17's example module:
Sub ChangeStateTransaction(st As String, state As String) 'Wrap implicitly call to Update 'in transaction to committ all changes 'at one time, or not at all. Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim bytResponse As Byte Set cnn = CurrentProject.Connection Set rst = New ADODB.Recordset With rst .Open "Clients", cnn, adOpenDynamic, _ adLockOptimistic .Find "State = '" & st & "'" cnn.BeginTrans Do Until .EOF .Fields("State") = state .MoveNext Loop bytResponse = MsgBox("Ready to commit changes to " _ & "State field?", vbYesNo) If bytResponse = vbYes Then cnn.CommitTrans ElseIf bytResponse = vbNo Then cnn.RollbackTrans End If End With rst.Close Set rst = Nothing End Sub
(If you didn't change the State (Clients table) Field Size property to 10 or greater in the previous section, do so now.)
Run the following statement in the Immediate window:
ChangeStateTransaction "CA", "California"
After creating and populating the Recordset object, the procedure selects the first record that contains the string "CA" in the State field. Then, the BeginTrans method is executed. Subsequently, all the updates made by the following Do Until loop are gathered, but not committed.
When prompted to commit the changes the first time, click No in the message box shown in Figure 17.8. If you like, open the Clients table to prove to yourself that those changes really weren't made. Then, run the procedure again, clicking Yes. This time when you check the table, you'll see that each State field now contains the string "California" instead of "CA." Unlike the earlier examples, this procedure doesn't warn you if you pass a value that has no matching records in the State field. If there are no matching values, nothing is changed, but there's no warning.
Figure 17.8. Click No to dump the updates.
|< Day Day Up >|