Cursors in MS Access and ADO

Introduction

This tutorial looks at the cursors, elements that allow the viewing of data in specialized ways. Cursors define the functionality of a specific recordset. ADO reaches out to the data through OleDB object -- the recordset, to get the data stored in a data structure called the cursor. The ADO Cursor engine sits between ADO and the OleDB.

Different cursors endow different functionalities to the recordset. Some cursors allow you to see data in real time (data changes as they are being made when you are working with the recordset), some allow only a "snapshot" of the data existing at a particular point in time. Also while some may allow you to move through the data up and down (or left and right), some others lets you do so only in the forward direction. In ADO there are four types of cursors. This tutorial shows how the four cursors define the functionality of the recordset when Recordset's cursor property is predefined.

This tutorial assumes that the reader has reviewed the first two tutorials on ADO's Connection object and the Recordset object which have appeared on the ASPFree.com site. Only a few of the properties of the recordset object were described. In the present tutorial you will see that a knowledge of cursors is essential to understanding the properties related to navigation in the data, the updatability of data and other issues. Complete examples will be given that may function as a ready reference for understanding the cursors. Although only one of the cursor types (adOpenStatic) is described using coding in detail, future tutorials will describe the others in similar detail.

If you look at the object browser for the recordset you will see two properties associated with cursor. One is the CursorLocation property and the other is the CursorType property. In part two of this series the default values of these were shown to be CursorLocation(2) is adUseServer and the CursorType(0) is adOpenKeyset.

As far as the Cursor Location property is concerned there are just two usable properties, the Client-side cursors and Server-side cursors denoted by adUseClient and adUseServer respectively. These enumerated constants also have values associated with them, adUseServer:= 2 and adUseClient:= 3. These determine how the retrieved results of your query are stored. The default setting adUseServer can be changed to client-side if you want. In addition to these two properties, two other properties of the recordset, the LockType and CacheSize, also influence the functionality of the cursors. These will not be discussed in the present tutorial.

In the case of Client-side cursors, the results of the entire query get stored to the ADO Cursor engine. In the case of Server-side cursors either the OleDB provider or the database manages the results, with ADO storing a small subset of the result. It is important to realize that the data in a client-side cursor is inherently disconnected from the database, but stored in the ADO Cursor Engine, it is a copy of the data when it was retrieved. Any changes to the recordset are applied as an action query through the OleDB provider, the intermediary between ADO and the Data.

