Quick Lookup Options

G

Gary Dolliver

Hi all,
I am looking to update a record in a table, but before the update is done, I
want to check the record to make sure certain criteria are met before it
updates. Is my only option using a DLookup? I ask as this has some lag time
going through the process of looking up the record.

I am using:

If DLookup("Field1", "Table1", "Field2=" & [Field2]) = 29 then
Do something
Else
Do something else
End if

Will a GoTo command work quicker or even be an option?
Thanks!
-gary
 
M

mscertified

DLookup tends to be quite slow.
Its faster to open a recordset.
Are the columns you are searching on indexed?

Dorian
 
G

Gary Dolliver

Hello, thanks for the reply! They are currently not indexed, but easily can
be (if needed) How would the recordset work and what effects will it have if
these tables eventually start going above 50,000 plus records? Compared to a
DLookup?
Thanks!
-gary

mscertified said:
DLookup tends to be quite slow.
Its faster to open a recordset.
Are the columns you are searching on indexed?

Dorian

Gary Dolliver said:
Hi all,
I am looking to update a record in a table, but before the update is done, I
want to check the record to make sure certain criteria are met before it
updates. Is my only option using a DLookup? I ask as this has some lag time
going through the process of looking up the record.

I am using:

If DLookup("Field1", "Table1", "Field2=" & [Field2]) = 29 then
Do something
Else
Do something else
End if

Will a GoTo command work quicker or even be an option?
Thanks!
-gary
Click to expand...
Click to expand...
 
R

Rick Brandt

Gary said:
Hello, thanks for the reply! They are currently not indexed, but
easily can be (if needed) How would the recordset work and what
effects will it have if these tables eventually start going above
50,000 plus records? Compared to a DLookup?
Thanks!
Click to expand...

It is a myth that in a single call like you have that DLookup() would be slower
than a Recordset. Because of built in overhead the Domain functions like
DLookup() are not good to use in Queries and looping operations. For a one-shot
lookup like you need a Recordset will incur the same overhead and therefore will
be no faster.
 
G

Gary Dolliver

Hi Rick,
Thank you so much for the information. Would there be any difference in
regards to how large the table gets and how quickly I want to submit an
update? I ask as this table will contiously be getting larger and larger,
and I will be using a barcode scanner to insert the record, close to 2 clicks
a second.
Please let me know, thanks!
-gary
 
R

Rick Brandt

Gary said:
Hi Rick,
Thank you so much for the information. Would there be any difference
in regards to how large the table gets and how quickly I want to
submit an update? I ask as this table will contiously be getting
larger and larger, and I will be using a barcode scanner to insert
the record, close to 2 clicks a second.
Please let me know, thanks!
-gary
Click to expand...

If you have the field that is used for criteria properly indexed then the size
of the table will really make very little difference.
 
G

Gary Dolliver

Awesome! How about the speed of the bar code scanner inserting a new record,
could this cause any complications?
Thanks!
-gary
 
Top