Results 1 to 9 of 9
  1. #1
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45

    Accde database password and performance?

    I made an accde data database that is linked to an accdb.
    When I put a password on the accde dlookup is very slow.

    I did a test of 10 dlookups.
    With password it takes 8.46 seconds.


    Without password only 0.39 seconds

    Is there a solution to get the dlookup fast in combination with a database password?

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    how are you using your dlookup? if you're using it in a query, find a way to avoid using the dlookup (i.e. link a sub query or table)

    If the dlookup is in code or on a form to return a specific value that'd be a different animal and I'd need to see the code you're using.

  3. #3
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    Dlookup is directly to a table and in VBA.

    Example: T = Dlookup("Number", "TblOrder", "OrderId = 525")

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Yeah, that doesn't help.

    Are you writing code and doing a dlookup that's what it looks like to me because you're setting a variable (T) is that correct or am I reading too much into this. Seeing this line of code without seeing what else you're doing doesn't really help either. For instance you can create a recordset that includes the lookup value you want so you don't have to do a dlookup. Domain functions should be used sparingly because they are notoriously slow and memory hogs especially on larger tables (which I assume tblorder is)

    Secondly I hadn't thought about it before, but if your front end has a password and your back end doesn't, how is your back end data getting protected? Or do you have a password on both front end and back end? Are the passwords the same? I do not know if this will make a difference at all I just don't have a clear picture of how your database is actually set up.

  5. #5
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    We use more than 1000 dlookup, dmax, dcount, etc. in our code. How we use these functions is not the problem.
    With a mde with password performance never was a problem.
    An accde without password is also no problem.
    But an accde with password also the database is encrypted en than the performance is a problem.
    We want to use password on the data database so the customer cannot open the database with access and make changes in the data database.

  6. #6
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    That sounds like an awful lot of domain functions.
    I would have thought that queries to produce the results of a dmax or dcount for all records linked back to your related records would be a far more efficient method of displaying the information.

    Passing the password each time will inevitably slow things down.
    Later versions of access have different security processes and more robust error checking of things, it's possible your existing methods will need to be revised or improved.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the backend is the backend and should be mdb or accdb, there is zero benefit in making it a mde or .accde

    That said, you can still use an mdb with password protection for the BE. Just be aware that, as Minty says, the .accdb encryption is to a much higher level that mdb. The mdb one can be easily hacked.

    With regards improving the performance of dlookup, it is the slowest way of doing things so I agree, probably time to find a better way. Only thing I can suggest if you want to stick with the domain functions is to ensure all relevant fields in your criteria are indexed.

  8. #8
    BrightSoftware is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jun 2016
    Posts
    45
    Dlookup to an accdb backend database is 10 times slower than an accde database

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I'm sorry but that makes absolutely no sense.
    The BE file should consist of tables only. No code.
    As an ACCDE file just makes VBA code inaccessible, using an ACCDE BE should be functionally identical to an ACCDB and s already stated, its pointless.

    By contrast, your FE SHOULD be an ACCDE file.

    Have a look at this article comparing security in different types of Access file http://www.mendipdatasystems.co.uk/c...ity/4594444323
    What you have now is very insecure.

    As already stated, using DLookups should be avoided as far as possible.
    They will be slow when accessing data from any networked BE file.
    Using them widely as you describe means your application will always be slow.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 06-03-2016, 03:58 AM
  2. Replies: 0
    Last Post: 08-18-2014, 09:11 AM
  3. Split database - ACCDE
    By Sheba in forum Database Design
    Replies: 21
    Last Post: 08-14-2014, 10:13 AM
  4. Replies: 2
    Last Post: 01-15-2014, 07:57 PM
  5. Database performance issues
    By smikkelsen in forum Access
    Replies: 3
    Last Post: 03-10-2011, 05:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums