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()
    Dim objRs As ADODB.Recordset
    Dim strSQL As String
    Dim strConnStr As String

    Set objRs = New ADODB.Recordset

    strSQL = "SELECT ProductID, ProductName, UnitPrice FROM Products " & _
           "WHERE CategoryID = 7"          '7 = Produce

    strConnStr = "Provider=SQLOLEDB;Initial Catalog=NorthWind;" & _
             "Data Source=MySrv;User Id=MyId;Password=123aBc;"
    
    objRs.Open strSQL, strConnStr, adOpenForwardOnly, _
             adLockReadOnly, adCmdText
    
    ' work with Recordset...
'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.

FIELD 0 Name = ProductID	FIELD 1 Name = ProductName	FIELD 2 Name = UnitPrice
7	Uncle Bob's Organic Dried Pears	30.0000
14	Tofu	23.2500
28	Rössle Sauerkraut	45.6000
51	Manjimup Dried Apples	53.0000
74	Longlife Tofu	10.0000

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