Adding Records Using AddNew

This is the basic syntax of the AddNew method:

recordset.AddNew FieldList, Values

The FieldList and Values arguments are optional. FieldList is either a single name or an array of names or ordinal positions of the fields in the new record.

The Values argument is either a single value or an array of values for the fields in the new record.

Typically, when you intend to add a single record, you will call the AddNew method without any arguments. Specifically, you will call AddNew, set the Value of each field in the new record, and then call Update and/or UpdateBatch. You can ensure that your Recordset supports adding new records by using the Supports property with the adAddNew enumerated constant.

The following code uses this technique to add a new Shipper to the sample Recordset. The ShipperID field value is supplied automatically by SQL Server, so the code does not attempt to supply a field value for the new records.

'BeginAddNew1.1
    If objRs1.Supports(adAddNew) Then
        With objRs1
            .AddNew
            .Fields("CompanyName") = "Sample Shipper"
            .Fields("Phone") = "(931) 555-6334"
            .Update
        End With
    End If
'EndAddNew1.1

Because this code uses a disconnected Recordset with a client-side cursor in batch mode, you must reconnect the Recordset to the data source with a new Connection object before you can call the UpdateBatch method to post changes to the database. This is easily done by using the new function GetNewConnection.

'BeginAddNew1.2
    'Re-establish a Connection and update
    Set objRs1.ActiveConnection = GetNewConnection
    objRs1.UpdateBatch
'EndAddNew1.2