When an Access database needs to support more than a handful of users, most organizations try one of three approaches. Two of them are workarounds. One is an actual solution.
Here is what each approach looks like in practice, what it costs, and when it stops working.
Approach 1: File Server (The Default)
This is what most multi-user Access installations look like. The database file (or the back-end of a split database) sits on a shared network folder. Each user runs their own copy of Access on their own machine and connects to the shared file over the network.
How it works: Every time a user opens a form, runs a query, or saves a record, Access reads from and writes to the file across the network. The Jet/ACE database engine runs on each user’s local machine and coordinates access through a locking file (.laccdb).
Where it works: Small teams (2-5 users) with a fast, reliable local network and a database under 200 MB. In this scenario, file server sharing works surprisingly well.
Where it breaks down:
- More than 5-10 concurrent users. Jet’s file-level and page-level locking was not designed for heavy concurrent access. Conflicts increase, performance drops, and the database becomes sluggish.
- Slow or unreliable networks. Access sends a lot of traffic over the network — far more than a client-server database would. Wi-Fi connections, VPN connections, and anything with latency or packet loss will make the experience miserable.
- Large databases. As the file grows, network traffic increases. A 1 GB database over a file share is painfully slow.
- Database corruption. This is the big one. A network hiccup during a write operation can corrupt the entire database file. The more users, the more writes, the more chances for corruption. This is not a question of “if” but “when.”
Cost: Low. Just a file share and Access licenses you already have.
Verdict: Fine for small teams. A ticking time bomb for anything larger.
Approach 2: Terminal Server / Remote Desktop
The idea here is to eliminate the network problem by running everything on one machine. Users connect to a Windows terminal server (Remote Desktop Services, Citrix, or similar) and run Access there. Since Access and the database file are on the same server, there is no network traffic between them.
How it works: Each user gets a remote desktop session on the server. They open Access within that session. The database file is local to the server (or on very fast attached storage), so Access reads and writes are fast.
Where it works: This genuinely solves the network latency and corruption problems. All database access is local to the server, so performance is consistent and corruption from network interruptions is eliminated. It works for 10-25 users in many cases.
Where it breaks down:
- Server resources. Each Access session consumes memory and CPU on the server. Access was designed as a desktop application, not a server application. It is not efficient with shared resources. A server running 20 Access sessions needs significant RAM and CPU.
- Licensing costs. Windows Server licenses, Remote Desktop Services CALs (Client Access Licenses), and possibly Citrix licenses add up quickly. For 15 users, you may be looking at thousands of dollars in licensing alone.
- User experience. Remote desktop is never as smooth as a local application. There is always some lag. Copy and paste between the remote session and the local desktop is clunky. Printing from a remote session to a local printer requires configuration. Users notice and complain.
- Single point of failure. All your users are on one server. If that server goes down, everyone is down simultaneously. With the file server approach, at least users could still work on local data if the server hiccupped.
- Jet/ACE limits still apply. You have eliminated the network problem, but the database engine is still Jet/ACE. You still have the 2 GB file size limit, the limited query optimizer, the lack of stored procedures, and the absence of real transaction logging.
- It does not scale. At some point, you hit the ceiling of what a single terminal server can handle. Adding a second server means splitting users and managing session routing. The complexity climbs fast.
Cost: Medium to high. Server hardware, Windows Server license, RDS CALs, ongoing maintenance.
Verdict: A legitimate solution for medium-sized teams, but you are spending real money to work around the limitations of a desktop database engine. That money might be better spent solving the actual problem.
Approach 3: Migrate the Data to a Server Database
Instead of working around Access’s limitations, fix them. Move the data tables from the Access back-end to SQL Server, MySQL, MariaDB, or PostgreSQL. Keep the Access front-end (forms, reports, VBA) and connect it to the server database using ODBC linked tables.
How it works: The server database handles all data storage, locking, query execution, and transaction management. Access becomes a front-end client that sends queries and receives results. The heavy lifting happens on the server, not on each user’s machine and not across the network file share.
Where it works: This scales to dozens or hundreds of concurrent users. Server databases are designed for exactly this scenario. Locking is row-level (not page-level), queries execute on the server (not the client), and transactions are logged and recoverable.
Where it breaks down:
- Migration effort. Moving the data is easy. Fixing the queries and VBA code that relied on Access-specific SQL or local Jet behavior takes real work. Some queries will need rewriting. Some VBA code will need adjusting.
- New skills needed. Someone needs to install, configure, and maintain the server database. For SQL Server Express, this is relatively simple. For PostgreSQL on Linux, there is a steeper learning curve.
- Some Access features do not translate. Certain Access-specific features (multi-value fields, attachment fields, some query types) do not have direct equivalents in server databases.
Cost: Low to medium. SQL Server Express and PostgreSQL are free. The main cost is the migration effort and learning curve.
Verdict: This is the actual solution. You are replacing the component that is failing (the Jet/ACE database engine) with a component designed for the job (a server database engine).
The Decision Framework
Choose file server if: You have 5 or fewer users, a reliable wired network, a small database, and you are comfortable with the corruption risk.
Choose terminal server if: You need a quick fix for network-related problems, you already have terminal server infrastructure, and budget is available for licensing.
Choose migration if: You have outgrown Access’s database engine, you want a long-term solution, or you are tired of corruption and performance problems.
Most organizations that start with option 1 try option 2 before eventually arriving at option 3. If you are already at the point where file server sharing is causing problems, consider skipping the terminal server detour and going straight to migration. You will end up there eventually, and every dollar spent on terminal server infrastructure is a dollar not spent on the real fix.