Access Databases

The Reality

Microsoft Access was designed as a desktop database tool for small-scale use. It was never intended to be a production database for a business. Yet that is exactly what it has become in thousands of organisations. Someone built a database to track orders, manage inventory, schedule jobs, or record client interactions. It worked well enough. More forms were added, more queries, more reports. Over the years, it became the system that runs a department or even an entire business.

Access is not without merit in this role. It offers a rapid development environment where forms, queries, and reports can be built quickly by people who are not professional developers. The learning curve is gentle. For small datasets and single users, it performs adequately. But Access was built for a world of local files and single-user workflows. The moment you need concurrent access, large datasets, remote connectivity, or integration with other systems, its limitations become serious constraints. The 2GB file size limit is well-known. Less visible are the file-level locking issues that corrupt databases when multiple users access them simultaneously over a network, the lack of proper transaction support, and the complete absence of any built-in backup or recovery mechanism.

The Risks of Doing Nothing

• Data corruption. Multi-user Access databases accessed over a network share are prone to corruption. Recovery from a corrupted .mdb or .accdb file is unreliable at best.
• The 2GB ceiling. When an Access database approaches its 2GB limit, performance degrades and eventually the database stops accepting new records. Compacting buys time but does not solve the problem.
• No remote access. Access databases live on a file share or a local drive. Remote workers, mobile access, and multi-site operations cannot be supported without awkward workarounds.
• Single point of failure. The database file is the system. If the file server fails, if the file is accidentally deleted, if the network share becomes unavailable, the business process stops entirely.

How We Approach This

We start by understanding the full scope of what the Access database does. This means documenting every table, relationship, query, form, report, macro, and VBA module. Access databases that have grown organically over years often contain a surprising amount of business logic: in queries, in form event handlers, in VBA code behind buttons. All of it needs to be catalogued before any migration begins.

The data migration itself is usually the simplest part. Access tables map cleanly to relational database tables in SQL Server, PostgreSQL, or MySQL. We script the migration, validate row counts and data integrity, and run it repeatedly in testing before the final cutover. The harder work is rebuilding the forms and reports as a web application, preserving the workflows that people depend on while improving the experience.

For organisations that are not ready for a full migration, we sometimes use an intermediate step: linking the Access front-end to a proper SQL Server backend. This eliminates the file-level locking and corruption risk while keeping the familiar Access forms in place. It is not a permanent solution, but it removes the most dangerous failure mode immediately.

What You End Up With

• Data stored in a proper relational database with backup, recovery, and transaction support
• A web-based application accessible from anywhere, by any number of concurrent users
• No more 2GB limits or file corruption risk
• Business logic documented and implemented in maintainable, testable code
• Reporting that can scale beyond what Access queries can handle

What We Have Seen

We migrated an Access database for a facilities management company that had been tracking maintenance contracts, job scheduling, and engineer assignments for over a decade. The database was 1.7GB and corrupted roughly every six weeks, each time requiring a half-day of recovery work. We moved the data to PostgreSQL, rebuilt the forms as a web application, and delivered a system that handled three times the concurrent users without a single corruption incident. The facilities manager told us it was the first time in years they did not dread Monday mornings.

How Much Time Have You Actually Got?

Access rarely fails on a date. It fails on a threshold. These are the ones that matter, and you can check every one of them yourself this afternoon.

You have years, not months, if: one or two people use it at a time, the file sits on a proper file server, it is backed up somewhere you have actually tested restoring from, and the person who built it still works with you.

You are inside a year if: more than about five people have it open simultaneously, or the file has started needing Compact and Repair regularly. Repeated corruption is not bad luck. It is the concurrency model telling you it has run out of road.

You are close to a wall if: the file is past roughly 1.5GB. Access has a hard 2GB limit per database file, and there is no way round it other than splitting the data or moving it. Check the file size now and check it again in a month. The gradient matters more than the number.

You have a hard deadline if: anyone has proposed moving the file to SharePoint, OneDrive or a synced cloud folder. Sync clients and Access’s file-locking do not coexist; that combination corrupts databases. If a migration to cloud storage is on someone’s roadmap, that is your date.

What Breaks First

1. Concurrency. Two people save at the same moment over a slow link and the file corrupts. This is the single most common way Access databases die.
2. The 2GB ceiling, which arrives without warning and stops writes dead.
3. The author leaving, taking with them the only understanding of the VBA behind the forms.
4. Reporting, when someone wants the data joined to something else and there is no way to get at it cleanly.
5. The Access application itself, last. It is still a supported part of Microsoft 365 and is rarely the actual problem.

Check These Six Things This Week

• What is the file size today? Right-click, Properties. Write it down and check again in four weeks.
• Is it split into a front-end (forms and code) and a back-end (tables)? An unsplit database shared by several people is the highest-risk configuration there is.
• How many people have it open at once at the busiest point of the week?
• Where does it physically live: a file server, someone’s desktop, or a synced cloud folder?
• When did you last restore from a backup to prove the backup works? Not “do we have backups”. Have you tested one?
• Who can change the VBA? If nobody can, the application is already frozen and you simply have not hit the moment that matters yet.

When You Genuinely Don’t Need To Act

A split Access database, on a file server, with a handful of users, well under the size limit, that somebody understands, is a perfectly reasonable piece of business software. It is cheap, it works, and replacing it would cost far more than it returns. Plenty of good businesses run on exactly that and should carry on doing so.

Act when a threshold above moves, not because Access has a reputation.

Your Access Database Deserves a Proper Home

If your business runs on an Access database, you already know the risks even if you have been living with them. Migration does not have to mean losing what works. It means putting your data and your workflows on a foundation that can actually support them. Let us assess what you have and show you the path forward.

For businesses in a similar position with spreadsheet-led processes, Replacing Spreadsheet-Led Operations covers how we approach that closely related problem. The Legacy Systems Overview sets out the full range of migration and modernisation approaches we use.