[AccessD] Find First in an Array?

Here are corrected DAO's .Seek code samples (sorry, previous version has had a couple of bugs):

Public Function testSeek1()
Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Products", dbOpenTable)
    With rst
       .Index = "ProductName"
       .Seek "=", "Aniseed Syrup"
        If (.NoMatch) Then
           Debug.Print "Not Found"
        Else
           Debug.Print "Found"
        End If
    End With
End Function

Public Function testSeek2()
'Prepequisites:
' Compound index named 'Contact' created
' for ContactTitle and ContactName fields
' of Customers table
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Dim avar As Variant
Dim i As Long
Dim rowCount As Long
Dim foundCount As Long
Dim notFoundCount As Long
Dim cyclesCount As Long
Dim k As Integer
    cyclesCount = 1000

    Set dbs = CurrentDb
    sql = "select ContactTitle, ContactName from Customers"
    Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
    rowCount = dbs.TableDefs("Customers").RecordCount
    avar = rst.GetRows(rowCount)
    Debug.Print "Started at = " & Now
    Debug.Print "Row count = " & rowCount
    foundCount = 0
    notFoundCount = 0
    Set rst = dbs.OpenRecordset("Customers", dbOpenTable)
    For k = 1 To cyclesCount Step 1
        For i = LBound(avar, 2) To UBound(avar, 2) Step 1
            With rst
               .Index = "Contact"
               .Seek "=", avar(0, i), avar(1, i)
                If (.NoMatch) Then
                   notFoundCount = notFoundCount + 1
                Else
                   foundCount = foundCount + 1
                End If
            End With
        Next i
    Next k
    rst.Close
    Set rst = Nothing
    Debug.Print "Found = " & foundCount
    Debug.Print "Not found = " & notFoundCount
    Debug.Print "Finished at = " & Now
'
' test results:
'Started at = 19.02.2009 13:54:18
'Row count = 91
'found = 91000
'Not found = 0
'Finished at = 19.02.2009 13:54:19
    
End Function


-----Original Message-----
From: Salakhetdinov Shamil <mcp2004 at mail.ru>
To: Access Developers discussion and problem solving<accessd at databaseadvisors.com>
Date: Thu, 19 Feb 2009 14:01:55 +0300
Subject: Re: [AccessD] Find First in an Array?

> 
> Hi Rocky,
> 
> Good old DAO's .Seek should do the trick.
> You'll have to create dedicated compound index for it.
> 
> --
> Shamil
> 
<<< tail trimmed>>>