Manage Databases: Compact, Split, Encrypt, and Configure Access
The Manage databases domain of the MOS Access Expert exam tests maintenance and configuration commands rather than object design: compacting and repairing, splitting, encryption, startup options, and Trust Center policy.
Compact and Repair Database
Run it from Database Tools > Compact and Repair Database. Compacting rewrites the file into a fresh copy, removing deleted-record remnants, temporary objects, and unused space, and it attempts to repair minor corruption. The file must be opened exclusive - if another user has it open, the command fails with a locking message. File > Options > Current Database > Compact on Close automates the process, but it forces an exclusive open every time, which is inappropriate for a back-end that several people share.
Splitting a database
Database Tools > Move Data > Access Database creates a back-end holding only tables and a front-end holding queries, forms, reports, macros, and VBA, with linked tables pointing at the back-end. Relink with Linked Table Manager when the back-end moves.
Encryption and Trust Center
File > Info > Encrypt with Password applies file-level encryption; the file must be opened exclusively to set or remove the password. The Trust Center (File > Options > Trust Center > Trust Center Settings) governs macro settings, trusted locations, and the message bar. A database inside a trusted location opens with code enabled and no message bar.
Worked example
Twelve people share one database on a file server and saves have slowed. Reasoning: split the file, place the back-end on the server, give each user a copy of the front-end, and compact the back-end during a maintenance window. Compacting alone would not cut network contention, and leaving the front-end on the share recreates it.
Traps
- Treating Compact and Repair as a backup - copy the file first.
- Turning on Compact on Close for a shared back-end.
- Confusing encryption (a password prompt at open) with the macro message bar.