DLookup vs. Recordset?

D

DJ

I was debating the use of 2 DLookup functions (Embedded probably) versus a
Recordset query to retrieve information from a database with two tables.
Speed is my main concern. Can anyone please tell me the pros and cons to
the use of each?

Thanks,

DJ
 
D

Dirk Goldgar

DJ said:
I was debating the use of 2 DLookup functions (Embedded probably)
versus a Recordset query to retrieve information from a database with
two tables. Speed is my main concern. Can anyone please tell me the
pros and cons to the use of each?
Click to expand...

Given that DLookup opens a recordset "under the covers", there's not a
whole lot to choose between a single DLookup and opening your own
recordset to find one value. But if you can replace two DLookups with a
single OpenRecordset (by specifying a suitable SQL statement for the
recordset), then the recordset will naturally be faster. If you're
concerned with extremely minor performance differences, then the
recordset approach would probably be a teensy bit faster even for
looking up a single value, but it's probably not worth it unless you're
doing it in a tight loop. And if you're performing the operation over
and over again in a loop, then by setting your database reference
outside the loop and using OpenRecordset inside it, you'll probably get
much better performance than DLookup.
 
R

Rick Brandt

DJ said:
I was debating the use of 2 DLookup functions (Embedded probably)
versus a Recordset query to retrieve information from a database with
two tables. Speed is my main concern. Can anyone please tell me the
pros and cons to the use of each?

Thanks,

DJ
Click to expand...

Depends on what you mean by Recordsets and how you implement their use. One of
the resource drains from a DLookup is the instantiation of a database object
with an associated refresh of its collections. When you use one DLookup this is
no problem because you would usually do the same with a Recordset as well.

When you need to perform more than one lookup though you can save resources by
using one database object to create multiple Recordsets whereas multiple
DLookups are going to each instantiate their own database object. So in your
case two Recordsets sharing a single database object will be more efficient than
two Dlookups. Will you be able to tell the difference? Probably not. However
as the number of lookups increases the advantage to using a Recordset increases,
BUT...only if you share a database instance. If you just replace 6 DLookups
with 6 calls to a user defined function that instatiates and then destroys its
objects you gain nothing.
 
Top