Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How many users max out Access 2000? (2000)

    I currently have a front-end/back-end Access 2000 database on a Microsoft 2000 network with 30 concurrent users. The database performance is acceptable, but not exactly snappy. I may need to add between 8-10 more users to this database within a couple of months, but am concerned about exceeding it's capability. I know that Access is capable of up to 255 users, although I'm sure that it's not recommended.

    Does anyone else have a similar configuration, and if so have you had any serious issues? If you opted to another solution rather than have this many users, what did you do?

    Thanks!

    Howard

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How many users max out Access 2000? (2000)

    Up to 50 should be OK if there are not too many calls, however it can be impacted by a lot of factors such as network speed, size of tables, whether you use subdatasheets(this can be a real killer) and, of course, how many entries are being made.

    The simplest answer would be to upgrade the back end to Sql server.
    David Grugeon
    Brisbane Australia

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How many users max out Access 2000? (2000)

    Up to 50 should be OK if there are not too many calls, however it can be impacted by a lot of factors such as network speed, size of tables, whether you use subdatasheets(this can be a real killer) and, of course, how many entries are being made.

    The simplest answer would be to upgrade the back end to Sql server.
    David Grugeon
    Brisbane Australia

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

    Re: How many users max out Access 2000? (2000)

    Is your front-end actually located on the local workstation, and only the back-end resides on a server? In that scenario we have run as many as 120 users on a database, but it wasn't heavily used. Periodic lookups were done, but only a few hundred records were typically added or edited in a day. On the other hand David is correct in suggesting SQL Server. If you have 50 or more users dependent on a database, you don't want it going down if at all possible, and Access is rather more fragile than SQL Server. The upgrade to SQL Server isn't too bad, as there are several tools available to assist you, and you won't have to make major changes to the front-end if you use ODBC linked tables.
    Wendell

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How many users max out Access 2000? (2000)

    You said
    <<and you won't have to make major changes to the front-end if you use ODBC linked tables.>>
    Not having experience with SQL Server, what changes would you have to make?
    Pat

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

    Re: How many users max out Access 2000? (2000)

    If your application is fairly simple, with little or no use of DAO or ADO, then probably no changes once you have upsized to SQL Server. On the other hand if you are doing lots of recordset editing and inserting, you may need to recode some aspects of it. For example, an autonumber field value in Jet is known once you start the insert of a record, but in SQL Server the value isn't known until the record is actually saved. In situations where you are writing out a second record that needs to use the key from the first record, Access would have it for you immediately, but SQL Server has to be queried to get it after the first record is saved. Also, if you are trying to maximize performance, you may want to explore things like pass-through and/or ODBC Direct queries. This just scratches the surface in terms of possible changes, but the bottom line is that in most cases there won't be a great many. Of course you either need to learn the basics of SQL Server administration, or find an administrator who will help you.
    Wendell

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: How many users max out Access 2000? (2000)

    Thanks Mate

  8. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many users max out Access 2000? (2000)

    First suggestion. Revert your backend back to Access 97. A2k has a 2 gig limit, opposed to 97's 1 gig limit, but A2k and up uses Unicode, instead of ASCII, which takes up twice the space. Thus twice the work to read off of a drive, and probably twice the network bandwidth (not sure on the bandwidth issue).

    Next, development wise, there are two camps. Go unbound, so your framework is as light as possible. I use what I like to call a 'hit and run' method. Hit the backend to read/write data then disconnect. I have db's that used to handle 180+ users. (Downsizing has taken us down to 60+ users). With 'hit and run', even with 180 users, you are rarely getting more then a handful at the same time.

    The other camp is bound, which is a valid approach, and probably what you have in place. There are several techniques which can seriously improve bound performance. To begin with, whenever data entry is occurring, use forms with Data Entry turned to Yes. This prevents the users from seeing previous records, but it also serious improves performance, since you are only adding, not reading/writing. You can also lighten the connection load in bound front ends, it just takes time to go through each process to determine where users are 'hogging' the database.

    Drew

  9. #9
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many users max out Access 2000? (2000)

    Comment on Unicode character-encoding. By default Access uses Unicode Compression for text and Memo fields. As long as you are using entirely Latin characters (as in English, Spanish,

  10. #10
    Lounger
    Join Date
    Oct 2002
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many users max out Access 2000? (2000)

    Thanks for all of the information. Drew - I'm not sure that I understand the principle that you are describing. By unbound, do you mean make the form unbound to the table, gather the data input, and then give the form the table as the data source and then unbind it after the record is saved? If so, wouldn't that add a lot of time to each transaction, or increase chances of problems if more than one user is updating a record? Could you please elaborate about how you do this?

    Thanks!

    Howard

  11. #11
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How many users max out Access 2000? (2000)

    Unbound solutions are not for the faint of heart! <VBG> The bound capabilities of Access forms/reports are a very powerful and useful feature of Access. Using an unbound approach can be a bit more work, but you are dealing with a clean slate, and empty framework, you don't have to build backwards. Unbound solutions can be faster then bound solutions. You are doing the same thing, with code, as the bound process is doing internally. The reason it can be faster, is that you can skip some processes that you may not want/use.

    The best way to approach an unbound solution is to build Class modules to store your stuff in collections, so you read and write data only when necessary, and you fill your form objects with the classes and collections.

    Drew

Posting Permissions

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