The Problem
A mid-size manufacturing company relied on a Microsoft Access database for daily inventory tracking across their entire operation. Fifteen employees used the system simultaneously every day to log materials, track work orders, and generate inventory reports. The database had grown organically over several years without proper architecture, and it was falling apart.
The database crashed multiple times per day. Each crash corrupted data, forcing staff to re-enter information they had already logged. Some days, employees lost hours of work. Inventory counts became unreliable because records disappeared or duplicated during crashes. Management could not trust the numbers coming out of the system.
The IT team had tried quick fixes, compacting and repairing, splitting the database, increasing timeouts, but nothing stuck. The root cause was structural: the database was never designed for 15 concurrent users. Queries ran against local tables with no optimization, forms locked entire tables instead of individual records, and there was no error handling to recover gracefully from conflicts.
The Solution
I started with a full audit of the existing database: table structure, relationships, queries, forms, and VBA code. I identified the specific bottlenecks causing crashes, primarily table-level locking, unindexed queries on large tables, and missing error handling in multi-user scenarios.
I rebuilt the database with a proper split architecture: a shared backend on the network server holding all data tables, and individual frontend files on each user's machine containing forms, queries, and reports. I rewrote the critical queries to use proper indexing and optimized joins. I converted all forms to use record-level locking instead of table-level locking, so multiple users could work simultaneously without conflicts.
I added full error handling throughout the VBA code so that when a conflict does occur, the system handles it gracefully, saving the user's work, retrying the operation, and logging the issue, instead of crashing. I also built an automated backup routine that runs every 4 hours during business hours, so even in a worst-case scenario, data loss is minimal.
The Outcome
The database has run without a single crash for over 8 months. All 15 users work simultaneously without conflicts or data loss. The team saves approximately 10 hours per week that was previously spent re-entering lost data and troubleshooting crashes. Inventory accuracy improved from roughly 85% to over 99%, which reduced over-ordering and stockouts.
“Robert Terry rebuilt our multi-user Access database. Zero crashes in 8 months, and we save about 10 hours every week we used to spend re-entering lost data.”