I understand that DLookup function can affect DB performance when the DB gets large.

Would one recommend to always use SQL satement and avoid DLookup function totally when it is possible to do so?

Hi Stoneman,

Some folks opt never to use any of the aggregate functions in their apps, which is probably a good idea.

They do consume a lot of resources and they won't upsize if you
decide to move to a different backend.

All of the aggregate functions can be written as SQL statments:

DCount = SELECT Count()
DMax = SELECT Max()
DLookup = SELECT
DSum = SELECT Sum()

Also, here's a simple function I picked up along the way that can
be used in place of any of the aggregate functions:


Public Function CLookup(strSQL As String) As Variant
Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open strSQL, CurrentProject.Connection, adOpenStatic
If rst.BOF And rst.EOF Then
    CLookup = Null
Else
    CLookup = rst(0)
End If

rst.Close
Set rst = Nothing

End Function


Instead of calling:

DLookup("fld1", "tblOne", "ID = 7")

you call:

CLookup("SELECT fld1 FROM tblOne WHERE ID = 7")


Its faster and upgradeable.


HTH,

Bob

Guess I should have said domain aggregate functions. Just gettin' lazy . Don't want to create any confusion though.