Andibevan said:
I have been told that using Dlookup is terribly inefficient and as a result
I am trying to replace all instances of Dlookup in my VBA codewith SQL.
Old wive's tale. DLookup when using an idexed field in the WHERE clause is not
inherantly less efficient than most other methods you might use. Since they
perform the equivelant of instantiating a database object and a refresh of its
collections they are not adviseable in queries and loops because that overhead
is repeated each and every time the function is called. However as long as you
are not in a query or a loop then the Domain Aggregate functions are fine.
A Recordset is a better choice over DLookup() when you need multiple fields for
the same reason. A single Recordset can grab any number of fields from the
desired row whereas you would need a Dlookup() per field to do the same. Again,
this would incur processing overhead for each function call that would only be
incurred once for the Recordset.
How would I use an SQL SELECT statemenet in VBA to lookup a specific value
from a table?
Dim db as Database
Dim sql as String
Dim rs as Recordset
Dim myVar as Variant
Set db = CurrentDB
sql = "SELECT SomeField FROM SomeTable WHERE SomeCriteria"
Set rs = db.OpenRecordSet(sql, dbOpenSnapshot)
If rs.eof = False Then myVar = rs!SomeField
Notice these two lines...
Dim db as Database
Set db = CurrentDB
Those two lines cause the same processing overhead as a DLookup() call. Now if
you can use that db object to make *multiple* Recordsets and/or use the
Recordsets to retrieve *multiple* values then you are saving some resources. If
you simply do what I have above and that is it, then you have saved nothing
except perhaps the ability to make a complicated WHERE clause easier to read in
your code.
