dlookup vs recordset Efficiency

[Chris]

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

 

[Douglas J Steele]

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)

 

[David C. Holley]

So you're saying that after however many years of using DLookups() to
grab a single value....

 

[Chris]

Thanks Doug

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

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)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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

 

[Rick Brandt]

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.

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.