Update Method

Applies To Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only-Type Recordset object, Recordset object, Snapshot-Type Recordset object, Table-Type Recordset object.

Description

Saves the contents of the copy buffer to an updatable Recordset object.

Syntax

recordset.Update(type, force )

The Update method syntax has the following parts.

Part	Description

recordset	An object variable that represents an open, updatable Recordset object.
type	Optional. A constant indicating the type of update, as specified in Settings (ODBCDirect workspaces only).
force	Optional. 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).

Settings

You can use the following values for the type argument. You can use the non-default values only if batch updating is enabled.

Constant	Description

dbUpdateRegular	Default. Pending changes aren't cached and are written to disk immediately.
dbUpdateBatch	All pending changes in the update cache are written to disk.
dbUpdateCurrentRecord	Only the current record's pending changes are written to disk.

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

Caution 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 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 Jet 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 Jet-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 Jet workspace, or an "Invalid argument" error will occur on the Update call in an ODBCDirect workspace.

See Also AddNew method, LockEdits property, OpenRecordset method.

Specifics (Microsoft Access)

When you use a bookmark in a Microsoft Access module, you must include an Option Compare Binary statement in the Declarations section of the module. A bookmark is a Variant array of Byte data, so the string comparison method for the module must be binary. If a bookmark is evaluated with a text-based string comparison method, such as the Option Compare Text statement or the default setting for the Option Compare Database statement, the current record may be set to an incorrect record.

Example

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

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.

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

Example (Microsoft Access)

See the AddNew method example (Microsoft Access).

Example (Microsoft Excel)

This example opens Product.dbf (a dBASE IV table located in the C:\Program Files
\Common Files\Microsoft Shared\MSquery folder), finds a record with the PRODUCT value 1, and then sets the CATEGORY field to the value in cell B2 on Sheet1. (In Windows NT, Product.dbf is located in the C:\Windows\Msapps\Msquery folder.)

Dim db As Database, rs As Recordset, categoryCell As Range
Sheets("Sheet1").Activate
Set categoryCell = ActiveSheet.Cells(2, 2)
categoryCell.Value = "BEVR"
Set db = OpenDatabase("C:\Program Files\Common Files\Microsoft Shared\MSquery", _
    False, False, "dBASE IV")
Set rs = db.OpenRecordset("PRODUCT.DBF", dbOpenTable)
With rs
    .Index = "PRODUCT"
    .Seek "=", "1"
    .Edit
    .Fields("CATEGORY").Value = categoryCell.Value
    .Update
End With
MsgBox "The field has been updated with " & categoryCell.Value
rs.Close
db.Close