DLOOKUP vs Multiple Queries

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

What is the advantages/disadvantages to using dlookup to pull a value from
different tables into a query or joining the query to other queries to get
values form the other tables? Performance, speed, etc impacts?

I have queries where there are two or three sub queries to get the record
set I need and was wondering if it is just faster to do dlookups to get teh
values I need rather than running all ofthe sub queries and tabel joins to
find the data.

Thanks

-joe
 
DLookups in queries are extremely slow, and usually much slower than using
subqueries.
 
Although slow, DLookup()s may create an updateable recordset while
subqueries may not. Also, crosstabs might work better with DLookup()s while
erroring with subqueries. If you can use queries only, then avoid
DLookup()s.
 
Thanks for the added info, Duane.

Just to add to this, if a subquery causes a problem with updateability and
DLookup is too slow, I often create a temp table to hold the subquery's
results and then use that temp table as a source table for the query.
 
Good point Ken. I often have resorted to the same for performance reasons.
This usually requires a primary or unique index on the temporary table.
 
Back
Top