faster option for dlookup?

  • Thread starter Thread starter Cynthia N.
  • Start date Start date
C

Cynthia N.

Hi,

Is there a better option other than dlookup? I'm using
it to send variables to a global but its really S L O W!

any help would be great!

thanks!

Cynthia.
 
Do you have an index on the field that you're using as the "WHERE" part of
the DLookup function? If not, put an index on that field and the DLookup
will run faster.

Otherwise, you can create and open your own recordset via VBA code and get
the value that way. As to whether it will be faster, that will depend upon
what you're doing. If you want to try this option, post back with more
details about what you're doing now.
 
Depends on the context where you are using this.

Would it be possible to use a query with a join instead of one table with a
DLookup() ?

If you can live with a read-only result (e.g. if this is for a report), a
subquery may be the best approach.

If this is in code, could you open a recordset and reuse it (by finding the
desired record) rather than call DLookup() multiple times?

If all else fails, there is a replacement for DLookup() in this link:
http://allenbrowne.com/ser-42.html
It's about twice the speed of DLookup(), but that is still slow if you are
calling it repeatedly.
 
Thanks for the info... here's some more info

im have an indexed user table to establish rights on
buttons and commands (this is and 2002 upgraded from
Access 97) when the user logs in, the name is refereced
and the user rights are sent to global variables, so on
the form load the commands are enabled/disabled, and
specifics for that user are loaded... there are several
rights, and they are only loaded once... the user table
is the backend on the network.. the lag takes about 10
seconds... but the users insist its about an hour!

thanks for any help

cyn.
 
Back
Top