The ADO 2.8 API library defines the various cursors as follows ( a reproduction of information from Microsoft's documentation on MSDN):

The Recordset's RecordCount, AbsolutePosition, BOF and EOF properties

In order to explore the properties of the recordset, the RecordCount, AbsolutePosition, BOF, and EOF, the code shown may be used to visualize what these properties mean as related to a recordset. In the code presented, the cursor type property is set to adOpenStatic which means you can go forward (use commands like MoveNext) and backward (use commands like Move Previous).

The picture shown next depicts the relative positions of some of the properties. The data can be viewed in a forward or backward direction. When the recordset opens, its Absolute position is 1 as shown. If you go backward you will get into the BOF area. Similarly if you go to the last record and try to go the next position you will be in the EOF area. The BOF and EOF properties give you the hooks to determine whether you have strayed into these areas. The RecordCount property shows you how many records there are in the query result.

What is achieved in the code is opening a recordset with a cursor that supports the bidirectional movement, in this example, adOpenStatic. After opening the recordset the AbsolutePosition, BOF and EOF properties of the recordset are evaluated as the recordset is moved to different locations using MovePrevious, MoveNext, and MoveLast properties of the recordset. The results are gathered in a string and dumped to a textbox as shown in the picture. The AbsolutePosition when the BOF is true, and when EOF is true are negative numbers. It does not mean they jumped to these values. They actually represent enumerated constants adPosBOF and adPosEof. These are not frequently used.

Option Compare Database
Private conn As ADODB.Connection
Private strsql As String
Private rst3 As ADODB.Recordset
Private Sub Form_Load()
Dim strg As String
strsql = "Select EmployeeID, LastName, FirstName, City from Employees"
strg = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program Files

Microsoft OfficeOFFICE11SAMPLESNorthwind.mdb;Persist Security Info=



False"
'CursorType=3 adOpenStatic
Set conn = New ADODB.Connection
Set rst3 = New ADODB.Recordset
conn.Open strg
Set rst3.ActiveConnection = conn
rst3.CursorType = 3
rst3.Open strsql, strg
'MsgBox (rst.CursorType)
Dim strgs As String
strgs = ""
strgs = strgs + "Cursor adOpenStatic" & vbCrLf
strgs = strgs + "Number of rows (RecordCount) is: " & rst3.RecordCount & 

vbCrLf
strgs = strgs + "Number of columns(Fields) is: " & rst3.Fields.Count & 

vbCrLf
strgs = strgs + "----------------------------------------------------" & 

vbCrLf
strgs = strgs + "This is the starting position of the recordset" & vbCrLf
strgs = strgs + "----------------------------------------------------" & 

vbCrLf
strgs = strgs + "Is it beginning of file(BOF) :  " & rst3.BOF & vbCrLf
strgs = strgs + "Is it end of file(EOF) :  " & rst3.EOF & vbCrLf
strgs = strgs + "What is the absolute position of record? :  " & 

rst3.AbsolutePosition & vbCrLf
'
'
'
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
strgs = strgs + "MOVE the Recordset to its PREVIOUS position" & vbCrLf
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
rst3.MovePrevious
strgs = strgs + "Is it beginning of file(BOF) :  " & rst3.BOF & vbCrLf
strgs = strgs + "Is it end of file(EOF) :  " & rst3.EOF & vbCrLf
strgs = strgs + "What is the absolute position of record? :  " & 

rst3.AbsolutePosition & vbCrLf
'
'Move to the last position and find BOF, EOF, and AbsolutePosition
'
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
strgs = strgs + "MOVE the Recordset to its LAST position" & vbCrLf
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
rst3.MoveLast
strgs = strgs + "Is it beginning of file(BOF) :  " & rst3.BOF & vbCrLf
strgs = strgs + "Is it end of file(EOF) :  " & rst3.EOF & vbCrLf
strgs = strgs + "What is the absolute position of record? :  " & 

rst3.AbsolutePosition & vbCrLf
'
'Move next to last position and find BOF, EOF, and absolute Position
'
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
strgs = strgs + "Now MOVE the recordset to the NEXT position" & vbCrLf
strgs = strgs + "-----------------------------------------------------" & 

vbCrLf
rst3.MoveNext
strgs = strgs + "Is it beginning of file(BOF) :  " & rst3.BOF & vbCrLf
strgs = strgs + "Is it end of file(EOF) :  " & rst3.EOF & vbCrLf
strgs = strgs + "What is the absolute position of record? :  " & 

rst3.AbsolutePosition & vbCrLf
'
Text0.SetFocus
Text0.Text = strgs
Set rst3 = Nothing
Set conn = Nothing
End Sub

CursorType changed to adOpenForwardOnly

If you change the CursorType property in the above code from being adOpenStatic to adOpenForwardOnly and try to run the application, you will end up with a run time error because going back is not permitted and the first time you try to do so (using MovePrevious), an error will be generated and you will get the following message:

Now if you try to debug, you will be guided to the highlighted line as shown.

In this tutorial we have seen some of the basic features of ADO's cursors as applied to data retrieved using a simple select statement on a table in the Northwind sample database. Not all OleDB providers provide all cursor types. This brings us to another important property not discussed, namely Recordset's Supports property which takes the property or method as an argument. Most importantly we have seen the different kinds of cursors that are used and two simple examples of how they change the functionality of retrieved data.

Record navigation was discussed in the context of cursors, although in addition to MovePrevious, MoveLast, and MoveNext there are also MoveFirst and Move (which takes two optional arguments, how many steps to move and starting from where). We will look at record navigation when bound to a form in another tutorial. When data changes are to be made using action queries the cursor location property becomes very important. The LockType and CacheSize properties were not discussed, but that will be rectified in a future tutorial.