Office
 

Microsoft Access 2010 : DATA ACCESS WITH ADO (part 3) - Navigating Recordsets

11/21/2011 10:08:29 AM

4. Navigating Recordsets

Recordset objects are a collection of Record objects. The current record cursor defines the Record object that the Recordset is currently pointing at. Therefore, navigating through the records is necessary to gather the data from any given Record. A number of methods are available in the Recordset object to complete this task.

4.1. The Move Methods

When a Recordset object is created, and it has records, the current record cursor is automatically moved to the first record. To move forward (and backward, if supported by the cursor) through the records in the Recordset, there are five basic methods, each of which is listed and described in Table 2 below.

Table 2. The Move Methods of a Recordset Object
METHODDESCRIPTION
MoveMoves the cursor to the specified record.
MoveFirstMoves the cursor to the first record.
MoveNextMoves the cursor to the next record.
MovePreviousMoves the cursor to the previous record.
MoveLastMoves the cursor to the last record.

The following code provides an example of navigating records:

Public Sub NavigateRecords()

' Define Variables
Dim rs As New ADODB.Recordset
Dim results As String

' Get a Recordset from one of our previous example functions
Set rs = OpenRecordsetFromSql

' Move through the Recordset
results = results & "Name: " & rs(3).Value & vbNewLine
rs.MoveNext
results = results & "Name: " & rs(3).Value & vbNewLine
rs.MovePrevious
results = results & "Name: " & rs(3).Value & vbNewLine
rs.MoveLast
results = results & "Name: " & rs(3).Value & vbNewLine
rs.MoveFirst
results = results & "Name: " & rs(3).Value & vbNewLine

' Show the results
MsgBox results

End Sub


If the cursor is moved before the first record or after the last record, the BOF and EOF properties are set, respectively.

4.2. The Seek Method

The Seek method is the fastest way to find a specific record, but it can only be used with server-side cursors on tables that have been opened as adCmdTableDirect because it specifically relies on the table's indexes (and the indexes reside on the server — not on the client). Naturally, the table must have at least one index for it to search on. And, unfortunately, it is not supported by all providers.

To use the ADO Seek method, three items must be specified: The name of the index key to use, a variant array whose members specify the values to be compared with the key columns, and a SeekEnum constant that defines the kind of Seek to execute. The Recordset object's Index property must be set prior to calling the Seek method.

Public Sub SeekRecord()

' Define Variables
Dim rs As New ADODB.Recordset
Dim strSQL As String

' Open the Recordset
strSQL = "SELECT [CONTACTS].* FROM [CONTACTS]"
rs.Open strSQL, "<Connection to Seek Supported Provider>"

' Seek to the record we want
rs.Index = "ID"
rs.Seek 4, adSeekFirstEQ

' Show the results
MsgBox "Name: " & rs(3).Value

End Sub


If the method finds a record that matches the criteria, the Recordset object's cursor is moved to that row, and if not, to the end of the recordset. So, if no matching record is found, the Recordset object's EOF property is set to True.


4.3. The Find Method

The Recordset.Find method can also be used to navigate to a specific record. The Find method takes four parameters: Criteria, SkipRows, SearchDirection, and Start, but only the Criteria is required. Unless otherwise specified, all searches begin at the current row.

Public Sub FindRecord()

' Define Variables
Dim rs As New ADODB.Recordset

' Open the Recordset
rs.Open "Contacts", CurrentProject.Connection

' Find the record we want
rs.Find "[Last Name] = 'Griffith'"

' Show the results
MsgBox "Name: " & rs("First Name").Value

End Sub


Once the record is found, the current record cursor is moved to the record, as shown in the last example.

There are two other interesting points to note about the Find method. First, literal string values for the Criteria parameter can be specified either within single quotes or within hash characters. For example:

"State = 'NY'" or "State = #NY#"

Also, the use of the asterisk as a wildcard character in the Criteria parameter is restricted. It be can specified at the end of the criteria string, or at the beginning and end. But, the asterisk cannot be at the beginning only (without one also being at the end), or in the middle. Table 3 illustrates this point.

Table 3. Use of the Asterisk Operator for the Criteria Parameter
EXAMPLEVALIDITY
State LIKE '*York'Illegal
State LIKE 'New*'OK
State LIKE '*ew Yor*'OK
State LIKE 'New *ork'Illegal

