Access performance

[leemorris2005]

Hi everyone

I'm starting a year-long university project which will be written in c#. The client app will be run on a LAN, with 35-40 users accessing the database concurrently, connecting to a database held on the server.

Could anyone tell me if Access can withstand 35-40 users at the same time? I found out that the maximum amount of users is 255, but what kind of performance impact(if any) will there be with 35-40?

I've searched all over the Internet for this, but I can only find references to using access behind a website, which isnt quite what I need. Any help would be great.

Thanks.

 

[RuralGuy]

It really depends on what those 35 to 40 users are doing. If they are all doing data entry and/or importing spreadsheet data is one scenario and all just viewing forms and reports is the other extreme.

Attribute Maximum
Microsoft Access database (.mdb) (Microsoft Access database: A collection of data and objects, such as tables, queries, or forms, related to a particular topic or purpose. The Microsoft Jet database engine manages the data.) file size 2 gigabytes minus the space needed for system objects (system object: Database objects that are defined by the system, such as the table MSysIndexes, or by the user. You can create a system object by naming the object with USys as the first four characters in the object name.).
Number of objects in a database 32,768
Modules (including forms and reports with the HasModule property set to True) 1,000
Number of characters in an object name 64
Number of characters in a password 14
Number of characters in a user name or group name 20
Number of concurrent users 255

Let some of the other guru's comment. There is a lot of Access experience in this Forum.

 

[mresann]

You can run 40, but there are three considerations.

1. Create queries that will return only the amount of data you need. Do NOT create forms or reports that bind to tables, just efficiently designed recordsets.
2. Do not link the tables. Use the ADO object model to create recordsets, making sure that when you read data, you use the read only attribute of the object.
3. If you do any mass or automatic inputting of data, try to schedule it during times when a lot of users will not be online. In fact, design these processes to work in exclusive (i.e, all other users locked out) mode so you don't have potential lockups.

Personally, even with all the efficient design, I would never run more than 10 users with Access (but then again, I wouldn't have too; my table design is ALWAYS geared to potential upsizing to SQL). I have run 20 users with no data entry with linked tables, but it was very slow. When I redesigned to ADO disambiguated recordset objects, it sped up to almost normal, but since there was no data entry except for management on a once a week basis, there wasn't that much chance for conflict.

I'm sure lots of other people have their experiences to share.

 

[leemorris2005]

Hi guys thanks for the replies

It's a mixture of both data entry and simple report viewing etc, the 40 users will be taking phone calls from new customers and they will have to add their details to the database, but they will also be tracking these customers over the weeks and printing reports.

I was also reading somewhere that if you perform a query on a remote access database, that the whole table/recordset is returned and the filtering is done clientside, is this true?

Thanks for your help.

 

[RuralGuy]

I was also reading somewhere that if you perform a query on a remote access database, that the whole table/recordset is returned and the filtering is done clientside, is this true?

Nope. It all depends on how you write the SQL.

 

[The_Doc_Man]

I was also reading somewhere that if you perform a query on a remote access database, that the whole table/recordset is returned and the filtering is done clientside, is this true?

Absolutely true IF the remote database is Access and your tables are merely linked to your clientside front-end. Or if you are running Access clientside on a .MDB file served remotely by a file server. I'm not sure whether there even exists a way to have Access be the server side of an ODBC link, so I doubt you can ever avoid the down-side of using Access clientside as you mentioned. The network load can get pretty hairy pretty quickly.

Not true if you really meant "little a" in the phrase "remote access database" (i.e. a database providing remote accessibility) - and the back end of the database is something like SQL Server or ORACLE or MySQL.

The problem with multiple users and a served .MDB file is file locking interactions. Despite the claim that Access can do an "Edited Record" lock, it lies. It really locks the buffer holding an edited record. If that buffer holds more than one record, ALL included records are locked. Which means that you have to write code carefully to minimize the time during which the record is open for editing.

The issue of how many users Access can handle has been bandied about for years on this forum. You can have lots of users but only if their use tends to be sparse. Like, up to maybe 4-8 users at a time out of a much larger range of potential users. Beyond that, run SQL Server or some other program that can allow Access to connect as a Front End to that product's back end via, say, ODBC.

That gives you TWO advantages. First, an ODBC back end does less with the network. Second, an ODBC back end keeps locks for a shorter time because of the lesser amount of network action.

Actually, there is a third advantage. An ODBC back end often has better security features than raw Access. While Access WorkGroups are OK, they are not what one would call "leading-edge security" mechanisms. You can't push them very hard before they fold.

There is even a fourth advantage. Once you have been forced into the split for front-end/back-end, you have the ability to upscale your application from, say, MySQL to SQL Server to ORACLE without changing the front end interface very much at all. Just migrate the tables, update the table links, and there you are, good to go on your upscaled DB.

 

[leemorris2005]

Hey guys thanks again for the replies, exactly what I needed. I think i'll go with MySQL or SQL Server instead. It's what I am more comfortable with anyway but I needed some kind of rationale as to why i'm not using Access in my work, this will do nicely Thanks again for the advice.

 

[The_Doc_Man]

I needed some kind of rationale as to why i'm not using Access in my work

Don't forget one thing of MASSIVE importance, though. Access, despite its ugliness over a network in "native" mode, is still one of the best PC-based front-end packages you can find for an ODBC mode of operation with something a bit more powerful on the back end.

Access is in the category of "Rapid Application Development" tools - which is a GOOD thing for the bottom line even though there are costs associated have multiple copies of Access, one on each affected PC. RAD tools can be an invaluable addition to your arsenal of problem-solvers.