What’s the Maximum Number of Microsoft Access Users?
There is a persistent myth that Microsoft Access Jet databases can only support 20 or so users. Here’s my response to a recent inquiry:
I flatly refute any suggestions that Microsoft Access users are limited to around 30. We’ve run many tests and have never seen that kind of degradation in performance. It is a myth from Access 2.0 days that was eliminated with Access 97 almost two decades ago.
A poorly designed Access database won’t support two users, but a well designed Access solution can support hundreds of users. Of course, what matters is the number of simultaneous users, and what they’re doing.
At Least 200 Simultaneous Microsoft Access Users on an Access/Jet Database
If everyone is just viewing data or entering data into a table, that takes very little work and a large number of people (well over 200) can be supported. People cannot type faster than what Access can handle. If users are all running massive reports and queries with data updates, that can still be done but performance would be an issue which applies to any technology, so testing and optimization would be necessary.
Migrate Back-End Access Database to Microsoft SQL Server for More Users
If the back-end database is in SQL Server rather than an Access/Jet database, the number of users can be practically unlimited if each user has their own front-end copy of the Access application. Performance issues still apply based on what they are doing. In some cases SQL Server is slower than Access, so it is important to understand the situation before thinking SQL Server is the answer.
All that said, any Access application that is distributed to others with shared data should be a split database design. Here are a few resources we’ve written:
- Splitting Microsoft Access Databases to Improve Performance and Simplify Maintainability
- Significantly Improve the Performance of Microsoft Access Databases with Linked Tables
- Microsoft Access to SQL Server Upsizing Resource Center
Simplify Support for All the Users of Your Access Databases
Total Access Startup helps with the distribution of databases to each user’s desktop, and launching it with the right version of Microsoft Access.
This allows you to centrally support a large number of Access users across your network and ensure everyone is using the latest version of your application. It also simplifies the migration from one version of Access to another.
Additional Discussions
This topic was also discussed on the Microsoft Developer Network, Microsoft Access Database Scalability: How many users can it support?
Well said Luke! In many ways the perception of Access has not evolved with its improvments and the technology that supports it. Thanks for your continued support of this incredible product.
Thanks Richard!
Your input is much appreciated Luke. Thank you.
I try to use only the latest software from Microsoft and so I am using Microsoft Access 2010. The idea of runtime has a lot of appeal but I sometimes wonder. If I steer a client to a runtime version of thier application it only takes one thing (one thing important to the client) to cause him to feel "it doesn’t work?" Any counsel on Access Runtime 2010.
I stick to split databases with Access as the back-end as well as front end.
Hi Woody,
Glad this info is helpful.
To deploy a runtime application, you definitely need to have a solid error handling system in place so the user doesn’t experience a nasty crash without gracefully exiting, and you have the code to document what happened so you can fix it. This is the same issue if you deploy an MDE or ACCDE program because the Cancel/Debug dialog isn’t going to work.
At that point, the runtime version is simply a licensing issue. Some features like spell checking aren’t part of the Runtime program because that’s part of Office. Essentially, it’s Access’s way of competing against other programs that let you create an EXE or DLL and distribute it without having to pay for each user.
Here’s some feedback related to a question about the Microsoft Access 2GB database limit indicating it’s not scalable and therefore not appropriate for use:
MS Access fits between what people do with Excel and what enterprises do to create large, scalable, mission critical applications.
Access scalability should be a secondary consideration of whether Access is appropriate or not. I would postulate that 99+% of Access databases (and desktop databases) never run into the 2 GB limit, so scalability is more of the exception than the rule. It’s really hard for people to type that much information even after several years. Similarly, most Excel spreadsheets never get that big either. To drop a technology in fear that someone may create something wildly successful is inappropriate.
Even if scalability were an issue and the 2 GB limit encountered, we agree that SQL Server is an appropriate storage platform for the data. In that case, Access is perfectly fine as a front-end to that. Should end-users rely on others to create reports and view data that they could do on their own with Access? Seems like a waste of professional developer time for those situations.
I agree that people create bad database applications. But I would say that is not technology specific. There are lots of terrible applications written with SQL Server, .NET, Java, Oracle, SAP, Excel, etc. The more important question is whether the technology and user were able to solve their immediate problem versus their alternatives at the time, and whether the organization can leverage what they built if it’s successful. Even if it’s decided to throw away what’s already built, the fact that something exists is very helpful.
My experience is that most organizations don’t have the time or resources to build every database application that their line of businesses need. It’s wrong to look at the applications that need to be migrated to a better developer or platform and say that it would have been better or cheaper to build it "right" originally:
• Originally, the design would not have been known the way it is today. It was the actual evolutionary process for the solution to become what it is today. Interrogating the original author for his vision to get to today’s functionality would be impossible even if you were allowed to use waterboarding.
• The database evolution was partly controllable but mostly not. That is, the changes occurred due to external forces unrelated to technology (e.g. the economy, competitors, new products, management decisions, etc.)
• Only a small percentage (under 5%) of workgroup level databases ever need centralized IT involvement, and that’s after several years
• If organization don’t allow information workers to create simple databases applications on their own, the IT department needs to create all of them for them when less than 5% should involve them. The costs would be astronomical and turn around times very long. It’s basically giving opportunities to competitors.
So yes, giving people matches may create uncontrolled fires, but that’s why we have fire alarms and firemen. We don’t tell people they can’t use fire and force them to eat everything raw or pay a huge amount if they want something cooked. There is a middle ground. Organizations that manage this well gain a huge competitive advantage, and their IT departments really focus on what’s strategic.