Multiple Users working in same Access database

[ski52]

Hi all,
I've been tasked with a database application that has the potential (although how much, I don't know) of having users simultaneously accessing the data. I want to use Access for this because the people here are accustomed to Office applications, but I've heard that Access has some problems with multiple users accessing data at the same time, that it tends to corrupt the database. Have I heard wrong, or should I look into using another database? Or, is there a work around to ensure these problems wouldn't occur?

[norie]

Well there can be problems when doing this.

One method to help avoid them is to split the database into 2 databases.

One 'data' database would contain all the data.

And the other 'front-end' database would contain forms/reports/queries etc and would have links to the tables in the 'data' database.

You could store the 'data' database in a central location and ditribute copies of the front-end to uses.

[ski52]

you say it 'helps avoid them [the multiple-access problems]', does that mean it's still more possible than with other databases?

[Will Riley]

How many users are we talking concurrently?

[ski52]

I'd say a possible maximum of 20 users at a time. There's a small chance of 50 users tops, but that's if everyone involved were using it at the same time, and not all of them would be editting the database.

[Will Riley]

Microsoft lists 255 as the maximum number of concurrent users to an Access database. This means that only 255 users can actively interact with the database at the same time. This might be theoretically true in Microsoft's labs under the ideal circumstances, but the reality of working with an Access database is that performance falls off sharply when more than 25 or 30 concurrent requests are made. This means that although Access remains suitable for small applications, those that experience growth where they are experiencing more than 25 concurrent connections should consider upgrading to a more robust database such as SQL Server or if that's too expensive, MySQL

[norie]

Another consideration is what the users are actually going to be doing in the database.

Are they all going to be trying to access the same records/tables at the same time?

[ski52]

There won't be many tables in this database, so yes they'd be accessing the same tables. The number of records might build up eventually, but per year there won't be many records, and the data focuses on a per-year basis, so they would also be accessing the same records. Maybe I should also point out that I'm figuring 20 users as a rare maximum, I doubt the application will ever go above 10 users simultaneously.

[norie]

What will the users actually be doing with the database?

Will they be adding/editing/deleting records?

[ski52]

They'd mainly be adding and editing, sometimes deleting.