Step 4: Manipulate the Data (ADO Tutorial)

You are Here...

Make a connection to a data source.
Optionally, create an object to represent an SQL query command.
Optionally, specify values in the SQL command as variable parameters.
Execute the command. If the command is row-returning, store the rows in a storage object.
Optionally, navigate, examine, manipulate, and edit the data.
If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
If a transaction was used, accept or reject the changes made during the transaction. End the transaction.

Discussion

The bulk of the Recordset object methods and properties are devoted to examining, navigating, and manipulating the Recordset data.

You can think of a Recordset as an array of rows. The row you can examine and manipulate at any given time is the current row, and your location in the Recordset is the current row position. Every time you move to another row, that row becomes the new current row.

Several methods explicitly move or "navigate" through the Recordset (the Move methods). Some methods (the Find method) do so as a side effect of their operation. In addition, setting certain properties (Bookmark property) can also change your row position. The Recordset object CursorType property, or Open method CursorType parameter, determines whether you can navigate forward and backward through the Recordset.

The Filter property controls the rows you can access (that is, which rows are "visible" to you). The Sort property controls the order in which you navigate the rows of the Recordset.

You can create new rows with the AddNew method or delete existing rows with the Delete method.

A Recordset has a Fields collection that is the set of Field objects that represent each field, or field (or column) in a row. Assign or retrieve the data for a field with the Field object Value property. You can also access field data in bulk with the GetRows method. After you have modified the Recordset, propagate your changes to the data source using the Update methods.

In this tutorial, you will:

Set the Optimize property on the Properties collection of the au_lname Field object to improve the performance of sorting and filtering.
Sort the Recordset on each author's last name.
Filter the Recordset so the only accessible rows will be those where the author's telephone number is in area code "415" and the exchange begins with "5".
Change the filtered telephone numbers to the mythical area code "777."

Use the Move methods to navigate from the beginning of the sorted, filtered Recordset to the end. Stop when the Recordset object EOF property indicates you've reached the last row. As you move through the Recordset, display the author's first and last name and the original telephone number, and then change the area code in the phone field to "777". (Telephone numbers in the phone field are formatted as "aaa xxx-yyyy" where aaa is the area code and xxx is the exchange.)

As each row is changed, it no longer matches the criteria specified by the filter so it is no longer visible in the Recordset. All the rows reappear when the filter is cleared.

See the schema sidebar for the layout of the Authors table. The code looks like this:

rst!au_lname.Properties("Optimize") = True
rst.Sort = "au_lname"
rst.Filter = "phone LIKE '415 5*'"
rst.MoveFirst
Do While Not rst.EOF
    Debug.Print "Name = "; rst!au_fname; " "; rst!au_lname; _
        ", Phone = "; rst!phone
    rst!phone = "777" & Mid(rst!phone, 4)
    rst.MoveNext
Loop
rst.Filter = adFilterNone

Next Step 5: Update the Data