Updating Data in a Recordset

Updating (or editing) existing data in a Recordset pulls together parts of most of the previous sections. After creating and populating the Recordset object, you must find the record or records you want to modify. Then, you commit the change by executing an ADO Update method, which you reviewed earlier in the "Adding Data Using a Recordset" section on p. 260.

CAUTION

Before you can actually run any of the procedures in this section, you must change the Field Size property of the State field in the Clients table to 10 or greater. If you don't, the procedures will return an error because the State field won't be large enough to accommodate the string "California."

First, let's look at an explicit call to Update, which is probably the safest way to approach changes because you control when the update takes place. The following procedure illustrates updates to existing data by changing the State two-letter abbreviations in the Clients table to the state's actual name. This procedure accepts two passed values, st and state, which represent the two-letter abbreviation and the full spelling, respectively:

 Sub ChangeStateExplicit1(st As String, state As String)   'Explicitly call Update to change state value   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   With rst     .Open "Clients", CurrentProject.Connection, adOpenDynamic, _      adLockOptimistic     .Find "State = '" & st & "'"     If .EOF = True Then       MsgBox "There are no matches", vbOKOnly       Exit Sub     Else      .Update "State", state      MsgBox .Fields("State"), vbOKOnly     End If   End With   rst.Close   Set rst = Nothing End Sub 

Enter the procedure in a standard module or use Chapter 17's example module. In the Immediate window, run the statement

 ChangeStateExplicit1 "CA", "California" 

Click OK when the MsgBox function displays the new value for the current record, as shown in Figure 17.6. If you view the Clients table, you'll find that the first record that matched "CA" now equals the string "California," as shown in Figure 17.7.

Figure 17.6. The updating procedure shows the current record's new value.

Figure 17.7. The first CA value has been changed to California.

The Find method locates the first record to contain st in the State field. Then, the Update method changes the current value, st, to the full spelling in state.

Alternatively, you can omit the two arguments and use the Update method to commit values stated earlier, as shown in this next procedure:

 Sub ChangeStateExplicit2(st As String, state As String)   'Explicitly call Update to change state value   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   With rst     .Open "Clients", CurrentProject.Connection, adOpenDynamic, _      adLockOptimistic     .Find "State = '" & st & "'"     If .EOF = True Then       MsgBox "There are no matches", vbOKOnly       Exit Sub     Else       .Fields("State") = state       .Update       MsgBox .Fields("State"), vbOKOnly     End If   End With   rst.Close   Set rst = Nothing End Sub 

There's little difference between the two processes. Both are explicit calls and both control when the change is actually committed against the data.

You can omit the Update method from the actual code by an implicit call. After supplying a new value, simply change the current record to call the Update method. The following procedure illustrates this process:

 Sub ChangeStateImplicit(st As String, state As String)   'Implicitly call Update to change state value   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   With rst     .Open "Clients", CurrentProject.Connection, adOpenDynamic, _      adLockOptimistic     .Find "State = '" & st & "'"     If .EOF = True Then       MsgBox "There are no matches", vbOKOnly       Exit Sub     Else       .Fields("State") = state       .MoveNext       .MovePrevious       MsgBox .Fields("State"), vbOKOnly     End If   End With   rst.Close   Set rst = Nothing End Sub 

Notice that there's no call to the Update method in this last example. After stating the new value in the .Fields("State") = state statement, the MoveNext method calls the Update method automatically. You're not committed to a change until you move the current record or explicitly call the Update method. There are two ways to dump changes before they're made:

• Call the CancelUpdate method.

• Close or destroy the Recordset object (by setting it to Nothing).

NOTE

When creating a Recordset that updates data, remember that the type of lock can matter if more than one user will be using the database at the same time. All the examples in this section use optimistic locking. That means ADO doesn't try to resolve conflicts that can arise when more than one person attempts to edit the same data at the same time. As a rule, your attempt to update the record will fail if another user updates the record first. Use pessimistic locking if you want ADO to resolve conflicts and commit all edits.