Guide · Access replacement
Why your Access database struggles with multiple users
Locked records, write conflicts and corruption explained, with the fixes worth trying first.
Microsoft's own specification says an Access database can have 255 people using it at once. In practice, many databases start misbehaving with a fraction of that.
That isn't because Access is bad. It's because of how it works, and once you see that, the symptoms make sense and so do the fixes.
One file, lots of people
A proper server database, like SQL Server or MySQL, has a program sitting in the middle. Everybody asks it for data, it decides who gets what, and only it touches the files.
Access has no program in the middle. Each person's PC opens the database file on the shared drive and reads and writes it directly. To stop two people changing the same thing at once, Access keeps a lock file next to the database (it ends in .laccdb, or .ldb for older files) and every PC checks it constantly.
So every extra user adds more network traffic, more lock checking and more chances for two PCs to want the same bit of the file. And if a PC loses its connection halfway through writing, the file can be left half-written. That's corruption.
What it looks like, and what it means
| What people see | What's usually going on |
|---|---|
| "Could not update; currently locked" | Someone else is editing the same record, or a record near it. Access can lock more than the one record you're working on. |
| The Write Conflict box: "Save record", "Copy to clipboard", "Drop changes" | Two people edited the same record and the second one saved over the first. Somebody's changes are about to be lost. |
| "The database has been placed in a state by user Admin..." | Someone opened it exclusively, or is changing the design while others are in it, or everyone is sharing one front end file. |
| "Unrecognized database format" or missing records after a crash | The file has been damaged. Time for Compact and Repair, and for checking the backups. |
| Everything is fine at 8am and slow by 10am | More people logged in means more network traffic and lock checking on the same file. |
| It's fine in the office but awful from home | Access over a VPN is reading a file across the internet. It was never designed for that. |
Seven things to check first
Before you spend money, check these. A few of them are free and make a real difference.
- Split the database. Tables in one file on the shared drive (the back end), everything else in another (the front end). Access has a built-in Database Splitter for this.
- Give everyone their own copy of the front end. On their own PC, not shared on the network. One shared front end is one of the commonest causes of locking trouble and corruption.
- Use a wired connection. Wi-Fi drops for a fraction of a second all the time. You don't notice. Access does.
- Don't run it over a VPN. If people work from home, have them use remote desktop to a PC in the office instead, so the file never crosses the internet.
- Check the folder permissions. Everyone needs to be able to create and delete files in the folder with the back end, or the lock file can't be managed properly.
- Check the locking settings. Under File, Options, Client Settings, record-level locking should be on, and forms set to lock only the edited record.
- Compact and repair the back end regularly, out of hours, with everyone out and a backup taken first.
If you're still in trouble, the next step up is moving the tables into SQL Server, which takes the file sharing out of the picture. Our guide on Access to SQL Server migration covers what that fixes and what it doesn't.
When it's the design, not the setup
Those fixes deal with the setup. Some problems come from the design itself, and no setting will cure them:
- People need to use it from home, on site or on a phone, without remote desktop.
- The number of users keeps growing.
- Slow screens have been "fixed" by copying data into tables on each PC, which then go out of date.
- Somebody has to run a cleanup or refresh routine for it to keep working.
That third one is more common than you'd think. On a database we're replacing now, some screens were so slow over the network that it copied results into local tables on each PC and read those instead. Very sensible at the time. But it meant each person could be looking at slightly different, slightly stale figures, and only the person who built it knew which screens to refresh.
At that point you're spending money keeping a system alive that the business has outgrown. A web application on a server database is designed for many users from the start: one program in the middle, no file on a shared drive, and it works the same from the office, from home or from a van. See converting Access to a web application and the main replacing Access page for timescales. We use AI-assisted development, so it's more cost-effective than most people expect.
Access multi-user questions
Microsoft's specification allows up to 255 concurrent users. In practice, a split database on a good wired network often copes with somewhere around 10 to 20 active users, and much fewer if the design is heavy or the network is poor.
Split it into a back end (the tables, on a shared drive) and a front end (everything else), and give each user their own copy of the front end on their own PC. Make sure everyone can create and delete files in the back-end folder, and use record-level locking.
Usually because a PC lost its connection while writing to the file. Wi-Fi, VPNs, a shared front end and PCs being switched off with the database open are the common causes. Moving the tables into SQL Server removes most of the risk.
It's the lock file Access creates next to the database to track who's using it and what they've locked. It should disappear when the last person closes the database. If it doesn't, someone may still have it open or a PC may have crashed.
Not safely. Reading an Access file across a VPN is slow and risks corruption. Remote desktop to an office PC works as a stopgap. For proper remote use, the system needs to be a web application.
Access falling over with more users?
Tell us what's happening and we'll tell you why.
How many people use it, how the database is set up (if you know), and what errors you're seeing. Screenshots of the error messages help.
What we will cover:
- Whether it's a setup problem you can fix
- Whether SQL Server would sort it
- When it's time to replace it, and roughly what that costs
Rather talk now? Book a call or ring 01474 639 089.
We reply within one working day.
