MDAC 2.5 SDK - ADO

BOF, EOF, and Bookmark Properties Example (VB)

See Also

This example uses the BOF and EOF properties to display a message if a user tries to move past the first or last record of a Recordset. It uses the Bookmark property to let the user flag a record in a Recordset and return to it later.

Public Sub BOFX()

   Dim rstPublishers As ADODB.Recordset
   Dim strCnn As String
   Dim strMessage As String
   Dim intCommand As Integer
   Dim varBookmark As Variant

   ' Open recordset with data from Publishers table.
   strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set rstPublishers = New ADODB.Recordset
   rstPublishers.CursorType = adOpenStatic
   ' Use client cursor to enable AbsolutePosition property.
   rstPublishers.CursorLocation = adUseClient
   rstPublishers.Open "SELECT pub_id, pub_name FROM publishers " & _
      "ORDER BY pub_name", strCnn, , , adCmdText

   rstPublishers.MoveFirst

   Do While True
      ' Display information about current record
      ' and get user input.
      strMessage = "Publisher: " & rstPublishers!pub_name & _
         vbCr & "(record " & rstPublishers.AbsolutePosition & _
         " of " & rstPublishers.RecordCount & ")" & vbCr & vbCr & _
         "Enter command:" & vbCr & _
         "[1 - next / 2 - previous /" & vbCr & _
         "3 - set bookmark / 4 - go to bookmark]"
      intCommand = Val(InputBox(strMessage))

      Select Case intCommand
         ' Move forward or backward, trapping for BOF
         ' or EOF.
         Case 1
            rstPublishers.MoveNext
            If rstPublishers.EOF Then
               MsgBox "Moving past the last record." & _
                  vbCr & "Try again."
               rstPublishers.MoveLast
            End If
         Case 2
            rstPublishers.MovePrevious
            If rstPublishers.BOF Then
               MsgBox "Moving past the first record." & _
                  vbCr & "Try again."
               rstPublishers.MoveFirst
            End If

         ' Store the bookmark of the current record.
         Case 3
            varBookmark = rstPublishers.Bookmark

         ' Go to the record indicated by the stored
         ' bookmark.
         Case 4
            If IsEmpty(varBookmark) Then
               MsgBox "No Bookmark set!"
            Else
               rstPublishers.Bookmark = varBookmark
            End If

         Case Else
            Exit Do
      End Select

   Loop

   rstPublishers.Close

End Sub

This example uses the Bookmark and Filter properties to create a limited view of the Recordset. Only records referenced by the array of bookmarks are accessible.

Public Sub BOFX2()

Dim rs As New ADODB.Recordset
Dim bmk(10)

rs.CursorLocation = adUseClient
rs.ActiveConnection = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=;" 

rs.Open "select * from Authors", , adOpenStatic, adLockBatchOptimistic
Debug.Print "Number of records before filtering: ", rs.RecordCount

ii = 0
While rs.EOF <> True And ii < 11
    bmk(ii) = rs.Bookmark
    ii = ii + 1
    rs.Move 2
Wend
rs.Filter = bmk
Debug.Print "Number of records after filtering: ", rs.RecordCount

rs.MoveFirst
While rs.EOF <> True
    Debug.Print rs.AbsolutePosition, rs("au_lname")
    rs.MoveNext
Wend
    
End Sub