Quick Lookup Options

[Guest]

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

 

[Guest]

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

Dorian

 

[Guest]

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

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

Dorian

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

 

[Rick Brandt]

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!

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.

 

[Guest]

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

 

[Rick Brandt]

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

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

 

[Guest]

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