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 rows of Recordset data.

A Recordset can be thought of 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 Filter property can be applied to control 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.

A Recordset has a Fields collection, which is the set of Field objects that represent each field, or column, in a row. Assign or retrieve the data for a field from the Field object’s Value property. As an option, you can access field data in bulk (the GetRows and Update methods).

In this tutorial, you will:

• Assume that telephone numbers in the "415" area code with exchanges starting with "5", are changing to the mythical area code "777."
  
  
• Set the Optimize property 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 (that is, "visible") rows will be those where the author's area code is "415" and exchange begins with "5".

Use the Move methods to navigate from the beginning of the sorted, filtered Recordset to the end. Stop when the Recordset 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, then change the area code in the phone field to "777". (Telephone numbers in the phone field are of the form "aaa xxx-yyyy" where aaa is the area code and xxx is the exchange.)

rs!au_lname.Optimize = TRUE
rs.Sort = "au_lname ASCENDING"
rs.Filter = "phone LIKE '415 5*'"
rs.MoveFirst
Do While Not rs.EOF
   Debug.Print "Name: " & rs!au_fname & " " rs!au_lname & _
      "Phone: " rs!phone & vbCr
   rs!phone = "777" & Mid(rs!phone, 5, 11)
   rs.MoveNext
Loop

Next   Step 5