HandsOn Finding Records Using the Seek Method

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Find_WithSeek procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub Find_WithSeek()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Set rst = New ADODB.Recordset With rst

.Index = "Region"

.Open "Customers", conn, adOpenKeyset, adLockOptimistic, _ adCmdTableDirect

Creating and Manipulating Databases with ADO

' find out if this recordset supports the Seek method MsgBox rst.Supports(adSeek) .Seek "SP", adSeekFirstEQ End With

If Not rst.EOF Then

Debug.Print rst.Fields("CompanyName").Value End If rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

If the Seek method is based on a multi-field index, use the VBA Array function to specify values for the KeyValues parameter. For example, the Order Details table in the Northwind database uses a multi-field index as the PrimaryKey. This index is a combination of the OrderId and ProductId fields. To find the order in which OrderId = 10295 and ProductId = 56, use the following statement:

rst.Seek Array(10295, 56), adSeekFirstEQ

0 0

Post a comment