4.4. The AbsolutePosition Property

Assuming the provider supports absolute positioning, the AbsolutePosition property enables you to move the cursor to a specific row in the Recordset. For example, to move to the 127th row, issue the following call:

rs.AbsolutePosition = 127

ADO provides three constants in the CursorPositionEnum to verify the cursor position:

  • adPosUnknown: The recordset is empty, or the provider doesn't support absolute positioning.

  • adPosBOF: True if the current cursor position is before the first record.

  • adPosEOF: True if the current cursor position is after the last record.

The AbsolutePosition property will be set to one of these values if the current record cursor does not point to a valid record.

 
Others
 
- Microsoft Access 2010 : DATA ACCESS WITH ADO (part 2) - Creating Recordsets
- Microsoft Access 2010 : DATA ACCESS WITH ADO (part 1) - Using the Execute Method
- Microsoft Word 2010 : Adding Supplementary Elements - Creating a Bibliography
- Microsoft Word 2010 : Adding Supplementary Elements - Figure Captions & Adding a Table of Figures
- Microsoft Visio 2010 : Tips for Creating Organizational Charts
- Microsoft Visio 2010 : Tips for Creating Timelines
- Microsoft PowerPoint 2010 : Prepare for Delivery - Rehearsing Presentations
- Microsoft PowerPoint 2010 : Prepare for Delivery - Adapting Presentations for Different Audiences
- Microsoft Excel 2010 : Analyzing Worksheet Data - Creating Groups and Outlines, Converting Text to Columns
- Microsoft Excel 2010 : Analyzing Worksheet Data - Charting a PivotTable
- Microsoft Outlook 2010 : Managing a Calendar - Setting Up a Meeting
- Microsoft Outlook 2010 : Managing a Calendar - Adding an Event
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 2)
- Customizing Microsoft OneNote 2010 : Setting Preferences for Editing and Searching (part 1)
- Microsoft Project 2010 : Managing Multiple Projects (part 2) - Linking Tasks in Different Projects
- Microsoft Project 2010 : Managing Multiple Projects (part 1) - Creating a Master Project
- Microsoft Project 2010 : Using Sorts and Auto-filters
- Microsoft Access 2010 : Enhancing Reports with VBA - WORKING WITH VBA IN REPORTS
- Microsoft Access 2010 : Enhancing Reports with VBA - INTRODUCTION TO REPORTS
- Microsoft Word 2010 : Creating a Table of Contents
 
 
Most View
 
- Packaging and Deploying Sharepoint 2013 Apps : Anatomy of an App (part 1)
- Microsoft Lync Server 2013 : Mediation Server Configuration
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 1)
- Microsoft Access 2010 : Creating Your Own Databases and Tables - Working with Field Properties (part 4)
- Windows 7 : Using BitLocker Drive Encryption
- Sharepoint 2013 : Customizing a SharePoint Site - Create a Content Type
- Windows 8 : Using the Control Panel Items (part 11) - Speech Recognition
- Windows Phone 8 : Walking Through the Bookshop Sample Application (part 5) - Overview of the Sample Bookshop WCF Service
- Application Lifecycle Management in SharePoint 2013 : Getting Started with Application Lifecycle Management
- Windows 8 : Disks and Storage Devices - Drive Properties and Tools
 
 
Top 10
 
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 3)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 2)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Placing div Tags (part 1)
- Adobe Dreamweaver CS5 : Working with Divs and AP Elements - Divs and AP Elements 101
- Microsoft Project 2010 : Setting Up Project for Your Use - Defining Project Information (part 2) - Defining Project Properties
- Microsoft Project 2010 : Setting Up Project for Your Use - Defining Project Information (part 1) - Understanding the Project Information Dialog Box
- Microsoft Project 2010 : Setting Up Project for Your Use - Setting the Task Mode
- Securing an Exchange Server 2007 Environment : Securing Outlook Web Access
- Securing an Exchange Server 2007 Environment : Protecting Against Spam (part 2) - Filtering Junk Mail
- Securing an Exchange Server 2007 Environment : Protecting Against Spam (part 2) - Filtering Junk Mail