ADO - Update method

In ADO (ActiveX Data Objects), the Update method is used to save changes made to a record in a Recordset back to the database. It works both for new records added via AddNew and for modifications to existing records.

1. Purpose

  • To commit changes made to the current record in the Recordset to the underlying database.

  • Without calling Update, changes made in memory are not saved.

2. Syntax

rs.Update [Fields], [Values]

  • Optional Parameters:

    • Fields: Array of field names to update.

    • Values: Array of values corresponding to the fields.

If you don’t specify Fields and Values, it updates all modified fields of the current record.

3. Example 1 – Update a New Record

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

rs.Open "Employees", conn, adOpenKeyset, adLockOptimistic

rs.AddNew
rs("EmployeeName") = "John Doe"
rs("Department") = "Sales"
rs("Salary") = 50000

rs.Update   ' Saves the new record to the database

4. Example 2 – Update an Existing Record

rs.Open "SELECT * FROM Employees WHERE EmployeeID = 101", conn, adOpenKeyset, adLockOptimistic

rs("Salary") = 60000   ' Change in memory
rs.Update              ' Save the updated salary to the database

5. Example 3 – Update Specific Fields Using Parameters

rs.Update Array("Salary", "Department"), Array(65000, "HR")

6. Key Points

  • Must use a lock type that supports updates, such as:

    • adLockOptimistic

    • adLockPessimistic

  • Works in combination with:

    • AddNew → for new records

    • Direct field assignment → for existing records

  • Does not move the record pointer; you remain on the current record after updating.

  • Changes are applied immediately to the database when Update is called.

7. Workflow Example

rs.Open "Employees", conn, adOpenKeyset, adLockOptimistic

' Add a new record
rs.AddNew
rs("EmployeeName") = "Alice Brown"
rs("Department") = "Marketing"
rs.Update   ' Save new record

' Update an existing record
rs.MoveFirst
rs("Salary") = 70000
rs.Update   ' Save changes