dlookup vs recordset Efficiency

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Apologies if this posts twice, the first time IE crashed!

At what point is it more efficient to use a recordset to retreive data from
a table than to use the DLookup function?

E.g To get a single value from Table A, I would expect the function to be
the better choice. To get two seperate values from Table A, I would still
expect the funciton to be the better choice, but at what point does the
balance tip in favour of the recordset (or does it?)

Many Thanks
Chris
 
I don't believe there's any hard-and-fast rule.

DLookup doesn't take advantage of indexes, so it's always going to be less
efficient than a recordset. You're correct that if you're getting a single
value, you probably won't notice the difference. On the other hand, if
you've got a large loop that's doing the lookup, you may well see the
difference.

BTW, you can actually use DLookup to return more than one value.

varReturned = DLookup("[Field1] & ';' & [Field2] & ';' & [Field3]",
"Table", "WHERE ID = 5")

will return 3 values, each separated by a semi-colon.

Assuming you're using Access 2000 or newer, you can use the Split function
to get the individual values:

strField1 = Split(varReturned, ";")(0)
strField2 = Split(varReturned, ";")(1)
strField3 = Split(varReturned, ";")(2)

or, to save having 3 separate calls to the Split function:

Dim varValues As Variant

varValues = Split(varReturned, ";")
strField1 = varValues(0)
strField2 = varValues(1)
strField3 = varValues(2)
 
So you're saying that after however many years of using DLookups() to
grab a single value....
 
Thanks Doug

That is useful to know (I'll have to investigate the Split function more!)

Chris

Douglas J Steele said:
I don't believe there's any hard-and-fast rule.

DLookup doesn't take advantage of indexes, so it's always going to be less
efficient than a recordset. You're correct that if you're getting a single
value, you probably won't notice the difference. On the other hand, if
you've got a large loop that's doing the lookup, you may well see the
difference.

BTW, you can actually use DLookup to return more than one value.

varReturned = DLookup("[Field1] & ';' & [Field2] & ';' & [Field3]",
"Table", "WHERE ID = 5")

will return 3 values, each separated by a semi-colon.

Assuming you're using Access 2000 or newer, you can use the Split function
to get the individual values:

strField1 = Split(varReturned, ";")(0)
strField2 = Split(varReturned, ";")(1)
strField3 = Split(varReturned, ";")(2)

or, to save having 3 separate calls to the Split function:

Dim varValues As Variant

varValues = Split(varReturned, ";")
strField1 = varValues(0)
strField2 = varValues(1)
strField3 = varValues(2)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Chris said:
Apologies if this posts twice, the first time IE crashed!

At what point is it more efficient to use a recordset to retreive data from
a table than to use the DLookup function?

E.g To get a single value from Table A, I would expect the function to be
the better choice. To get two seperate values from Table A, I would still
expect the funciton to be the better choice, but at what point does the
balance tip in favour of the recordset (or does it?)

Many Thanks
Chris
Click to expand...
Click to expand...
 
Douglas said:
I don't believe there's any hard-and-fast rule.

DLookup doesn't take advantage of indexes, so it's always going to be
less efficient than a recordset.
Click to expand...

Old wive's tale. The domain aggregate functions most certainly do use
indexes. A simple test against a large table will show this.

The inefficiencies of using the domain aggregates are because they do the
equivelent of using CurrentDB which causes a refresh of all of the
collections in the db. So if you replace them with a Recordset that ALSO
uses CurrentDB you gain nothing. So both domain functions and custom
functions that instantiate a database object are inefficient when used in
queries or looping operations because of all of the database object
creations and collection refreshing, not because of the data retrieval
speed.

However; multiple domain functions can be replaced with custom Recordset
functions that all share a common db object reference and thus gain some
efficiency by doing so.

If you are not doing a looping operation or using them in a query there is
no performance reason NOT to use a domain function that I am aware of.
 
Back
Top