Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    72

    Unanswered: Please "10 gold rules" to use access on network to speed up operation

    When i use in ADO c:\mymdb.mdb all opeartion to read write update and select are very very fast...
    But if i use the same code and have the mdb in \\myserver\mydir\mymdb.mdb all operation are very very very slow....
    Note:
    i just have indexed all my filed for the variuos operation

    Please "10 gold rules" to use access on network to speed up operation

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    rule #1: don't use .MDB as a backend data engine across a LAN.
    if yourCorp-LAN is anything like myCorp-LAN, talking to an Access data backend across a network is a recipe for data corruption.
    speed (lack of speed) is the very least of your problems.
    that .MDB sitting on your server is a dumb file - NOTHING happens on the server, all the action takes place on the client machine. huge lumps of data must travel from (file-)server to client for every interaction. each client sends huge lumps of data back to the server when the most minor edit is saved.
    having said that, i do have a successful and reasonably fast network implementation using .MDB backend but it is in a Corp with a powerful dedicated fileserver, dedicated LAN, and never more than five users. this system has not corrupted the backend data since 1998 (but it still makes a backup every time a user logs in "just in case")[/COLOR]

    rule #2: don't use Access as data engine - use MySQL
    rule #3: don't use Access as data engine - use MS SQL-Server
    rule #4: don't use Access as data engine - use anything you can get your hands on.

    that will solve the corruption issue

    rule #5: use unbound forms
    rule #6: do not use linked tables
    rule #7: use pass-through queries

    that will solve your speed problem if you have a decent data engine.

    rule #8: careful interface design (e.g. single-record forms)
    rule #9: careful table (& index (& query)) design
    rule #10: careful interface design (e.g. open forms with zero data - gives instant gratification to the user so is subjectively "faster")

    #1, any of #2...4, plus #5...10 can give you an instant response 1000-user system.

    MySQL on a modest 5-year old server will handle 1000 concurrent users with speed and a smile. it's free! Access talks happily to the MySQL engine.
    MS SQL-Server is also able to handle a huge number of users but in general needs more oooomph from the server (and you might need to pay the licence fee).
    Access is a truly fantabulous data engine when it is running single-user on your local machine, but it is way lower than second choice for multi-user over a busy LAN.

    not what you wanted to hear?
    sorry!
    check out the codebank - i seem to remember that either healdem or pkstormy or both put up some posts on this topic using .MDB as the data "engine".

    izy
    Last edited by loquin; 02-02-09 at 15:57.
    currently using SS 2008R2

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    Might I also suggest this article?
    Me.Geek = True

  4. #4
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    There seems to be all sorts of variation on this topic ranging from izy's comments through to people who say they 20 and 30 people using the DB at the same time, with Access.

    I am beginning to wonder how much it has to do with the individual DB. For example a telemarketing DB I have is used by several people and each case there is from 2 to a maximum of 7 people on it. I also have it for my own use and it I have 6 and the network is wireless. So far no corruption after a few years. This DB is opening and closing forms all of the time as well as running queries, each time a phone call is made. At start up it opens a lot of forms and checks for dates/call backs etc and the local machine is a little quicker but the networked machines are fine.

    Now to the other extreme. About 12 months ago I made a little DB that is for diet. It is a simple little thing. Recently I have had some interest from people wanting to buy it so a week or so ago I cleaned it up and then networked it. It is almost a stall on just two machines and with cable connection. Its "Close" label has a Quit and even that is painfully slow. Yet the telemarketing one does a hundred times as much and has a 100 times more records and it will run on 6 machines and using wireless. The diet DB is stored in the same folder as the telemarketing DB.

    I am betting the little Diet DB has something wrong with it that does not show up until it is networked and if I remake it from scratch it will be OK.

    As a side note I have never had a corruption with the telemarketing DB with wireless and there would be at least one drop out a day. Also, the BE is the full DB, not just tables. I suppose I have been lucky.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the point where JET becomes a problem varies
    I've seen it as low as 15, I've seen it as high as 35..50 concurrent users.

    some of that variation is hardware specific (the speed of the network, the speed of the fileserver (the speed of the users)

    some of that is software, the way the application is designed. you can get reasonable high performing Access applications using JET data. however Ive not seen many these days which are new applications. due to the fall in price of servers you can start off using a server based data storage engine for not a lot of money (MySQL and the various desktop/personal editions of more traditional servers like SQL server), that and there are realtively few multi user organisations which don't use servers.

    most of the performance benefits to try to use JET are common for server applications (such as unbound forms, single record forms, and so on are common). the only extra is to carefully craft the SQL to make sure you get the best out of the server and Access using pass through queries.

    there is no point bolting on a server db to an 'standard' Access App.. potentailly you have the worst of both worlds.... JET type SQL, bound forms and a server sittign there doing not a lot.

    but that doss mean abandoning most of the nice features that many developers rely on... bound controls, access wizards.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2009
    Posts
    8
    rule #5: use unbound forms
    rule #6: do not use linked tables

    I use relation tables and a bound datagrid for my access database system. It is a local database, only one user at a time.
    Will I see a speed increase from going unbound and unlinking my tables?

  7. #7
    Join Date
    Sep 2006
    Posts
    265
    You can put a mdb on a server, it is not a recipe for disaster or corruption. There are a number of considerations:

    The server should have a Gigabit Ethernet capability
    It will also depend on the power of the server
    There should be a Front end / Back End deployment
    The Directory holding the mdb should be mapped directly
    One form [FE] should be attached to any table [BE] to resolve performance.

    Whilst this is not necessarily conventional wisdom there should be able to get the similar performance from a networked FE/BE as a local deployment.

    From a business point of vie and depending on urgency, it does not matter what is behind the database. Once your networked solution is established then look at the deployment of something like SQL Enterprise.


    Simon

Posting Permissions

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