Recordset.Update Method

  Note
ODBCDirect workspaces are not supported in Microsoft Office Access 2007. Use ADO if you want to access external data sources without using the Microsoft Access database engine.
Saves the contents of the copy buffer to an updatable Recordset object.

Syntax

expression.Update(UpdateType, Force)

expression   A variable that represents a Recordset object.

Parameters

Name Required/Optional Data Type Description
UpdateType Optional Long A UpdateTypeEnum constant indicating the type of update, as specified in Settings (ODBCDirect workspaces only).
Force Optional Boolean A Boolean value indicating whether or not to force the changes into the database, regardless of whether the underlying data has been changed by another user since the AddNew, Delete, or Edit call. If True, the changes are forced and changes made by other users are simply overwritten. If False (default), changes made by another user while the update is pending will cause the update to fail for those changes that are in conflict. No error occurs, but the BatchCollisionCount and BatchCollisions properties will indicate the number of conflicts and the rows affected by conflicts, respectively (ODBCDirect workspaces only).

Remarks

Use Update to save the current record  and any changes you've made to it.

Important  Changes to the current record are lost if:

  • You use the Edit or AddNew method, and then move to another record without first using Update.
  • You use Edit or AddNew, and then use Edit or AddNew again without first using Update.
  • You set the Bookmark property to another record.
  • You close the Recordset without first using Update.
  • You cancel the Edit operation by using CancelUpdate.

To edit a record, use the Edit method to copy the contents of the current record to the copy buffer. If you don't use Edit first, an error occurs when you use Update or attempt to change a field's value.

In an ODBCDirect workspace, you can do batch updates, provided the cursor library supports batch updates, and the Recordset was opened with the optimistic batch locking option.

In a Microsoft Access workspace , when the Recordset object's LockEdits property setting is True (pessimistically locked) in a multiuser environment, the record remains locked from the time Edit is used until the Update method is executed or the edit is canceled. If the LockEdits property setting is False (optimistically locked), the record is locked and compared with the pre-edited record just before it is updated in the database. If the record has changed since you used the Edit method, the Update operation fails. Microsoft Access database engine-connected ODBC and installable ISAM  databases always use optimistic locking. To continue the Update operation with your changes, use the Update method again. To revert to the record as the other user changed it, refresh the current record by using Move 0.

  Note
To add, edit, or delete a record, there must be a unique index on the record in the underlying data source. If not, a "Permission denied" error will occur on the AddNew, Delete, or Edit method call in a Microsoft Access workspace , or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

Example

This example demonstrates the Update method in conjunction with Edit method.

Visual Basic for Applications
Sub UpdateX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   With rstEmployees
      .Edit
      ' Store original data.
      strOldFirst = !FirstName
      strOldLast = !LastName
      ' Change data in edit buffer.
      !FirstName = "Linda"
      !LastName = "Kobara"

      ' Show contents of buffer and get user input.
      strMessage = "Edit in progress:" & vbCr & _
         "  Original data = " & strOldFirst & " " & _
         strOldLast & vbCr & "  Data in buffer = " & _
         !FirstName & " " & !LastName & vbCr & vbCr & _
         "Use Update to replace the original data with " & _
         "the buffered data in the Recordset?"

      If MsgBox(strMessage, vbYesNo) = vbYes Then
         .Update
      Else
         .CancelUpdate
      End If

      ' Show the resulting data.
      MsgBox "Data in recordset = " & !FirstName & " " & _
         !LastName

      ' Restore original data because this is a demonstration.
      If Not (strOldFirst = !FirstName And _
            strOldLast = !LastName) Then
         .Edit
         !FirstName = strOldFirst
         !LastName = strOldLast
         .Update
      End If

      .Close
   End With

   dbsNorthwind.Close

End Sub

This example demonstrates the Update method in conjunction with the AddNew method.

