Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 3rd, 2003, 08:12 AM
Authorized User
 
Join Date: Jun 2003
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default Dllokup function

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?
 
Old October 6th, 2003, 01:49 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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

 
Old October 6th, 2003, 07:18 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Code:
Some folks opt never to use any of the aggregate functions in their apps, which is probably a good idea.
Guess I should have said domain aggregate functions. Just gettin' lazy . Don't want to create any confusion though.







Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use Function akumarp2p SQL Server 2000 1 May 28th, 2007 05:04 AM
send variable in function to another function schoolBoy Javascript How-To 6 March 3rd, 2007 09:16 AM
Function monika.vasvani ASP.NET 1.0 and 1.1 Professional 3 December 5th, 2006 03:11 AM
How to call javascript function from VB function vinod_yadav1919 VB How-To 0 February 13th, 2006 06:03 AM
retreive function/Line from macro or function? MikoMax J2EE 0 April 1st, 2004 04:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.