Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Improve Dlookup Speed (ACC 2002)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.

  3. #3
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    Thanks HansV, I'll give it a try and post back the results. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.
    Wendell

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    (Edited by HansV to make URLs clickable - see <!help=19>Help 19<!/help>)

    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

    also some tips to make access faster can be found at

    http://www.fmsinc.com/tpapers/faster/index.html

  6. #6
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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 <!profile=WendellB>WendellB<!/profile>'s tutorial The Secrets of Security. It contains many useful links. Another good tutorial can be found on jacksonmacd's website (first link).

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.
    Wendell

  9. #9
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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

  10. #10
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    I don't see how this 1 use of Dlookup can possibly make all your forms slow.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.

  13. #13
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Lounger
    Join Date
    Jan 2002
    Posts
    32
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Improve Dlookup Speed (ACC 2002)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •