Edit

Share via

Chapter 3: Examining data

  • Article

Applies to: Access 2013, Office 2013

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.

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.

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

Rssle Sauerkraut

45.6000

51

Manjimup Dried Apples

53.0000

74

Longlife Tofu

10.0000

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

This chapter covers the following topics: