Distribution: SQL upsize at 1.9 GB without changing Access screens
The back-end .accdb hit 1.9 GB and Compact and Repair was a weekly ritual. Tables moved to SQL Server; staff kept the same Access forms. Cutover ran on a Saturday with a rollback plan.
Anonymized scenario · Representative of projects we deliver weekly
Example
Before and after: large table performance
Before
Opening the 400k-row order history table took 45–90 seconds. Compact and Repair ran every Friday. IT feared a corruption event during month-end append.
After
Same Access order-entry form; tables on SQL Server. Open times under 5 seconds on indexed paths. Nightly SQL backup with tested restore replaced manual file copies.
- Back-end size at start
- 1.9 GB .accdb
- Cutover window
- Saturday maintenance
- Users
- 28 across two sites
Upsize included hot-query indexes, staging row-count validation, and a two-week archived .accdb rollback file kept read-only on the file server.
Challenge
Problem
Twenty-eight users across two sites linked to one back-end file. Performance slowed on large tables, and IT worried about corruption during network blips. Leadership wanted stability before approving a full web rewrite.
Solution
Approach
We upsized tables to SQL Server, re-linked the front-end, indexed hot query paths, and validated row counts and relationships in staging. Users trained on a single "open this front-end only" shortcut. Production cutover used a maintenance window with the old .accdb archived, not deleted, for two weeks.
Outcomes
Results
- Open and save times improved for large order and inventory tables
- Scheduled SQL backups and point-in-time restore replaced file-copy habits
- Same Access UI reduced training cost versus an immediate web replacement
- Written roadmap for web conversion of high-traffic forms in year two