Update Method

Applies To

Dynaset-Type Recordset Object, Recordset Object, Table-Type Recordset Object.

Description

Saves the contents of the copy buffer to a specified dynaset- or table-type Recordset object.

Syntax

recordset.Update

The recordset placeholder represents the name of an open, updatable dynaset- or table-type Recordset object.

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 the recordset referred to by recordset without first using Update.
You cancel the Edit.
You move off the record.

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.

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 with a trappable error (3197). Note that optimistic locking is always used on external database formats such as ODBC or installable ISAM. 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 using the Move methods, or set the Bookmark property to itself, as shown in the following example:

' Error 3197 detected; Restore record from database (as changed).
rstEmployees.BookMark = rstEmployees.Bookmark

To add a new record to a recordset, use the AddNew method.

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

See the Edit method example.

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 \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. (On Windows NT, PRODUCT.DBF is located in the \WINDOWS\MSAPPS\MSQUERY folder.)

Const sourceDir = "C:\Program Files\Common Files\Microsoft " _
    & "Shared\MSquery"
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(sourceDir, 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