If your application has already opened a recordset, you can modify data using the recordset's methods. Modifying records with a Recordset object is limited to a single addition, deletion, or update at a time. Consider using the Connection object's Execute method for performing multiple updates at once.

Adding a New Record

Adding a new record to a recordset is a two-step process. Your application must first create a new record to be added, and then it must use the Update method to send the change to the data source. To create a new record entry, use the AddNew method.

Note  The CursorType and LockType properties of the Recordset object must be set to the appropriate values in order to be able to call the AddNew, Update, or Delete methods.

The following example code creates a recordset and adds a new customer record:

Dim rsStudents As Recordset
Set rsStudents = New Recordset
  
' open a recordset
rsStudents.CursorType = adOpenKeyset
rsStudents.LockType = adLockOptimistic
rsStudents.Open "SELECT First_Name, Last_Name FROM Students", cnStateUBookstore
  
' add a new record
rsStudents.AddNew

Once the user has entered the new record's information, you must use the Update method to send the changes to the data source. The changes will then be made permanent. New records will be added to the end of the current Recordset object, regardless of the original SQL command used to create the recordset. The following example code updates a recordset:

rsStudents.Update

Deleting a Record

To delete the current record from the recordset and the data source, use the Delete method. Unlike adding or changing a record, there is only one step to cause a deletion; you do not need to use the Update method.

The following example code deletes the current record from the recordset:

rsStudents.Delete

After deleting a record, you should add code to ensure that there will be no attempts to reference data belonging to a record that has been deleted and is, therefore, invalid. The following example code shows one technique for setting the current record to one that is valid.

rsStudents.MoveNext
If rsStudents.EOF Then
 rsStudents.MoveLast
End If

Changing a Record

To edit records in an ADO recordset, specify the field to change and the new value. Then use the Update method as you would to add a new record to save the changes to the data source. These changes are reflected in the current recordset without refreshing.

To see a demonstration of how to change a record, click your modem speed or Download below.

      

(Requires the Microsoft Windows Media Player.)


Note  Unlike DAO and RDO, there is no Edit method in ADO.

The following example code creates a new Recordset object. The application navigates to the last record and changes the student's major:

Dim rsStudents as Recordset
Set rsStudents = New Recordset
  
rsStudents.Open "SELECT MajorID FROM Students"
rsStudents.MoveLast
rsStudents!MajorID = 2
rsStudents.Update