Using Transactions to Commit Groups of Records-or Not

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:

• BeginTrans This method marks the spot where the provider begins to group the data changes.

• CommitTrans You must execute this method to actually commit the group of changes to the underlying data source.

• RollbackTrans All uncommitted changes are dumped.

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.

CASE STUDY: Using a Recordset Object to Add Items to a Combo Box

In Chapter 12, you learned about list controls, including how to add an item to an existing control's list. The examples worked directly with the data source using SQL statements. You can do the same with a Recordset object.

Currently, the project name control in the Projects form shown in Figure 17.9 is a text box control, which means users have to continually re-enter these names. This setup is less efficient and allows typos to creep into the data. One way to limit typos and keep users happy is to provide a combo box that supplies all possible entries for that field.

Figure 17.9. Changing the project name text box to a combo box control.

Occasionally, users might need to introduce a new project name. You learned in Chapter 12 that the combo box can accommodate new values. Let's provide a combo box that lists the existing project names and also accepts new ones. To do so, complete the following steps:

1. Open the Projects form in Design view.
   
   

2. Right-click the project name text box and choose Change To from the resulting context menu, and then choose Combo Box from the resulting submenu.
   
   

3. Refer to Table 17.3 for the new control's properties.
   
   

   Table 17.3. Combo Box Properties

   Property	Setting
   Row Source	SELECT DISTINCT Projects.ProjectName FROM Projects ORDER BY Projects.ProjectName
   Limit To List	Yes

   
   

4. Open the form's module and enter the following event procedure:
   
   

   
   

    Private Sub ProjectName_NotInList(NewData As String, Response As  Integer)   'Handle non-list items entered into   'ProjectName combo box   Dim bytResponse As Byte   Dim cnn As ADODB.Connection   Dim rst As ADODB.Recordset   'On Error GoTo HandleErr   bytResponse = MsgBox("Do you want to add " _    & NewData & " to the list?", vbYesNo)   Set cnn = CurrentProject.Connection   Set rst = New ADODB.Recordset   With rst     .Open "Projects", cnn, adOpenDynamic, _        adLockOptimistic     If bytResponse = vbYes Then       .AddNew "ProjectName", NewData       .Update       Response = acDataErrAdded     ElseIf bytResponse = vbNo Then       Response = acDataErrContinue       ProjectName.Undo       GoTo ExitHere     End If   End With ExitHere:   rst.Close   Set rst = Nothing   cnn.Close   Set cnn = Nothing   Exit Sub HandleErr:   MsgBox "Error " & Err.Number & ": " & _    Err.Description, vbOKOnly   Resume ExitHere End Sub 

   
   

5. Save the form and view it in Form view. Figure 17.10 shows the new combo box with its open list.
   
   

   Figure 17.10. View the new combo box control's list.

   

   
   

6. Click the New Record button on the form's navigation toolbar.
   
   

7. Choose any client from the client list.
   
   

8. Instead of choosing an item from the project list, enter Quality Control, and press Tab or Enter. Doing so triggers the control's NotInList event, which displays the message box shown in Figure 17.11.
   
   

   Figure 17.11. Entering a non-list item triggers the control's NotInList event.

   

   
   

9. Click Yes.
   
   

10. Open the drop-down list to view the newly added item shown in Figure 17.12. If you open the Projects table, you'll find a new record with Quality Control as the project name value.
    
    

    Figure 17.12. The NotInList event adds the new item to the list.

    

    
    

If you click No in response to the message box shown in Figure 17.11, the event removes the non-list item from the control's text box component and does not add the item to the control's data source (the Projects table). Consequently, the item is not added to the control's list.