Managing Multi-User Conflicts in Access Databases
Record locking, split databases, and refresh strategies — here is what every Access developer needs to know about keeping shared databases stable under concurrent use.
Sharing an Access database across a network is one of the most common — and most fragile — setups in small business IT. When it works, it is convenient. When it breaks, it breaks badly: locked records, corrupted files, users overwriting each other's changes.
Most of these problems are preventable. Here is what you need to know.
The Split Database: Non-Negotiable for Shared Use
If your team shares an Access database and you have not split it, do that first. Everything else in this post assumes a split database.
What splitting does: separates the database into two files.
- Backend (.accdb) — tables only, stored on the network share
- Frontend (.accdb) — queries, forms, reports, VBA, stored locally on each user's machine
Each user runs their own copy of the frontend, which links to the shared backend. This means:
- Only data travels across the network, not the entire application
- A frontend crash does not corrupt the backend
- You can update the frontend without touching the data
- Performance is dramatically better
How to split: Database Tools > Access Database (the "Move Data" section) > Split Database. Follow the wizard. It creates the backend file and relinks the frontend automatically.
After splitting, distribute the frontend to each user's local machine. Never run the frontend from the network share.
Understanding Record Locking
When a user opens a record for editing in Access, the database engine can lock that record to prevent other users from editing it simultaneously. Access offers three locking modes, set in File > Options > Advanced > Default Record Locking:
No Locks (Optimistic Locking) Records are not locked while being edited. When the user saves, Access checks whether anyone else changed the record in the meantime. If they did, Access shows a conflict dialog and asks the user to resolve it.
- Best for: environments where the same record is rarely edited by two people at once
- Risk: conflict dialogs confuse users; some dismiss them without reading, losing changes
All Records The entire table is locked while any user has a form open. No other user can edit any record in that table.
- Best for: batch operations where you need exclusive access
- Risk: completely unusable in a shared environment for normal data entry
Edited Record (Pessimistic Locking) The specific record being edited is locked. Other users can view it but cannot edit it until the first user saves or cancels.
- Best for: environments where conflicts are common and you want to prevent them proactively
- Risk: a user who opens a record and walks away locks it for everyone else
For most shared Access databases, No Locks with good user training is the most practical choice. Pessimistic locking sounds safer but creates its own problems.
The .laccdb Lock File
When any user opens an Access backend, a lock file appears in the same folder — same name as the database but with a .laccdb extension (.ldb for older .mdb files).
This file tracks who has the database open. It is created automatically and deleted when the last user closes the database.
Common issues:
- If Access crashes, the lock file may not be deleted. The next user to open the database sees a warning about the file being in use. If you are certain no one else has it open, you can safely delete the
.laccdbfile. - If the lock file is on a read-only network share, Access cannot create it and will refuse to open the database in shared mode.
- Never delete the lock file while users have the database open — you will corrupt the backend.
Refresh Strategies: Keeping Forms Current
In a shared database, one user's changes are not automatically visible to other users. If User A updates a record while User B has the same form open, User B will not see the change until the form refreshes.
Manual refresh: Users press Shift+F9 to requery the form's record source. Reliable but requires user action.
Automatic refresh: Set the form's Auto Refresh property (in seconds) to requery periodically. A value of 60 means the form requeues every minute.
' Programmatic refresh in a form
Me.Requery
' Refresh current record only (faster, does not move to first record)
Me.Refresh
Me.Refresh updates the current record's data without requerying the entire record source — useful for forms bound to a single record. Me.Requery re-runs the underlying query, which repositions to the first record unless you save and restore the bookmark.
Preserving position after Requery:
Sub RequeryAndReturn()
Dim bm As Variant
On Error Resume Next
bm = Me.Bookmark
Me.Requery
If Not IsNull(bm) Then Me.Bookmark = bm
On Error GoTo 0
End Sub
Handling Write Conflicts Gracefully
When optimistic locking detects a conflict, Access shows a built-in dialog that most users find confusing. You can intercept it with the form's Error event:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
' 7787 = Write conflict
If DataErr = 7787 Then
Response = acDataErrContinue
MsgBox "Another user has changed this record. " & _
"Your changes have not been saved." & vbCrLf & vbCrLf & _
"Click OK to see the current version of the record.", _
vbExclamation, "Record Conflict"
Me.Undo
Me.Refresh
End If
End Sub
This replaces the cryptic built-in dialog with a clear message and automatically shows the user the current version of the record.
Network and File Share Considerations
The network path to the backend matters more than most developers realize.
Use UNC paths, not mapped drive letters. Mapped drives (Z:\SharedData) vary between machines and can disconnect. UNC paths (\server\share\data) are consistent.
Relink tables with UNC paths:
Sub RelinkTables()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strNewPath As String
strNewPath = "\\server\share\data\Backend.accdb"
Set db = CurrentDb
For Each tdf In db.TableDefs
If tdf.Connect <> "" Then
tdf.Connect = ";DATABASE=" & strNewPath
tdf.RefreshLink
End If
Next tdf
MsgBox "Tables relinked to " & strNewPath
End Sub
Avoid VPN-only access to the backend. VPN connections introduce latency that makes Access feel sluggish. If remote users need access, consider a Remote Desktop setup where they run the frontend on a machine that has a fast local connection to the backend.
When Multi-User Access Reaches Its Limit
Access handles 5–10 concurrent users reasonably well in a split database on a fast network. Beyond that, you will start seeing:
- Slow form loads as the network becomes a bottleneck
- Frequent lock file corruption after crashes
- Increasing frequency of write conflicts
- Occasional "database is not a valid Access database" errors after a bad shutdown
These are not bugs you can fix — they are architectural limits. Access was designed as a desktop database, not a server. When you hit these limits consistently, the right answer is SQL Server as the backend (with Access as the frontend via linked tables, or a full migration to Power Apps).
The good news: a split database with linked tables is already halfway to a SQL Server migration. The tables move to SQL Server; the frontend stays largely the same.
Quick Checklist for Shared Access Databases
- Database is split (backend on network, frontend local)
- Frontend distributed to each user's local machine
- Default record locking set appropriately (usually No Locks)
- Forms have Auto Refresh set or manual refresh available
- Write conflict errors handled with a custom
Form_Errorhandler - Backend linked via UNC path, not mapped drive
- Regular Compact and Repair scheduled on the backend
- Backup of the backend runs nightly
Get these right and a shared Access database can serve a small team reliably for years. But keep an eye on user count and performance — the ceiling is real, and it is better to plan a migration before you hit it than after.
Work With Access Evolved
If you found this article useful, Access Evolved specializes in exactly this kind of work — from hands-on Microsoft Access support to full database modernization projects.
Dealing with record locking and conflicts in your multi-user Access database? Schedule a free call and get an expert diagnosis.
Thinking about migrating?
Ready to move beyond Microsoft Access?
Access is a great starting point, but growing businesses often hit its limits. Anthony Passeri specializes in migrating Access databases to modern platforms — Power Platform, Power Apps, SQL Server, Azure, and more — so you keep your data and workflows without the headaches.
Written by
Anthony Passeri, MBA
Access Expert and Editor
Bridging the gap between Access and the Future
Explore Topics
Keep reading
More on Access Basics
Access Split Database: When It Helps and When It Doesn\'t
Splitting your Access database into a front end and back end is the standard advice for multi-user deployments. But it is not a cure-all. Here is when it actually helps — and when you need something more.
Why Is My Access Database So Slow with Multiple Users?
If your Access database crawls when two or more people are using it at the same time, you are not alone. Here is what is actually happening — and what your options are.
Deploying Access Databases with the Runtime
The Access Runtime lets you distribute your database to users who do not own Access. Here is how it works, what it limits, and when it makes sense — and when it does not.