Page 519 - Microsoft Office Excel 2003 Programming Inside Out

Introducing ADO

When you initially open the Recordset object, the current record pointer will point to the first
row. Calling the MoveNext method will move the current record pointer through each of the
rows, up to and including the last row. If the current record pointer is on the last row, calling
MoveNext will set the EOF property to True. If you call MoveNext a second time, although the
EOF property is True, you’ll get an error.

Tip The Ends of the Earth
Never call the MoveNext method when the EOF property is True, and never call the
MovePrevious method when the BOF property is True. Calling either of these methods will
result in an error because it’s impossible to move the current record pointer before the
beginning of the Recordset (MovePrevious) or move the current record pointer after the end
of the Recordset (MoveNext).

The RecordCount property contains the total number of rows retrieved from the database.
Remember that this property may have a value of -1, meaning that the total isn’t known at the
current time. If this happens, use the MoveLast method to move to the end of the Recordset.
Using the MoveLast method will also update the RecordCount property.
The Filter property can be used for several different purposes. The most useful is when you
assign it a string value containing an expression similar to one you’d use in an SQL Where
clause. This technique can be useful if you wish to restrict the Recordset to only a subset of the
data you just retrieved.

Tip Only Take What You Need
Although you can use the Filter property to view a subset of the rows returned from the
database, it’s always better to retrieve only those rows you really want to process from
the database.

The Sort property is essentially the same as the SQL Select statement’s Order By clause. Simply
assign a list of column names followed by an optional Asc keyword or a Desc keyword and
then a comma, and ADO will sort the rows in the Recordset and return them to your program
in the desired order.

You can access each individual column through the Fields collection. You can extract the
current value for the column through a Field object as well as by saving a new value into the Chapter 23
column in the current row. Changing any of the values forces the EditMode property to
change from adEditNone to adEditInProgress. Once you are finished making changes, you
should call the Update method to save the changes to the database.
The first step in writing data to a table is to make sure the EditMode property is set to
adEditAdd. Then you can use the AddNew method to insert a blank row at the end of the
recordset, into which you can write each column’s value using the values in the Fields collec­
tion. Finally, you use the Update method to save the changes to the database.

493
Part 6: Excel and the Outside World: Collaborating Made Easy