Setting Up Microsoft Access for Multiple Users

Microsoft Access is often dismissed as a "single-user" tool, but that is not accurate. A properly configured Access database can support dozens of simultaneous users reliably. The key word is "properly configured" — the default single-file setup is indeed unsuitable for multi-user environments, but the split database architecture changes everything.

The Problem with a Single-File Database on a Network

When you place a single .accdb file on a network share and have multiple people open it simultaneously, Access has to transfer the entire file — including all the forms, reports, queries, and VBA code — across the network every time someone opens it. This is slow, and it creates contention as multiple users try to read and write the same file.

Worse, if two users are editing the same record at the same time, Access has to resolve the conflict. Without proper configuration, this can lead to data loss.

The Solution: Split the Database

The split database architecture divides your database into two files:

Backend (.accdb) — contains only the tables. Stored on the network share. This is the only file that needs to be on the network.

Frontend (.accdb) — contains forms, queries, reports, macros, and VBA code. Stored locally on each user's computer. Each user gets their own copy.

With this setup:

• Forms and queries run locally — no network transfer for application logic
• Only data travels over the network
• Each user's frontend can be updated independently
• If one user's frontend crashes, it does not affect other users

Using the Database Splitter

Access includes a built-in wizard:

1. Open your database
2. Go to Database Tools → Move Data → Access Database
3. Click Split Database
4. Choose a location for the backend file (on the network share)
5. Click Split Now

Access creates the backend file, moves all tables to it, and replaces the tables in your original file with linked tables pointing to the backend. Your original file becomes the frontend.

Distribute a copy of the frontend to each user's local machine.

Record Locking Strategies

When multiple users edit data simultaneously, Access needs a strategy for handling conflicts. You have three options, set in File → Options → Client Settings → Advanced → Default record locking:

No Locks (Optimistic Locking)

Access allows multiple users to open the same record for editing simultaneously. When the second user tries to save, Access detects the conflict and presents a dialog asking which version to keep.

Best for: Databases where conflicts are rare and users can resolve them manually.

All Records (Pessimistic Locking)

When a user opens a form for editing, Access locks the entire table. No other user can edit any record in that table until the first user closes the form.

Avoid this. It is too restrictive for most multi-user scenarios.

Edited Record (Pessimistic Locking)

When a user starts editing a specific record, Access locks just that record. Other users can view it but cannot edit it until the first user saves or cancels.

Best for: Databases where data integrity is critical and conflicts must be prevented rather than resolved.

For most multi-user databases, No Locks (optimistic locking) is the right choice. Conflicts are rare in practice, and the dialog that appears when they do occur is clear enough for users to handle.

The .laccdb Lock File

When an Access database is open, Access creates a lock file with the same name but a .laccdb extension (e.g., Database.laccdb). This file tracks which users have the database open and which records are locked.

Do not delete the .laccdb file while the database is in use — this can corrupt the database. If you see a .laccdb file when no one should have the database open, it means Access did not close cleanly. It is safe to delete in that case.

Refresh Interval Settings

In a multi-user environment, users need to see changes made by other users. Access does not automatically refresh data in real time — it refreshes on a schedule.

Configure this in File → Options → Client Settings → Advanced:

• Refresh interval (sec): How often Access refreshes the current view. Default is 60 seconds. Set to 15-30 seconds for more responsive multi-user behavior.
• Number of update retries: How many times Access retries a failed write. Default is 2.
• ODBC refresh interval (sec): For linked SQL Server tables.
• Update retry interval (msec): Time between retry attempts.

Handling the "Record is locked by another user" Message

Users will occasionally see this message when using pessimistic locking. Train users to:

1. Wait a few seconds and try again — the other user may have just saved
2. If the message persists, check with colleagues to see who has the record open
3. Never force-close Access to "release" a lock — this can corrupt the database

If lock conflicts are frequent, switch to optimistic locking and handle conflicts through the dialog.

Network Considerations

Use a wired connection — Wi-Fi introduces latency and packet loss that can cause corruption in Access databases. If users must use Wi-Fi, ensure a strong, stable signal.

Map the network drive — Use a mapped drive letter (e.g., Z:) rather than a UNC path (\server\share) for the backend. UNC paths can cause issues with Access's locking mechanism.

Avoid VPN for the backend — VPN connections add significant latency. If remote users need access, consider migrating the backend to SQL Server with Azure connectivity, or use Remote Desktop to run Access on a local machine.

Antivirus exclusions — Add the backend database file to your antivirus exclusion list. Real-time scanning of an open Access file can cause corruption and performance problems.

Maximum Users

A well-configured split Access database can comfortably support 10-20 simultaneous users. Beyond that, performance degrades and the risk of corruption increases. If you need more than 20 users, it is time to migrate the backend to SQL Server while keeping the Access frontend — this gives you enterprise-grade concurrency with minimal changes to your forms and reports.

Conclusion

Multi-user Access is entirely viable when configured correctly. Split the database, store the backend on a reliable network share, configure appropriate locking, and set a reasonable refresh interval. Follow these steps and your Access database will serve a team of users reliably for years.