Multiple Users in Access

[msliston]

I've heard through various sources that Access has problems handling multiple users. Unfortunately I heard this after I created a database and it crashed a file server when a number of users attempted to use the database.

It is a bit difficult for me to believe that 10 users could bring a 1 GHz file server to its knees but this is what I'm being told. I'm hoping that someone on this forum can provide some insight.

The database is very small and simple. On the other hand the number of users can be very large. It can be as little as 10 users up to 100 users.

Database Description: 1 primary table with 30 fields. Depending on circumstances this table will have between 500 and 3000 records. This table will need to be updated. A number of other tables contain data which is used for selection in combo boxes. These tables occasionally need to be updated but this will be done by the DB Administrator.

During the first test I didn't split the database but for an upcoming test I will.

My question is, what other kinds of things should I be doing? What is the maximum number of users that can safely modify data at the same time?

I use a number of queries to extract data to be displayed on forms and subforms (hopefully in lieu of using print outs). I noticed that the queries were creating updatable dynasets. Since the forms don't allow editing I'm wondering if changing the query to create a snapshot would have an impact as well?

Hyperlinks are used to link to related documents, one per record. To my way of thinking this should not adversely impact the database. Should I take the hyperlinking out?

Thanks in advance for any assistance.

 

[Craig0201]

msliston,

Access is not really the sort of tool that should be used by 100 users simultaneously. It is essentially a tool for small to medium enterprises and functions. The main reason for this I find to be the record-locking facilities aren't really up to the job once you get beyond 20 users.

If I was in your position, I would seriously consider moving up to SQL Server.

Craig

 

[MontyBurns]

smliston,

Craig's correct - you should really be looking at running the db on SQL Server.

Access has a theoretical maximum of 64 concurrent users (as far as i'm aware), but in reality it becomes incredibly slow with only a few concurrent users.

If you have Access 2000 or later, you could make sure the record locking is set to Record rather than Page (as a page can contain a group of records), but I don't see this as being the solution to your problem.

You could also split your db, as you say, and install the frontend db on every client PC. This will remove a large load from the server, but isn't very easy to implement as you will have to install the frontend on every users PC. This problem will persist though if you move to SQL Server - unless you have the frontend Access db located on the central server for everyone to use.

Ideally, you would want to move to an ASP Intranet solution using SQL Server - but this means your firm must have both an Intranet web server and SQL Server. And you may need to learn ASP, which isn't as simple as Access.

Hope this helps,
Burns

 

[tlbroadbent]

100 simultaneous users can really stretch Access even with a well-designed database and good programming. However, I read of people who successfully implement Access databases used by that many users. I have some DBs that are used by several users simultaneously, but never 100 at a time.

On the other hand, one user with a poorly designed application or query can bring SQL Server to its knees, even if it is running on a 4 processor, 600mhz server with 2GB of RAM. I watched it happen this week.

Regadless of the database choice, the developer still needs to pay attention to the details of the design or performance will be poor. I do recommend SQL Server over Access for your application, not only for the performance but for security, data integrity, recoverability, etc. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[msliston]

Thanks for the help. Not the answers I was hoping for but what I needed to know.

I will be exploring the SQL server and other options. Guess I should have looked beyond the MS hype that Access allows 255 concurrent users.

 

[turtlemaster]

Good luck msliston!!!

SQLServer, Oracle or any other enterprise DBMS doesn't run cheap. If you can convince your employer to shell out cash you let me know how you do it!!! LOL

 

[UncleJack]

All the foregoing criticisms notwithstanding, there are some things you can do to enhance the generally miserable Access network performance. Pay attention to your record locking scheme; set record locking on each form as well as the entire database. Open a connection from each front end to the back end on DB launch and do not close it so that every network request doesn't have to first establish a connection. I do this by putting an empty table in the back end and then establishing a unclosed connection to it.

But if you really do have 100 concurrent users then Access is worthless. SQL Server's purchase price will be regained within a month from recovering otherwise wasted employee time.

Uncle Jack

 

[msliston]

Unfortunately SQL Server is not available for the moment. So I'm stuck with plain MS Access for the time being.

A number of you have mentioned design issues. In addition to the things covered so far are there other things I should be looking for? Are there any reference documents that guide a person through these things?

The number of people actually needing to input/modify can be reduced to between 20 to 40. One of the critical aspects of the database will be extracting data (creation of reports). Will it do any good to create a separate front end for extracting data and another to input data? Not the ideal solution but a possibility until I can work out a better solution.

Any guestimates on the number of users if the Report front end remains on the file server, as opposed to putting the front end on the workstations?

Again thanks for the help.

 

[calian]

Like most, we have a number of tables with "live" data that gets constantly modified, and new records added. There are also tables with data that rarely or never changes (Zip codes, state abbreviations, violation codes, district designators, etc.), which we call reference data. I've gained a noticeable performance boost by having the live data on the server and the reference tables stored in separate .mdb's on the work stations. Just for convenience, I keep them in the same work station subdirectory as the front-end mdb.

The front end doesn't care where the tables are located as long as it can link to them. Drawing the reference data from local machines is faster for individual transactions, and reduces the overall traffic on the network. Also, if one reference table gets corrupted, it's a small matter to copy in a good one from some one else's station.