[AccessD] Find First in an Array?

Hi Rocky,

Gustav have answered your question already: compound index on the table level consisting of your two search fields. The fields should be defined in the index in the sequence their values are used in the .Seek method in the code.

As you can see from sample code 91,000 .Seek calls are executed within a second on my test Dual Core Pentium, which is a mainstream PC now eveywhere? Tested with MS Access 2007 (but .mdb) and MS Windows Vista Ultimate.

--
Shamil

-----Original Message-----
From: "Rocky Smolin at Beach Access Software" <rockysmolin at bchacc.com>
To: "'Access Developers discussion and problem solving'"<accessd at databaseadvisors.com>
Date: Thu, 19 Feb 2009 06:38:11 -0800
Subject: Re: [AccessD] Find First in an Array?

> 
> Shamil!  I forgot all about Seek.  This is one of those rare occasions where
> I'm working with a non-linked table. But this table is in the front end -
> local to the  user - so each user can have their own language. 
> 
> I would have to seek on two fields however - the form name and the control
> name.  Should I make a concatenated field, then?
> 
> Best, 
> 
> 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>  
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Salakhetdinov
> Shamil
> Sent: Thursday, February 19, 2009 3:33 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [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>>>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>