Microsoft Access Database Resources: When to Fix, Split, or Replace Your System
By Greg Nowak. Updated 22 July 2026.
Microsoft Access databases rarely arrive with a tidy specification. More often, someone built a useful tool years ago, the original developer moved on, and the file gradually became responsible for quotes, bookings, inventory, reporting, casework, or client administration.
If that sounds familiar, the first question is not whether Access is old. It is whether the system remains dependable enough for the job it performs. A modest database with clear ownership may need only maintenance and documentation. A shared file supporting daily operations may need structural work—or a planned migration before it becomes an emergency.
Begin With the Business Process, Not the Tables
Before changing forms, queries, or VBA, establish what the database does in operational terms. Who uses it? Which deadlines depend on it? Where does its data come from, and where does it go? What would staff do if it were unavailable for a day?
Record the answers in a short system brief. Include the file locations, Access version, approximate user count, main forms and reports, linked data sources, imports, exports, scheduled tasks, and known problems. Ask users to identify manual workarounds too; these often reveal missing functionality or business rules that are invisible in the database design.
Make a recoverable copy before investigating. For a shared back end, ensure everyone is out of the database before copying or maintaining it. A backup that has never been restored should be treated as an assumption, not proof of recovery.
Access Has Limits, but the Headline Numbers Are Misleading
Microsoft currently specifies a maximum database size of 2GB, less the space required by system objects, and a maximum of 255 concurrent users. These are technical ceilings, not sensible design targets. Performance and reliability can become unacceptable much earlier depending on query design, record locking, network quality, file growth, attachments, and how frequently users write data.
Access remains a reasonable fit for a contained Windows desktop workflow with a modest user group, local-network access, forms, reports, and frequent business-led changes. It is a weaker fit for public-facing services, browser or mobile access, geographically distributed teams, strict permission requirements, heavy integrations, or processes that cannot tolerate file-level failure.
| What you find | Likely next move | Priority |
|---|---|---|
| One or two users, stable process, reliable file | Document and maintain it | Backups, ownership, change log |
| Several LAN users opening one shared file | Split the database | Local front end for each user; shared back end |
| Slow queries or recurring file growth | Optimise before replacing | Indexes, joins, data types, archived data |
| Remote users over a WAN, VPN, or Azure file share | Change the architecture | SQL Server, Azure SQL, Dataverse, or Remote Desktop |
| External access, complex permissions, or critical uptime | Plan a managed replacement | Preserve rules, reports, and migration controls |
For a Shared Database, Split the Front End and Back End
For several users on a reliable local area network, splitting is often the most valuable first improvement. The back-end file holds the shared tables. Each user receives a local front-end copy containing forms, reports, queries, macros, VBA, and links to those tables. This limits front-end corruption to an individual copy and makes application updates easier to control.
Start from a backup copy on a local drive, with users out of the live database. In Access, use Database Tools > Access Database to start the Database Splitter Wizard. Place the resulting back end in a secured network folder and link it using a UNC path such as \\server\share\system_be.accdb, rather than a drive letter that may differ between computers. Test the result with representative users before deployment.
Do not stretch this design across a wide area network merely because a VPN or cloud file share makes the folder visible. Microsoft explicitly warns against running a split Access database over a WAN or Azure file share because it can be slow and may become corrupt. In that situation, keep the Access interface if it remains useful but move the tables to SQL Server, Azure SQL, Dataverse, or another appropriate service—or run the complete application through Remote Desktop Services.
Compact and Repair Is Maintenance, Not a Recovery Plan
Access files retain unused space as data and objects change. File > Info > Compact & Repair Database can reclaim that space and may improve performance. It can also help repair certain forms of damage.
Back up immediately beforehand and obtain exclusive access to the file. Microsoft notes that repair can truncate damaged table data, so the output must be checked. Automatic “Compact on Close” can also interrupt availability in a multi-user database; use a controlled maintenance window instead when the system is shared.
If compacting becomes a frequent response to crashes or corruption, investigate the cause. Network interruptions, every user opening the same front-end file, failing hardware, unchecked file growth, and problematic VBA will not be solved by a recurring repair ritual.
Modernise in Stages, Without Losing the Business Rules
A full rebuild is not automatically the responsible choice. Start with an inventory of tables, relationships, queries, forms, reports, macros, modules, linked sources, credentials, hard-coded paths, and recurring exports. Then trace the rules hidden in validation settings, calculated fields, queries, VBA, and staff habits.
The resulting options are usually:
- Stabilise: fix defects, split the database, establish backups, and document support ownership.
- Improve: simplify forms, add indexes, remove duplicate entry, strengthen validation, and automate controlled imports or exports.
- Upsize: move tables to SQL Server or Azure SQL while retaining Access as the front end.
- Replace: rebuild the workflow when browser access, integrations, security, scale, or resilience justify it.
Microsoft’s SQL Server Migration Assistant can assess Access objects and migrate schemas and data, but it does not remove the need to test queries, forms, reports, VBA, permissions, and user workflows. Treat migration as a business-system project rather than a file conversion.
What to Bring to an Access Database Review
A consultant can give a more useful answer when provided with a safe copy, screenshots of important workflows, user and location counts, sample outputs, current backup arrangements, known errors, and the deadline driving the work. Sensitive production data can be discussed before it is transferred.
If your Access system is valuable but fragile, Greg can help establish what is safe to keep, what needs immediate attention, and whether improvement or replacement offers the better business case. The first step is a focused review of the workflow, risks, and realistic options—not a predetermined rebuild.
Related on GrN.dk
- AI automations need a spend dashboard before the first runaway bill
- Search Console Can See Social Posts—Your Reports Need a New Map
- Structured Outputs Make Intake Automation a Schema Design Job
Need help with this kind of work?
Talk to Greg about your Access database Get in touch with Greg.
Sources
- Log in to post comments