Split database slows to a crawl and loses backups
A 12-person operations team runs a split Access database. The front end was copied to C:\Apps\Ops.accdb on every workstation; the back end sits at \fs01\shared\Ops_Data.accdb. Over three weeks the main entry form went from about one second to nine to open. Someone occasionally opens the back end directly, and afterwards other users get 'the database has been placed in a state by user Admin on machine WS-07 that prevents it from being opened or locked.' The back-end file has grown from 400 MB to 1.1 GB even though enrollment records rose by only about 2,000. Last week's restore lost two days of entries because the nightly file copy runs at 01:00 while the warehouse crew is still keying orders. Two users also report #Deleted appearing in a few order records that still exist in the table. Leadership wants file size, speed and recoverability addressed before the audit.
1. Diagnose the slowdown and the lock message, and describe the front-end and back-end changes you would make. Justify each change.
Read the worked answer
Linked tables do not hold a connection open. Every time the front end touches a table it is not already connected to, Access opens the back-end file, reads its schema, and closes it again. With a dozen linked tables and a dozen users that churn produces exactly the multi-second delay you describe, and the schema read can collide with a user who has the back end open, producing the lock message.
Fix in this order:
- Keep a persistent connection - open one bound form on a linked table at startup and leave it open, or hold a recordset open in a hidden form's Load event. One open connection serves all linked tables.
- Turn off Name AutoCorrect on both files; it creates hidden dependency queries and grows the file.
- Compact and repair the back end with everyone out, then compact each front end.
- Remove direct shortcuts to the back end from the share. Users get only the front end, redeployed from a master copy whenever it changes.
The 700 MB of growth is mostly debris from the churn and repeated schema reads, not data, which is why compaction reclaims most of it.
2. The nightly file copy is not a valid backup. Propose a backup approach, and state whether Compact on Close should be enabled on the shared back end.
Read the worked answer
A file copy of an .accdb while it is being written is not a backup. Access writes pages on its own schedule and holds an .laccdb lock file, so a copy taken mid-session can be internally inconsistent; it may open, which is worse than failing outright.
Approach:
- Schedule the copy for a window when nobody is connected, and have the job check that the .laccdb is absent before copying.
- Better, run the copy from a maintenance front end that opens the back end exclusively, performs the copy, then compacts and releases it.
- Keep several generations rather than one file, and test a restore into a scratch folder each month; an untested backup is a guess.
Compact on Close must be off on the back end. The property fires for whoever closes the file last. On a share that means an unexpected exclusive compaction while other users still hold connections, which is a documented corruption and lock vector. Compact on Close on each user's front end is harmless and worth keeping. Compacting the back end should be a scheduled maintenance task run with exclusive access, not a side effect of someone closing a form.
3. Users see #Deleted in a handful of order records that still exist in the table. How would you diagnose and remediate this?
Read the worked answer
#Deleted means the form's recordset can no longer resolve the row it is positioned on, not that the row is gone. Work through the causes in order of likelihood:
- The row really was deleted or re-keyed by another user while the form sat open for hours. Confirm by opening the table directly and searching the primary key.
- Index or page damage. Run Compact and Repair on the back end; if the symptom survives, check the table's indexes for a damaged unique index, and try rebuilding it.
- Non-updatable record source. If the form is bound to a query joining one-to-many tables, Access can lose the bookmark after an edit. Bind the form to a single table, or to a query that is updatable, and set Recordset Type to Dynaset.
- Stale data. Add a Refresh button, or requery on the form's Timer, so a long-open form re-syncs.
Handle Form_Error for error 3167 ('Record is deleted') by issuing Me.Requery instead of letting the default dialog appear. That turns a confusing #Deleted row into a clean refresh, and the underlying data is untouched.