Microsoft Access to SQL Server Migration: Why Your Access DB Will Break at 2GB

Microsoft Access has a hard 2GB file size limit. Not a recommendation. Not a best practice guideline. A hard ceiling baked into the Jet and ACE database engines that Microsoft has never raised since Access 97. When your MDB or ACCDB file hits 2GB, Access does not slow down gracefully or warn you with a friendly dialog box. It corrupts. Tables become unreadable. Queries return garbage. Compact and Repair — the tool every Access developer knows by heart — fails because the file is structurally damaged beyond recovery. If you do not have a backup from before the corruption event, your data is gone. We have received calls from companies that lost months of operational data because a single Access database grew past the 2GB threshold on a Friday afternoon and nobody noticed until Monday morning.

The 2GB limit is the catastrophic failure mode, but Access databases start degrading long before they hit that wall. Performance drops noticeably around 500MB–1GB. Queries that used to return in under a second take 10–15 seconds. Forms take 5–8 seconds to load. Report generation that used to complete during a coffee break now runs for 45 minutes. The Jet/ACE engine was designed for single-user desktop databases in the mid-1990s. It has no query optimizer worth mentioning, no execution plan caching, no parallel query execution, and no ability to use more than a fraction of available system memory. Every query performs a full table scan unless you have manually created indexes, and even indexed queries degrade as fragmentation increases because the engine has no background maintenance processes.

Multi-user Access is where the real horror stories live. Microsoft's documentation says Access supports up to 255 concurrent connections. In practice, anything above 5–10 simultaneous users causes record locking conflicts, database corruption, and split-database architectures that create their own maintenance nightmares. The standard workaround — splitting the database into a backend MDB on a network share and frontend MDB copies on each workstation — introduces a new failure mode: linked tables break when the network path changes, when the file server is rebooted, when someone maps a drive letter differently, or when Windows updates the SMB protocol version. Every Access developer has spent hours troubleshooting 'Could not find file' and 'Record is locked by user ADMIN on machine' errors that bring entire departments to a standstill.

Access has no real security model. The legacy MDW workgroup security file was deprecated in Access 2007 and was trivially bypassable even when it worked. Any user with file-system access to the MDB can open it directly, bypass your login form entirely, and view or modify any table. There is no row-level security, no column-level security, no audit trail, no encryption at rest in any meaningful sense, and no way to prevent a user from simply copying the entire database file to a USB drive. For companies in regulated industries — healthcare, finance, insurance, manufacturing with ITAR requirements — running business data in Access is a compliance violation waiting to be discovered during an audit.

Then there is the VBA problem. Most Access databases that have been in production for more than a few years have accumulated thousands of lines of VBA code behind forms, reports, and modules. This code typically has no version control, no documentation, no error handling, and dependencies on specific Windows registry settings, ODBC DSNs, or COM libraries that may or may not be installed on any given workstation. The developer who wrote the original code left the company in 2011. The person who modified it in 2017 no longer remembers what they changed. Nobody understands the full dependency chain. The business runs on code that nobody can confidently modify, and every Windows update or Office patch is a dice roll on whether something critical will break.