​Yes – Microsoft Access works in a Multi-User Environment

One of the biggest misconceptions about Microsoft Access is that it can’t handle complexity. In regards to a multi-user environment, many professionals continue to operate under the paradigm that MS Access doesn't work properly in a multi-user environment. This assumption is an unfortunate and costly mistake.

​i’ve worked with MS Access since version 2.0 back in the Mid 1990s. I’ve watched Access go through all the various upgrades and transformations. I also have extensive experience with SQL. From experience I can tell you that Microsoft Access serves many of my clients very well in a multi-user environment. MS Access is a tool. And like any other tool, it has to be used properly. So, there are best practice protocols to using MS Access, but there are best practice protocols to developing in any other database software as well.

Best practice protocols, in regards to Microsoft Access, are not just my opinion as a programmer with 20+ years of experience. Best practice protocols are defined by Microsoft. Generally speaking, if the following issues are addressed properly, MS Access will function very well in a multi-user environment.

Record Locking: Within a multi-user environment, record locking is pivotal. Record locking prevents two people from editing the same record at the same time. In a multi-user environment record locking should be set as follows:

• Default Open Mode – Shared
• Default record locking – Edited Record
• You can find these settings in Access 2010 by going through the following steps:
  • File Menu
  • Click Options
  • Click Client Settings in the left side Options Menu Pane
  • Scroll through the Client Settings until you find the Advanced Settings
  • Set advanced settings as defined above.

Split Your Database: Splitting your Access database will dramatically improve performance in a multi-user environment. Generally speaking splitting an Access database means separating data storage functions from data processing functions. Data storage is managed with tables. Data processing is managed through forms (for data entry), queries, reports and other processing objects. To split a database, the storage component is “split” away from the forms, reports, queries, and reports. After an Access database has been split there will be a backend file (data tables) and a frontend file (data application).

There are a couple of different ways to split an Access database.

1. Data tables can be stored in an Access data file
2. Data tables can be stored in a SQL file

Either way, the data tables are stored separately from the main Access application. The backend tables are then linked back to the main frontend application for data access. Most of my clients operate in multi-user environments and every multi-user client operates off of a split database.

The use of SQL comes into play because of different factors. High volumes of data, high volumes of end-users, network capacity and capabilities all play a role in determining when to use SQL over an Access backend.

Other Considerations

Beyond the technical considerations listed above, following are some observations from my own experience.

Microsoft Access is not the perfect solution to all data processing problems. But, then there really is no perfect solution. By its very nature office data is dynamic, and therefore it requires dynamic data processing solutions.

One of my biggest frustrations with MS Access is that Microsoft really hasn’t developed an efficient means of deploying Access in an online environment. It can be done, but it’s not easy. I was excited when they introduced Sharepoint, because all their literature promised that it would be possible to deploy Access databases on a Sharepoint site. Technically, this is true. But, in practicality it simply doesn’t work, not with complex databases anyway. I have found that Sharepoint can play a role, at best, it’s a limited role. If the backend files are SQL, then it’s possible to harvest data through a Sharepoint form and use that data in Access as well. But, Sharepoint does not have the capacity for complexity that Access has.

If I feel frustration at Access’ limitations with online applications, in most other ways I’ve been pleased enough to continue to use and recommend the software. Access stands up well to all the critics. It continues to be the best selling desktop database application software on the market, and with good reason. It develops faster and less expensively than other options. It integrates quite nicely with Excel, Outlook, SQL and other software products. End-users can learn how to accomplish basic things in Access (like writing their own reports, queries, etc…). It works great in a multi-user environment, if set up properly.

MS Access is capable of highly complex information management requirements. Over the years I’ve developed and maintained database applications that process data from over a hundred SQL tables. This is no small feat and requires a lot from the frontend software. But, when used properly, Access has been able to stand up to all the requirements in the following areas:

1. Complex data entry screens needed to harvest data to the various tables
2. Complex report writing capabilities needed to report off of so many tables.
3. Complex querying capabilities needed to process such a high volume of data
4. Volume of traffic over the network needed to manage a high multi-user environment with a high data load.

MS Access is under appreciated, and under-utilized, in many organizations. This really is too bad; it’s a costly mistake to discard a product simply because of misconceptions and misunderstandings about its capabilities. Since Access develops so much faster than other frontend applications, it is a very cost effective choice for a lot of data processing needs. Not only that, Access is a fantastic tool for data management adaptation and evolution. Data is dynamic. It always has been and it always will be. Because data is dynamic, finding a data processing tool that is flexible is imperative. Access is extremely flexible; it can grow and change with the needs of an organization.

Before you dismiss Microsoft Access as a tool for your organization, take the time to really learn what it can do. You will be pleasantly surprised.

=================

EDIT - 02/26/2016:

​The following is a link to a discussion among professional Microsoft Access Developers about using Access in a deployment of thousands of users. If you want more information about using Access in a multi-user environment, this link is a must read: Can Access 2010 using SQL Server 2012 and RemoteApps (RA, aka terminal services) scale to hundreds/thousands of users?

EDIT - 02-21-2018:

Since writing this article,  some of my clients have successfully deployed Microsoft Access in a cloud environment. An article about Deploying Microsoft Access in a Remote Environment can be found here.

More Articles About Microsoft Access​​

• YES – Microsoft Access Can be Used Securely
• Why You Should Care about RAD and How It Impacts Your Bottom Line ....
• Do you have questions about your own data management project? Contact Michelle.