What Compact and repair does to a Base by Zaweba database

Databases that are used every day collect small untidiness over time. Records are deleted and leave empty space behind. A lookup points at a record that no longer exists. An index gets out of step. None of it is dramatic, but it adds up, and now and then something odd shows on a form or a report. Database > Compact and repair in Base by Zaweba is the one command that checks for all of it and puts it right.

It starts by making a backup copy, so whatever it finds and changes, you still have the file as it was.

The checks it runs

First comes SQLite's own integrity check. Each Base database is a single .obase file with SQLite inside, and SQLite can examine its own pages and structures for damage. That is the deepest level of checking, looking at the file itself rather than your records.

Next, Base looks for records that point at records that are gone. If an order's customer lookup leads to a customer who has been removed, that link is broken. Where the lookup is optional, Base empties it, so the order simply has no customer set. Where the lookup is required, Base lists the record instead of guessing, so you can decide what it should point to.

Then it checks Base's own design tables. Those hold the things that make a file more than a set of tables: relationships, the diagram, forms, buttons and the start-up form. If any of those refer to something that has been renamed or removed, Base puts them right.

Tidying and shrinking

Finally, Base rebuilds the indexes and compacts the file. Rebuilding indexes means searching and sorting work from fresh, accurate lists. Compacting reclaims the space left by deleted records, so a database that has had a big clear out gets smaller on disk. That makes it quicker to copy, to back up and to send to someone.

When to run it

Run it after big changes: a large delete query, an import that you later removed, or a long session of redesigning tables and forms. Run it when something looks wrong, such as a form that complains about a missing record or a report that won't open. And run it now and then as routine, perhaps monthly for a busy database, much as you might tidy a filing cabinet.

It is also a good step before handing a file to someone else, or before exporting the database as a website, so what you pass on starts clean.

Reading the results

Pay attention to the list of required lookups that point nowhere. Those are records Base won't fix on its own, because only you know the right answer. Open each one and choose the correct link, or delete the record if it shouldn't exist. Then run Compact and repair again to confirm everything is clear.

If the integrity check reports damage it can't put right, go to your backups. File > Backup copies lists the automatic daily copies, and one from before the problem started is usually the quickest way back.

What it can't do

Compact and repair fixes the structure, not the meaning. It can't tell that a price was typed wrongly or that two customers are really the same person. For those, validation rules, unique indexes and the occasional checking query are the right tools.

Used regularly, though, it keeps a Base file in good order, and the backup it takes first means there is no reason to be nervous about running it.