The perfect customized DLookup (slow, Null problem) 2

[waldemar]

DLookup seems to be nice comfy function to access any database data quickly from anywhere.

However there remain two problems:

1) DLookups (and the DSum, etc. counterparts) are supposed to be slow. I keep reading this everywhere - dlookup is the slowest data access possible.... is this true?

2) Before getting data via a dlookup, the dlookup must be compared to IsNull to avoid the error of assigning a Null value to a variable or field. So every single DLookup ends up being two DLookups (One check for IsNull and Another One for the variable assignment if not(isnull())). I prepared a function like this to work around this problem:

Function secureDLookup(Expr As String, Domain As String, Optional Criteria As String) As String
If IsNull(DLookup(Expr, Domain, Criteria)) Then
secureDLookup = ""
Else
secureDLookup = DLookup(Expr, Domain, Criteria)
End If
End Function

Regarding the Speed problem, my question: Does it make sense to replace the DLookup inside the above function with an own VBA Recordset lookup (currentdb.OpenRecordSource, FindFirst, etc...) ??? Again I *read*, accessing data via recordsets is supposed to be the fastest access?

Related question: How fast is DoCmd.RunSql in comparison ??

Another related question: How do I *prepare* an access application for later use with an sql server? I guess this is a more complicated issue, isnt it?

Regards
waldemar

 

[LittleSmudge]

Yes waldemar DLookup is s l o w

As a general rule - if I need more than one result from a given table I'll open a recordset rather than use two DLookups - but for a single value then DLookup just pips a Recordset to the post ( for ease of use if nothing else. )

Caveat: If you just doing a couple of DLookups - the user won't notice anyway. It's the situation where you need two dozen fields extracted from a single record that wallops the perfomance !

As for your Null problem - I'm sure you'll not be surprised to find that there is an easier way.

I think your problem comes from the way you've worded
Before getting data via a dlookup, the dlookup must be compared to IsNull to avoid the error of assigning a Null value to a variable or field.

Actually
"When getting data via DLookup you must protect a variable that cannot accept Null from DLookup's ability to return Null"

So reading it this way means that we simple need the Nz( Value,NullValue) construct

secureDLookup = Nz(DLookup(Expr, Domain, Criteria),"&quot

Nz works by always returning the 'Value' unless Value = Null , whereupon Nz returns 'NullValue'

This approach, like your original, only makes sense if returning "" ( or any other standard default value ) makes sense to the place that you're returning it to.

Sometimes it makes more sense to allow the Variable = Null to cause an error and then trap the error with exception handling.


'ope-that-'elps.

G LS

 

[waldemar]

nz() rocks