Results 1 to 5 of 5

Thread: Performance

  1. #1
    Join Date
    Nov 2002

    Unanswered: Performance

    Hi everyone,

    I created a DB on my PC and it worked fine.
    Now, when I put it all on the network, performance is very, very slow.

    To connect to other databases I use linked tables connecting to a mapped drive.

    for example : In my DB I need a table from another DB which is located on a server called \\SERVER\DB\db.mdb

    \\SERVER\DB is mapped to S:\ so my DB points to S:\db.mdb

    I'm not sure if this will decrease performance.
    If so, what should be the best way to talk with othter databases.

    I also have a form with several subforms on it. Also this is loading very slowly.
    Is there any trick to load it mch faster.

    Reducing the number of controls is no option, because it's part of a weekoverview.

    Thanks for your advice

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    multiple use of an Access app can be a problem. An Access app using bound forms will hit problems around 10..20 users. Although an Access front end app can support many many users, Ive heard of multiple thousand users talking to a mainframe server db.

    the first thing to do is to split the db into a "front end", containing the forms, reports, quries etc.) and "back end", containing the data.

    ideally you should deploy the front end onto each workstation. that way round the user interface requires no network traffic to retrieve the forms & reports. you will still pull the data from the central back end.

    optionally consider dumping local data, or data that hardly ever changes onto the front end. that may require a periodic update from the central back end.

    do all temporary work or whatever on the local front end. If say you use temporary tables.

    Thats all relatively easy to implement, the main design issue is making sure that the client workstations are using the correct (most recent front end).

    There are other things that you can look at to improve performance. improving the 'quality' of your SQL can lead to big performance gains.. but its a black art. Generally you need to reduce the amount of data your queries use as soon as possible. make sure all the columns you do joins on are indexed, indexing columns that are often used in where clauses helps.

    Bear in mind that Access is a file based db, not a server based db. each and every data interaction requires network IO, sucking across at least the indexes, and often the data before discarding any irrelevant data.. hence why indexing join & where clauses can be important

    However getting the best often requires a rethink of how you use Access.. that means discarding bound forms and using unbound forms (being much more selective about how you retrieve data, update and other issues).. all of which is beyond the scope of forum like this.. Id suggest you consider getting a quality Access book such as the Developers Handbook by Getz and others.

  3. #3
    Join Date
    Nov 2002
    Thanks for your quick reply.

    I already use a splitted DB (I always do).
    Maybe I should take a closer look at the indexis.

    But stil I'm wondering if the use of linked tables is the fastest way.
    Or should I, one way or another, use OLEDB or ODBC to connect to other databases.


  4. #4
    Join Date
    Nov 2007
    Adelaide, South Australia
    Not back to an Access data file.

    You might like to move your data to an SQL / MySQL server and ODBC to that, but still the way you write your queries and the use of bound vs unbound forms is going to have huge impacts on speed.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!

    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Sep 2006
    Do you have a form that is using a Query or Table derived from the Back End Table. For instance a Main Menu boound to a Back End tables (just one record will do). Because this form is persistent, the Back End ldb file will always be open.


Posting Permissions

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