Microsoft Office 2000/Visual Basic Programmer's Guide

Updating Data in a Recordset Object

The following code demonstrates how to open a scrollable, updatable Recordset object and modify the data in a record.

Sub UpdateRecordset(strDBPath As String, _
                   strSQL As String, _
                   strUpdateFld As String, _
                   strUpdateValue As String)
   Dim cnn As ADODB.Connection
   Dim rst As ADODB.Recordset

   Set cnn = New ADODB.Connection
   ' Open the connection.
   With cnn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .Open strDBPath
   End With

   Set rst = New ADODB.Recordset
   With rst
      ' Open the Recordset object.
      .Open Source:= strSQL, _
         ActiveConnection:= cnn, _
         CursorType:= adOpenKeyset, _
         LockType:= adLockOptimistic

      ' Update the specified field for the current record.
      .Fields(strUpdateFld).Value = strUpdateValue

      ' Save the changes you made to the current record in the Recordset object.
      .Update

      ' Close the Recordset object.
      .Close
   End With

   ' Close connection and destroy object variables.
   cnn.Close
   Set rst = Nothing
   Set cnn = Nothing
End Sub

For example, to use this procedure to open the Customers table in the Northwind database, retrieve the record for the Around the Horn company, and update the ContactName to "Jane New," you can use a line of code like this:

UpdateRecordset "c:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb", _
   "SELECT * FROM Customers WHERE CustomerId = 'AROUT'",_
   "ContactName", "Jane New"

The UpdateRecordset procedure can be found in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.

ADO and DAO code for updating data in a Recordset object is very similar. The major difference between the two examples above is that DAO requires that you put the Recordset object into an editable state by using the Edit method, whereas ADO does not require that you explicitly indicate that you want to be in edit mode. In both DAO and ADO, you can verify the edit status of the current record by checking the EditMode property.

One difference between DAO and ADO to be aware of is the behavior when you update a record and then move to another record without calling the Update method. In DAO, any changes made to the current record are lost when you move to another record without first calling the Update method. In ADO, on the other hand, the changes to the current record are automatically saved when you move to a new record. You can explicitly discard changes to the current record with both DAO and ADO by using the CancelUpdate method.