Cursors in MS Access and ADO - The Recordset's RecordCount, AbsolutePosition, BOF and EOF properties

(Page 3 of 4 )

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

Next: CursorType changed to adOpenForwardOnly >>
 

More Microsoft Access Articles
More By Jayaram Krishnaswamy

Comments On: Cursors in MS Access and ADO

· The tutorial is written targeting the beginner and so the discussion is at its very...    · These are excellent articles and I am enjoying reading them. We all have to start...   >>> Post your comment now!