Visual Basic for Applications
Sub UpdateX2()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strOldFirst As String
   Dim strOldLast As String
   Dim strMessage As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees")

   With rstEmployees
      .AddNew
      !FirstName = "Bill"
      !LastName = "Sornsin"

      ' Show contents of buffer and get user input.
      strMessage = "AddNew in progress:" & vbCr & _
         "  Data in buffer = " & !FirstName & " " & _
         !LastName & vbCr & vbCr & _
         "Use Update to save buffer to recordset?"

      If MsgBox(strMessage, vbYesNoCancel) = vbYes Then
         .Update
         ' Go to the new record and show the resulting data.
         .Bookmark = .LastModified
         MsgBox "Data in recordset = " & !FirstName & _
            " " & !LastName
         ' Delete new data because this is a demonstration.
         .Delete
      Else
         .CancelUpdate
         MsgBox "No new record added."
      End If

      .Close
   End With

   dbsNorthwind.Close

End Sub

This example uses the BatchCollisionCount property and the Update method to demonstrate batch updating where any collisions are resolved by forcing the batch update.

Visual Basic for Applications
Sub BatchX()

   Dim wrkMain As Workspace
   Dim conMain As Connection
   Dim rstTemp As Recordset
   Dim intLoop As Integer
   Dim strPrompt As String

   Set wrkMain = CreateWorkspace("ODBCWorkspace", _
      "admin", "", dbUseODBC)
   ' This DefaultCursorDriver setting is required for
   ' batch updating.
   wrkMain.DefaultCursorDriver = dbUseClientBatchCursor

   ' Note: The DSN referenced below must be configured to 
   '       use Microsoft Windows NT Authentication Mode to 
   '       authorize user access to the Microsoft SQL Server.
   Set conMain = wrkMain.OpenConnection("Publishers", _
      dbDriverNoPrompt, False, _
      "ODBC;DATABASE=pubs;DSN=Publishers")
      
   ' The following locking argument is required for
   ' batch updating. It is also required that a table
   ' with a primary key is used.
   Set rstTemp = conMain.OpenRecordset( _
      "SELECT * FROM roysched", dbOpenDynaset, 0, _
      dbOptimisticBatch)

   With rstTemp
      ' Modify data in local recordset.
      Do While Not .EOF
         .Edit
         If !royalty <= 20 Then
            !royalty = !royalty - 4
         Else
            !royalty = !royalty + 2
         End If
         .Update
         .MoveNext
      Loop

      ' Attempt a batch update.
      .Update dbUpdateBatch

      ' If there are collisions, give the user the option
      ' of forcing the changes or resolving them
      ' individually.
      If .BatchCollisionCount > 0 Then
         strPrompt = "There are collisions. " & vbCr & _
            "Do you want the program to force " & _
            vbCr & "an update using the local data?"
         If MsgBox(strPrompt, vbYesNo) = vbYes Then _
            .Update dbUpdateBatch, True
      End If

      .Close
   End With

   conMain.Close
   wrkMain.Close

End Sub

This example uses the AddNew method to create a new record with the specified name. The AddName function is required for this procedure to run.

Visual Basic for Applications
Sub AddNewX()

   Dim dbsNorthwind As Database
   Dim rstEmployees As Recordset
   Dim strFirstName As String
   Dim strLastName As String

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   Set rstEmployees = _
      dbsNorthwind.OpenRecordset("Employees", dbOpenDynaset)

   ' Get data from the user.
   strFirstName = Trim(InputBox( _
      "Enter first name:"))
   strLastName = Trim(InputBox( _
      "Enter last name:"))

   ' Proceed only if the user actually entered something
   ' for both the first and last names.
   If strFirstName <> "" and strLastName <> "" Then

      ' Call the function that adds the record.
      AddName rstEmployees, strFirstName, strLastName

      ' Show the newly added data.
      With rstEmployees
         Debug.Print "New record: " & !FirstName & _
            " " & !LastName
         ' Delete new record because this is a demonstration.
         .Delete
      End With

   Else
      Debug.Print _
         "You must input a string for first and last name!"
   End If

   rstEmployees.Close
   dbsNorthwind.Close

End Sub

Function AddName(rstTemp As Recordset, _
   strFirst As String, strLast As String)

   ' Adds a new record to a Recordset using the data passed 
   ' by the calling procedure. The new record is then made
   ' the current record.
   With rstTemp
      .AddNew
      !FirstName = strFirst
      !LastName = strLast
      .Update
      .Bookmark = .LastModified
   End With

End Function

save changes to record save changes to recordset save record save recordset saverecord