Fastest/best way to look up a record by key?

  • Thread starter Thread starter MyndPhlyp
  • Start date Start date
M

MyndPhlyp

Assuming a unique value on an indexed column and ADODB.Recordsets (cursor =
adUseClient), what is the fastest or best way to retrieve a row - .Filter,
..Seek or .Find?

Does it matter if the table behind the Recordset is linked, a query or an
Access table?

Just wondering.
 
MyndPhlyp said:
Assuming a unique value on an indexed column and ADODB.Recordsets (cursor =
adUseClient), what is the fastest or best way to retrieve a row - .Filter,
.Seek or .Find?

Does it matter if the table behind the Recordset is linked, a query or an
Access table?

Just wondering.
Click to expand...

Except in certain instances, Seek can only be used on local Access tables.
Or: In order to use the Seek method, you must open the source database where
the linked table resides. You also must use dbOpenTable instead of
dbOpenDynaset, which also means you must use DAO, not ADO.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
 
Arvin Meyer said:
(cursor

Except in certain instances, Seek can only be used on local Access tables.
Or: In order to use the Seek method, you must open the source database where
the linked table resides. You also must use dbOpenTable instead of
dbOpenDynaset, which also means you must use DAO, not ADO.
Click to expand...

Well since .Seek requires me to open using dbOpenTable, thereby requiring me
to use DAO, that one is ruled out.

So, between .Filter and .Find, is there any performance difference and does
it matter if the Recordset is a linked table, a query or an Access table?

Still wandering ... er, wondering.
 
So, between .Filter and .Find, is there any performance difference
Click to expand...

Since .filter returns an entire recordset - all records that match the
filter criteria - I would guess that .find would usually be faster,
since it needs to load only one record into memory. No experimental
validation but it seems reasonable to me!
and does
it matter if the Recordset is a linked table, a query or an Access table?
Click to expand...

Of course it would matter. At a guess, table would be fastest; linked
table next; and query could be fast or slow or inbetween, depending on
the query.
Still wandering ... er, wondering.
Click to expand...

"All that is gold does not glitter,
Not all those who wander are lost..."
 
Back
Top