Seek Method and Index Property Example (VB)

This example uses the Recordset object's Seek method and Index property in conjunction with a given Employee ID, to locate the employee's name in the Employees table of the Nwind.mdb database.

'BeginSeekVB
Public Sub SeekX()
Dim rst As ADODB.Recordset
Dim strID As String
Dim strPrompt As String
strPrompt = "Enter an EmployeeID (e.g., 0 to 9)"

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open "employees", _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=E:\Program Files\Microsoft Office\Office\Samples\northwind.mdb;" & _
            "user id=admin;password=;", _
            adOpenKeyset, adLockReadOnly, adCmdTableDirect

' Does this provider support Seek and Index?
If rst.Supports(adIndex) And rst.Supports(adSeek) Then
    rst.Index = "PrimaryKey"
    ' Display all the employees.
        rst.MoveFirst
        Do While rst.EOF = False
            Debug.Print rst!EmployeeId; ": "; rst!firstname; " "; _
                rst!LastName
            rst.MoveNext
        Loop
    
' Prompt the user for an EmployeeID between 0 and 9.
      rst.MoveFirst
      Do
         strID = LCase(Trim(InputBox(strPrompt, "Seek Example")))
         ' Quit if strID is a zero-length string (CANCEL, null, etc.)
         If Len(strID) = 0 Then Exit Do
         If Len(strID) = 1 And strID >= "0" And strID <= "9" Then
            rst.Seek Array(strID), adSeekFirstEQ
            If rst.EOF Then
               Debug.Print "Employee not found."
            Else
               Debug.Print strID; ": Employee='"; rst!firstname; " "; _
               rst!LastName; "'"
            End If
         End If
      Loop
End If

rst.Close
End Sub
'EndSeekVB

Seek Method and Index Property Example (VB)

See Also