Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 36 ยท Administration and Security

Compact, Repair and Backup in Access

Keep an Access database healthy. Learn why files bloat, how Compact and Repair works, automatic compact on close, backup strategies, version control for the front end and recovery steps.

An Access database is a single file, which is wonderful for sharing and terrible for neglect. Two habits keep it healthy: compact and repair regularly, and back up before anything risky and on a schedule. This lesson explains what happens inside the file, how to maintain it, and how to recover when something goes wrong.

Why an Access file grows and gets damaged

When you delete records, modify objects or run temporary action queries (for example a make-table query that rebuilds a table every day), Access does not shrink the file. It marks the space as free and leaves gaps, so the file keeps growing until it hits the 2 GB limit of an .accdb (attachments, OLE objects and temporary tables use a lot of it). Fragmentation also slows queries. Separately, a crash, a network drop or a power failure while the file is open can leave it in an inconsistent state, and Access will report corruption the next time you open it.

Signs of trouble: the file is much larger than its data, Access says "database needs to be repaired" or "unrecognized database format", forms or reports vanish or throw odd errors, or MSysCompactError appears as a system table.

Compact and Repair

Database Tools > Compact and Repair Database (or File > Info > Compact & Repair) does two jobs: it rewrites the file contiguously, reclaiming deleted space and resetting some statistics, and it repairs minor corruption. Do it:

  • after deleting lots of data or running big action queries;
  • weekly on busy databases (back ends especially);
  • before making a backup copy or distributing a front end.

Rules: make a backup first, close the database for everyone else (a shared back end must be opened exclusively), and make sure you have free disk space equal to at least the size of the file because Access writes a new copy and then replaces the old one. For a split database compact the back end and the front end separately; the front end swells fast because temporary objects are stored in it (see split databases).

For a hands-off routine, set File > Options > Current Database > Compact on Close. It is convenient for a single-user file, but do not use it on a shared back end: a compact while others are connected fails, and a failure during compaction is exactly when corruption happens. For back ends, schedule a compact at night when nobody is connected.

You can start it from the command line too:

TEXT
"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "D:\Data\Sales_BE.accdb" /compact
"C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "D:\Data\Sales_FE.accdb" /compact "D:\Data\Sales_FE_small.accdb"

The second form compacts into a different file, which is safer because the original stays untouched if something fails. Put the lines in a .bat file and run it with Windows Task Scheduler.

Remember the SQLite counterpart: VACUUM rebuilds a database file and frees unused pages, and you can see the data survive intact:

SQL Stand-in: VACUUM, SQLite's compact

Press Run (or Ctrl + Enter) to see the result here.

Backing up

A backup is a separate copy of the file that you can restore. Two ways to make one:

  1. File > Save As > Back Up Database adds the date to the file name (Sales_2025-03-10.accdb) and works while you are in the file.
  2. Copy the file with Windows or a script. Only copy a closed file: copying an open database can give you a copy that is corrupt.

A sound strategy follows the 3-2-1 rule: 3 copies of the data, on 2 kinds of media, 1 off-site (cloud storage, another office). Keep several generations (daily for a week, weekly for a month, monthly for a year), because corruption can be noticed late and an old backup is useless if it contains the same damage. Test a restore now and then; a backup you have never restored is a hope, not a backup.

Automate it from Access itself with VBA on close, for a single-user file:

VBA
Public Sub BackupBackEnd()
    Dim src As String, dst As String
    src = CurrentProject.Path & "\Sales_BE.accdb"
    dst = CurrentProject.Path & "\Backups\Sales_BE_" & Format(Now, "yyyy-mm-dd_hhnn") & ".accdb"
    If Dir(CurrentProject.Path & "\Backups", vbDirectory) = "" Then MkDir CurrentProject.Path & "\Backups"
    FileCopy src, dst
End Sub

FileCopy fails if the file is open by others, so run it from a scheduled script when nobody is connected. If the data lives on SQL Server instead (see upsizing), use that server's backup tools.

Versioning the front end

Treat the front end like software. Keep a master copy in a read-only folder, distribute copies to users on startup (a small launcher batch file that copies the newest .accde into the user's local folder), and number releases (Sales_FE_v2.4.accde). The data stays in the back end; only the front end is replaced. Never let people edit the only copy.

Pair this with a version table in the back end: a tblVersion row with the minimum front end version allowed, which the front end checks at start-up to refuse to run if it is out of date.

flowchart LR
  A["Master front end<br/>(read-only)"] -->|copy at launch| B["User PC<br/>local FE copy"]
  B -->|linked tables| C[("Shared back end<br/>on server")]
  C -->|nightly| D["Compact"]
  D --> E["Backup copies<br/>(daily/weekly/off-site)"]

Only the back end holds data, so only the back end needs a backup rotation.

When the database is corrupt

  1. Stop everyone from opening it; do not keep trying (each open can make it worse).
  2. Copy the file to a safe place and work only on the copy.
  3. Try Compact and Repair on the copy. On a split database also check the back end separately.
  4. If the file will not open, create a new empty database and use External Data > New Data Source > From Database > Access to import all objects (tables, queries, forms, reports, macros, modules) from the damaged file. Import in groups to isolate the bad object.
  5. If one object fails, import everything else and recreate that object, or restore it from the last good backup.
  6. As a last resort restore the most recent backup and re-enter lost data from other sources (an import file, a paper log).

Preventing corruption is easier than curing it: keep the back end on a reliable wired network (not Wi-Fi, VPN or a flaky cloud-synced folder such as OneDrive), never leave a user with an Access file open for days, split the database, and give every user their own front end copy.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does an Access file keep growing after you delete records?

    Only Compact and Repair rewrites the file and reclaims the free space.
  2. 2. Which is safest for the shared back end?

    A compact needs exclusive access; a failure during compaction while others are connected can corrupt the file.
  3. 3. What does the 3-2-1 backup rule mean?

    Keep three copies on two kinds of media with one stored off-site.
  4. 4. What is the first step when you suspect corruption?

    Preserve the evidence: each open can cause more damage, so work on a copy.

Summary

  • Deleting does not shrink an Access file; Compact and Repair reclaims space and fixes minor corruption (the file limit is 2 GB).
  • Back up first, compact the back end on a schedule, never while others are connected, and keep several backup generations including one off-site.
  • Version the front end and distribute local copies; test your restores.
  • If corruption strikes, work on a copy and import objects into a fresh database.

Next: Security and trusted locations