How many users max out Access 2000? (2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

You said
<>
Not having experience with SQL Server, what changes would you have to make?
Pat

Reply | Quote

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.

Reply | Quote

Thanks Mate

Reply | Quote

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

Reply | Quote

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,

Reply | Quote

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

Reply | Quote

Unbound solutions are not for the faint of heart! 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

Reply | Quote