Access, Excel: leaving a homemade tool without losing the history

An Access database built fifteen or twenty years ago, by a single person, runs the whole business of an insurance broker: contracts, renewals, claims history. The short answer to "how do we leave it without losing everything" fits in one sentence: you sort the history before you switch off the old tool, never after. The rest of this article explains how to do that sort without it taking six months.

What is actually inside a database built over the years#

The fear is always the same: "if we touch it, we lose fifteen years." It grows with the age of the tool, but it almost never matches what the database really contains.

A database that has lived for fifteen years accumulates fields added for a one-off need and never removed, tables created for a test that lasted a month, columns half filled in because two people never entered them the same way. At an insurance broker, the useful history usually fits in five or six tables: clients, contracts, renewals, claims. Everything else was bolted on along the way, and nobody uses it anymore.

The first move is not to migrate: it's to look, table by table, at the date of the last row added. A table that hasn't received anything in five years is not history to carry over. It's an archive to keep as is, on the side.

Sorting before migrating: what counts, what doesn't#

Three kinds of things hide inside a homemade tool, and they don't get the same treatment.

The second row is the one companies forget most often: old data can stay searchable without being actively migrated. Keeping a read-only copy of the old tool costs a licence or a simple file, and avoids moving thousands of rows nobody will ever look for.

The method, in three moves#

1. Export table by table, not all at once. Each table has its own use, its own freshness, and its own inconsistencies. Handling them one at a time catches duplicates and dead columns before they land in the new application.
2. Keep a read-only copy of the old tool. Nobody should need to reopen it to work, but someone should be able to search an old row the day a client calls back about a 2016 claim.
3. Have the person who knows the database sign off. Before switching it off, ask them to confirm that no active table was left out, and to say out loud the rules they apply without thinking about them.

At a mid-sized insurance broker, an eighteen-year-old Access database held forty tables. Six were still fed every week; the other thirty-four hadn't received a row in at least five years. Carrying over all forty tables would have taken about three weeks: half a day per table to understand its use, clean up duplicates, and map it to the new application. Limiting the active transfer to the six tables still in use brings the work down to four days. The other thirty-four stay searchable in the read-only copy, until someone confirms nobody ever looks for them.

What must never be lost: what is written nowhere#

When I go on holiday, I'm the one who handles all the paperwork.

This sentence comes up often at companies built around a homemade tool. It isn't about a missing field in the database: it's about a rule nobody ever wrote down, because the person applying it never had to write it down for themselves.

Before switching off the old tool, those rules need to be said out loud and recorded: why a given client has a special rate, why an exception has existed since a certain year, why a file is always handled in a specific order. This isn't data you export. It's knowledge you document once, before it leaves with the person who holds it.

What changes with an application built for the company#

An application built for a single company starts by carrying over the live history, table by table, with the right fields. The unwritten rules, once surfaced during the audit, become screens or alerts: a client's special rate shows up directly on their record, instead of staying in one person's memory.

The old tool stays accessible, read-only, for as long as it takes everyone to trust the new one. Nobody switches over in one go on the sole promise that "everything was carried over": the safety copy stays in place until nobody needs it anymore.

Leaving a homemade tool: the key takeaways#

The real risk is almost never losing data: an old database holds far less active history than people fear, and what's dormant can stay searchable without being migrated. The real risk is losing the rules nobody ever wrote down, because only one person knows them.

Sort before migrating, keep the old tool read-only, and get the unwritten rules said out loud: these three moves are enough to leave a homemade tool without losing fifteen years of memory with it. The grid to decide which tool to keep for what helps decide what, in the old tool, is worth replacing first. And an old ERP isn't always the problem: sometimes it's the homemade tool sitting next to it that carries the real risk. What an application built for a single company contains starts from exactly this kind of history, not from a blank screen.