Submitted By: Kent Finkle

Sample script that demonstrates the use of the CompareBookmarks method in an ADO database script.

Visual Basic
Edit|Remove
'* Script name: CompareBookmarks.vbs 
'* Created on: 2008-01-10 
'* Author: Kent Finkle 
'* Purpose: Demonstrates the CompareBookmarks method 
'* http://msdn2.microsoft.com/en-us/library/ms681526(VS.85).aspx 

Main 

Sub Main() 
     ' recordset and connection variables 
    Dim rstCustomers 
    Dim objConnection 
    Dim strSQLCustomers 
    Dim strCnxn 
    
     ' comparison variables 
    Dim count 
    Dim target 
    Dim result 
    Dim strAnswer 
    Dim strTitle 
    
    Const adOpenStatic = 3 
    Const adLockReadOnly = 1 
    Const adCmdText = 1 
    Const adBookmarkFirst = 1 
    Const adCompareNotEqual = 3 
    Const adCompareNotComparable = 4 
    Const adCompareLessThan = 0 
    Const adCompareEqual = 1 
    Const adCompareGreaterThan = 2 
    
    strTitle = "CompareBookmarks Example" 
    
    ' Open a connection 
    Set objConnection = CreateObject("ADODB.Connection") 
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
        "Data Source=C:\Scripts\Northwind.mdb" 

     ' Open recordset as a static cursor type recordset 
    Set rstCustomers = CreateObject("ADODB.Recordset") 
    
    strSQLCustomers = "SELECT * FROM Customers" 
    
    rstCustomers.Open strSQLCustomers, _ 
        objConnection, adOpenStatic, adLockReadOnly, adCmdText 
    
    count = rstCustomers.RecordCount 
    
    Output "Rows in the Recordset = " & count 
    
     ' Exit if an empty recordset 
    If count = 0 Then Exit Sub 
    
     ' Get position between 0 and count -1 
    Randomize 
    count = (Int(count * Rnd)) 
    Output "Randomly chosen row position = " & count 
     ' Move row to random position 
    rstCustomers.Move count, adBookmarkFirst 
     ' Remember the mystery row 
    target = rstCustomers.Bookmark 
    
    count = 0 
    rstCustomers.MoveFirst 
         ' Loop through recordset 
    Do Until rstCustomers.EOF 
       result = rstCustomers.CompareBookmarks( _ 
           rstCustomers.Bookmark, target) 
       
       If result = adCompareNotEqual Then 
          Output "Row " & count _ 
              & ": Bookmarks are not equal." 

       ElseIf result = adCompareNotComparable Then 
          Output "Row " & count _ 
              & ": Bookmarks are not comparable." 
       Else 
          Select Case result 
             Case adCompareLessThan 
                strAnswer = "less than" 
             Case adCompareEqual 
                strAnswer = "equal to" 
             Case adCompareGreaterThan 
                strAnswer = "greater than" 
             Case Else 
                strAnswer = "in error comparing to" 
          End Select 
            'show the results row-by-row 
          Output "Row position " _ 
              & count & " is " & strAnswer & " the target." 
       End If 
       
       count = count + 1 
       rstCustomers.MoveNext 
    Loop 
    
    ' clean up 
    rstCustomers.Close 
    objConnection.Close 
    Set rstCustomers = Nothing 
    Set objConnection = Nothing 
    
End Sub 

Sub Output(msg) 
    Wscript.Echo msg 
End Sub