DLookup alternative

[willsth]

I have performance issues with my Access 2007 database running over a network with the FE and BE split. I have looked at various posts and I am implementing various suggestions. One of the problems highlighted is using DLookup.

Does anyone have a procedure to use instead of DLookup ??

 

[RuralGuy]

It really depends on how you are using DLookUp() as to performance in any version of Access. Posting the code that uses the DLookUp() would help with giving suggestions. A persistant connections can sometimes help with a split application.

http://www.granite.ab.ca/access/performancefaq.htm

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[F2F4]

Hi willsth
I've found dlookups to be slow in general when large datasets are involved. They are great for small tables. If it's important to use dlookup it sometimes helps to index the table for the field you are using in the where part of your lookup. However the better solution is to create a qrydef with a temporary recordset to find the record matching the criteria you specify. Then do what you like with the field in this recordset you want to access.

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim vJobSiteID As Long 'criteria value

Set db = CurrentDb()
vJobSiteID = OpenArgs (how you get your criteria value is your choice)
strSQL = "SELECT tblJobDetails.* "
strSQL = strSQL & "FROM tblJobDetails "
strSQL = strSQL & "WHERE (((tblJobDetails.JobsiteID)=" & vJobSiteID & "));"
Set qdf = db.CreateQueryDef("", strSQL)
Set rs1 = qdf.OpenRecordset(dbOpenDynaset)
If Not rs1.EOF Then
'your action here Eg. Forms!frmYourForm!YourField=rs1!FieldName
End If

rs1.Close
db.Close

Hope this helps

F2F4

 

[SeeThru]

Hi F2F4

Out of interest, is your method faster/more efficient than using:

Set db = CurrentDb()
vJobSiteID = OpenArgs (how you get your criteria value is your choice)
strSQL = "SELECT tblJobDetails.* "
strSQL = strSQL & "FROM tblJobDetails "
strSQL = strSQL & "WHERE (((tblJobDetails.JobsiteID)=" & vJobSiteID & "));"
Set rs1 = qdf.OpenRecordset(strSQL)
If Not rs1.EOF Then
 'your action here  Eg. Forms!frmYourForm!YourField=rs1!FieldName
End If

rs1.Close
db.Close

i.e. just opening a recordset.

SeeThru
Synergy Connections Ltd - Telemarketing Services

 

[willsth]

I have created a persistent connection and that has improved things, and I'm looking if its really necessary to use DLookUp. I'm using the following bit of code :-
=DLookUp("[Total Billables]","Projects Extended","[ProjectID]=" & Nz([ID],0))

How would your code look instead ??

 

[RuralGuy]

Is your code the ControlSource of a control on a form?

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[willsth]

Yes

 

[RuralGuy]

If the value is not changed by actions on the form then try putting your DLookup() in the OnLoad event of the form or maybe the OnCurrent event depending on what you are doing.

RuralGuy (RG for short) aka Allan Bunch MS Access MVP acXP winXP Pro
Please respond to this forum so all may benefit

 

[PHV]

Another way is to bind your form with a query instead of a table.

http://www.devshed.com/c/a/MySQL/Understanding-SQL-Joins

http://support.microsoft.com/kb/148581

Yet another way is to use a ComboBox for the ID control.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[willsth]

Thanks for your help