ADO - AddNew

In ADO (ActiveX Data Objects), the AddNew method is used to add a new record to a Recordset. It prepares the Recordset for inserting data and works together with the Update method to save the new record to the database.

1. Purpose

  • To create a new, blank record in a Recordset that supports adding records.

  • After calling AddNew, you can assign values to fields before saving.

2. Syntax

rs.AddNew [FieldList], [Values]

  • FieldList (optional): A string or array of field names to set.

  • Values (optional): A single value or array of values corresponding to the fields.

If FieldList and Values are not provided, you can assign field values manually after AddNew.

3. Example 1 – Simple AddNew

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

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

' Add a new record
rs.AddNew
rs("EmployeeName") = "John Doe"
rs("Department") = "Sales"
rs("Salary") = 50000
rs.Update   ' Save the new record to the database

4. Example 2 – Using FieldList and Values

rs.AddNew Array("EmployeeName", "Department", "Salary"), Array("Jane Smith", "HR", 55000)
rs.Update

5. Key Points

  • Must use a cursor type and lock type that supports adding records, e.g.,

    adOpenKeyset, adOpenDynamic
adLockOptimistic or adLockPessimistic

  • After calling AddNew, the record is not yet saved; you must call Update.

  • If you don’t call Update, the new record is discarded.

  • Can be used in bound or unbound Recordsets.

6. Workflow

  1. Open a Recordset that supports adding records.

  2. Call AddNew.

  3. Assign values to fields.

  4. Call Update to save.

rs.AddNew
rs("Field1") = Value1
rs("Field2") = Value2
rs.Update