Microsoft Access: from shared .mdb to a real system
That departmental Access database quietly became business-critical years ago. We move it to SQL Server and .NET in phases — starting with the step that ends file corruption forever.
How Access apps get stuck
Every Access application we meet followed the same arc: built by a capable power user for one team, then grown by accretion — more forms, more VBA, more linked spreadsheets — until a company of fifty depends on a file format designed for a workgroup of five:
- The 2 GB file limit approaches, and archiving records to a second MDB has already begun.
- Concurrent users corrupt the file; Compact & Repair is a scheduled ritual.
- "The database is slow" means every query drags the whole file across the network.
- Security is a shared password, or an .mdw file nobody remembers.
- The author left; the VBA behind form events is undocumented and interlocking.
- Management wants it on the web, on tablets, integrated with the ERP — none of which Access will ever do well.
The phased path out
-
Assessment
Inventory of tables, queries, forms, reports, macros and VBA; usage measurement; dependency map (linked spreadsheets, mail merges, other MDBs). Risk-ranked roadmap, fixed price.
-
Data to SQL Server — corruption ends here
Schema translated with the traps handled: Jet date semantics, Yes/No fields with nulls, autonumber gaps and seeds, Unicode compression artifacts, attachment and OLE fields extracted to proper storage. The Access front-end keeps running via linked tables — users notice nothing except stability.
-
Logic out of VBA
Rules migrate to where they belong: constraints and views in SQL, services in .NET. The parity harness runs old and new side by side on real cases until outputs match.
-
Front-end rebuilt by usage, not by inventory
The forms people live in become a web application (.NET 8); the long tail of once-a-year forms is often replaced by two reports and a query screen. Access retires when nothing opens it for a full business cycle.
Related
Access files often arrive accompanied by DBF exports, ancient dBase links or a FoxPro sibling system — see dBase/DBF data migration and FoxPro to .NET. The decision framework in rewrite vs convert vs wrap applies to Access word for word.
Frequently asked questions
Access still ships with Microsoft 365. Why would we migrate?
Access the product is alive; your Access application is still a single .mdb/.accdb file on a network share with a 2 GB ceiling, a corruption-prone Jet/ACE engine under concurrent use, VBA logic nobody dares touch, and no meaningful path to web, mobile, audit trails or real security. Microsoft’s own guidance for growing Access apps is to move the data to SQL Server — we take that to its logical end.
What does "the database keeps corrupting" actually mean?
Jet/ACE is a file-based engine: every user’s machine writes directly into the shared file over SMB. A dropped Wi-Fi connection or a laptop going to sleep mid-write can leave the file inconsistent — hence the ritual of Compact & Repair and the lost mornings. A client/server backend (SQL Server) eliminates the failure mode entirely; that alone justifies phase one of most Access migrations.
Can we keep the Access forms and just move the data?
Yes — that is often the right first phase. Tables move to SQL Server (we handle type mismatches, date semantics, autonumber seeds, and the query rewrites that linked tables force on you), and the familiar Access front-end keeps working via linked tables with corruption gone and multi-user behavior fixed. Rebuilding the front-end for web comes second, prioritized by which forms people actually use.
What happens to thousands of lines of VBA?
It gets read, mapped and triaged — typically into thirds: logic that belongs in the database (constraints, views, stored procedures), logic that belongs in the new application layer, and dead code. The parity harness pins down current behavior first, so the rewrite is checked against reality rather than against what the VBA "looks like it does".
We have Access 97 / 2003-era MDBs that current Access cannot even open. Dead end?
No. Legacy Jet formats, lost workgroup security files (.mdw), even password-"protected" MDBs are all recoverable situations we deal with routinely. The data and structure come out cleanly with the right tooling regardless of what current Office thinks of the file.
Start with a fixed-price Legacy Assessment
One to two weeks. You get a full inventory of the application and its data, a risk map (what breaks first, what is undocumented, what nobody can rebuild), a data-quality report and a migration roadmap with honest effort estimates — a document you can execute with us or with any other team. Fixed price, agreed before we start.