Improve Dlookup Speed (ACC 2002)

Topic

New Reply

I have splitted up my time clock program into a FE BE and it works fine with one exception the forms are very slow and I’m going to have to blame the dlookup function. OI would like to know if there is a way to spped up the lookup on my form by replacing dlookup with a recordset call. However I’m not sure how to go about it.
The following line of code is on the form:
If IsNull(DLookup(“[AssID]”, “tblAssociateInfo”, “[AssID]= ” & Me.txtAssID.Value & “”)) Then
Me.chkID.Value = Me.chkID.Value + 1
If Me.chkID.Value > 3 Then
MsgBox “You may not make more than three User ID entry attempts.”, vbCritical + vbOKOnly, “Security Violation!”
DoCmd.Quit
End If
My goal is to look up an id and if it exsist the let the user continue, if it doesn’t record the number of attemps and after 3 failed attemps shutdown th db. Again the code works the way I want it to I just want to speed up the opening of the form and speed of the lookup.

Reply | Quote

Replies

I don’t know if using a recordset will be faster, but it won’t hurt to try.
Set a reference to the Microsoft DAO 3.6 Object Library (Tools | References…)
The code could look like this:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblAssociateInfo”, dbOpenDynaset)
…
rst.FindFirst “[AssID] = ” & Me.txtAssID
If rst.NoMatch = True Then
‘ not found
…
End If
…
rst.Close
Set rst = Nothing
Set dbs = Nothing

For reasons of efficiency, it is best not to open and close the recordset for each entry attempt, but to set it at the beginning, and to keep it open until it is no longer needed.

Reply | Quote

Thanks HansV, I’ll give it a try and post back the results.

Reply | Quote

You should be aware that the approach you are taking to register users isn’t very secure. You could of course prevent bypassing the startup key, but there isn’t anything to prevent a user from opening the backend and reading the tblAssociateInfo and getting the ID (and password presumably) and logging in. If you are concerned about those issues, then you should explore the Access User Security feature.

As a method of speeding up performance, you might consider putting the table you are working with in the Front-End rather than the Back-End. We often do that with small tables that are relatively static. It can however be a pain if you have a number of users with the FE deployed to each workstation and you need to update it. If that’s an issue here post back and we can make some suggestions.

Reply | Quote

Thought about using Access security. However I keep hearing that the workgroup needs to be kept with the mdb, if this is the case the user can bypass (over write) the workgroup and have admin access to the file regardless. Is this a fact?

To your point the issue with keeping the table on the FE is the maintenance (large turn over).
Nevertheless I’m open to all suggestion in improving this program.

Reply | Quote

If you set up user-level security properly, the admin user can do little or nothing in the database, so bypassing the secured workgroup file will not work. See WendellB‘s tutorial The Secrets of Security. It contains many useful links. Another good tutorial can be found on jacksonmacd‘s website (first link).

Reply | Quote

Hans’ response is correct – a properly secured database can only be opened by a person who is using the correct .MDW file, and is authorized by userid and password to open the database. There isn’t a three-strikes and you are out facility, so people can try repeatedly, but a resonably complex password will make it quite difficult to hack. The documents Hans suggested should give you a decent feel for the subject, and our tutorial includes references to a number of Microsoft articles that are quite useful as well.

The usual problem people have with applying security is that they don’t follow all the steps – it’s relatively complex with 13 to 15 steps depending on the source document you read, but the Security Wizard will do a fair bit of it for you. One of the more valuable side benefits is that you can track who made changes using form events.

As to the turn-over issue, then you will probably want to locate the .MDW file on the server so you only have to change one copy, though that may cause some performance issues during the login process, but once that’s been done, it should be responsive. If you have lots of simultaneous users, you may want to look at a deployment manager that will copy new versions of both the FE and the MDW file down to the local hard drive on the workstation.

Reply | Quote

First Thank you both Wendell & HansV for all of the valuable information you both have providing me with.

HansV, removing the DLOOPUP and going to recordset improved the speed of the lookup. I was clocking 8 seconds at first now it is down 3 second. Thank you.

Now, I

Reply | Quote

(Edited by HansV to make URLs clickable – see Help 19)

have a look at these replacement functions for the Dlookup and other domain aggregate functions.

I have not used them but they may help.

http://easyweb.easynet.co.uk/~trevor/AccFA…ules.htm#doamin%5B/url%5D

also some tips to make access faster can be found at

http://www.fmsinc.com/tpapers/faster/index.html%5B/url%5D

Reply | Quote

I don’t see how this 1 use of Dlookup can possibly make all your forms slow.

Reply | Quote

Oops, got distracted and posted the last message before I had a chance to finish it.

As I was saying, I can’t see how 1 DLookup will make your forms run slow. Poor form performance is usually caused by 1 or more of the following factors:

– Large recordsets for bound forms.

– Many subforms with large recordsets.

– Name autocorrect is ON.

– Subdatasheets haven’t been removed.

– Lack of indexes on sorted/searched fields.

Reply | Quote

It wasn’t one Dlookup is was 2 of them and only on one form. To test my assumption I made a copy of the form. I then ran it several times and averaged the time of the form with the Dlookup, then I did the same thing with the one with the recordset call. So, for what ever reason the form with the recordset call always ran faster then the Dlookup. This may not be the most scientific way of testing performance, but it sure works well for a poorman.

Reply | Quote

Well, your original message said “…the forms are very slow and I’m going to have to blame the dlookup function.” So from this I gathered there was as issue with multiple forms.

Regardless, whether it is 1 Dlookup or 2 of them, I doubt that it would make any sort of meaningful difference using dlookup vs a recordset.

Reply | Quote

Mark, you are correct I should have done a better job of describing my situation. There several forms that are slow, but my original post was for the one form.

Reply | Quote