Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Max Capabilities

    I have been designing in Access for a long time now, but never really knew what is the database's limit in regards to simultaneuos user connections.

    If I have a frontend database on say 100 workstations, and this database has all its tables linked to a backend database. How many people can be working on this DB changing and editing various forms before major lag is an issue?

    With all of the positive things that Access has, am I wrong in saying that if you need a database for a large department, then another solution should be considered for their database needs?

    I ask this becuase I have been ask to designing a database for a large department. I was told that it will track inventory, billing/invoices, scheduling and such. They also mentioned corporate headquarters which is located off site will need to be able to access reports from this database. I do not think Access will be up for this task?

    What are your thoughts?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think the theoretical limits are somewhere in the region of 255 concurrent conncenctions to an Access MDB, effectively though Access runs out of grunt somwhere between 15 and 50 concurrent users - it depends on the application, how its coded and deployed.

    However the limiting factor is not Access itself but the default back end database JET which works on a file server model. If you replace JET with a suitable server based database then those limits fall by the wayside. There is a fairly steep learning curve to get the best out of the client server model. It menas ditching bound forms, doing mor error handling, and beeing a lot more savvy about query design.

    The choice of back end data store is up to you (or your company) - there are a lot of options not just the Microsoft SQL server but other offerings such as MySQL, DB2. Depends what you and your company already have and are able to deploy.

    I'd consider buying a good reference such as the Access xxxx developer books by Litwin Getz & Gunderloyu as a good initial resource.

    HTH

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Healdem is correct; the limit according to Access (V 2000 anyway) is 255. For a list of all limitations fire up the Access help critter (Rocky is my favorite) and enter Microsoft Access database general specifications. This will lead you to all the limitations of your current version. As he also stated, the actual usable limit depends on the structure of the particular database. You really have to use trial and error to see the limits of your particular application, and then make adjustments accordingly.

    Good Luck!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Oct 2005
    Posts
    4
    you replace JET with a suitable server based database then those limits fall by the wayside.
    So if I designed all the tables and fields say in MySQL and use access to link to those table, it would improve speed as well as numebr of concurrent connections?

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by NoFrills
    So if I designed all the tables and fields say in MySQL and use access to link to those table, it would improve speed as well as numebr of concurrent connections?
    If you put a V8 engine in a Geo Metro would it go faster?

    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is a design pardigm that you have to take on board to take advantage of server based data storage. Linking tables will give some improvement in performance, but to get the 'best' performance (and your applciations be 'good' clients of the server and network) you need to move to uinbouind controls, and requesting only the information you need. also soem of the thr 'dirty' tricks you have poicked up along the way become redundant. Tricks such as stuffing combo/list boxes with masses of columns to speed up Access to access.

    That's why I'd suggest you get decent book on designing server applications in Access before actually diving in.

    Bolting on a super fast engine isn't going to help if the basic chassis isn't designed to take advantage of the power - in some circumstances it makes the car undrivable.

    If you go down the server route then there are a lot more considerations that you need to take into account such as security, data integrity data traffic, tuned query design. Bearing in mind that most corporate data servers limit or prohibit what you yourself can do - the requirements often go through a DBA to ensure that a malformed SQL doesn't bring the server to its knees. On of the key changes is to make, is to shift as much of the SQL processing onto the server, and some of this involves droppping / relearning SQL. Although SQL is an international standard each vendor has variations on the standard and JET has some odd quirks (just as SQL server and other do).

    If you are deploying the server then you alsoe need to get upto speed on maintaining the server (although there are tools out there to help you do this) you till need to do some periodic intervention to make sure the db is healthy, indexes OK. Its no big deal - think of it as running "comapct and repair" but on the server.

    PS career wise if you have the budget and choice go down the SQL server route, if budget is an issue MySQL is in my view the prime consideration (although I have deployed MySQL in favour of SQL server where budget wasn't a problem).
    Last edited by healdem; 10-19-05 at 05:33.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    If you put a V8 engine in a Geo Metro would it go faster?

    Quote Originally Posted by Healdem
    Bolting on a super fast engine isn't going to help if the basic chassis isn't designed to take advantage of the power - in some circumstances it makes the car undrivable.
    Oh super - analogies.

    Quote Originally Posted by Healdem
    That's why I'd suggest you get decent book on designing server applications in Access before actually diving in.
    This would be like barrelling down a country lane with your headlights off. At night.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Oh super - analogies.

    This would be like barrelling down a country lane with your headlights off. At night.
    As my best mucker CJ might say....
    "I didn't get where I am today without using analogies"

  9. #9
    Join Date
    Oct 2005
    Posts
    4
    I am going to run some test DB's using Access and MYSQL since I never really used this combo together. You would think that the less of my worries would be installing the software, but as life has shown often, you can never assume.

    Trying to install MYSQL 4.1 and also Tried 5.0 with no luck. Keep getting a server can not be started.

Posting Permissions

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