Chapter 3: Examining Data

Chapter 2 explained how to retrieve data from a data source as a Recordset object. This chapter will discuss the Recordset in more detail, including how to navigate through the Recordset and view its data.

The following diagram illustrates the object model of the Recordset object. Click an object or collection for more information.

Recordsets have methods and properties designed to make it easy to move through them and examine their contents. Depending on the functionality supported by the provider, some Recordset methods or properties might not be available. To continue exploring the Recordset object, consider a Recordset that would be returned from the Northwind sample database on Microsoft SQL Server 2000, using the following code:

'BeginRsTour
Public Sub RecordsetTour()
    On Error GoTo ErrHandler:
    
    Dim objRs As New ADODB.Recordset
    Dim strSQL As String
    
    strSQL = "SELECT ProductID, ProductName, UnitPrice FROM Products " & _
        "WHERE CategoryID = 7"          '7 = Produce
    
    objRs.Open strSQL, strConnStr, adOpenForwardOnly, _
        adLockReadOnly, adCmdText
        
    'Clean up
    objRs.Close
    Set objRs = Nothing
    Exit Sub
    
ErrHandler:
    If Not objRs Is Nothing Then
        If objRs.State = adStateOpen Then objRs.Close
        Set objRs = Nothing
    End If

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub
'EndRsTour

This SQL query returns a Recordset with five rows (records) and three columns (fields). The values for each row are shown in the following table.

The next section will explain how to locate the current position of the cursor in this sample Recordset.