Ghost Tracker: A Free Tool to Find Broken Relationships in Microsoft Access

Find the hidden relationships and orphan records haunting your database, without changing a thing.

Every Access database that has grown over the years has a few ghosts. A relationship that was never defined. A foreign key with no rules protecting it. Order details pointing to orders that were deleted long ago. You can't see them in the Relationships window, but they let bad data slip in, and they cause trouble the day you need to change a key field.

Ghost Tracker is a free VBA module from TechMentors that scans your Access database and lists every one of those problems in a single results table. It only reports. It never changes, deletes, or fixes anything, so it's safe to run on any database.

What Ghost Tracker Finds

• Unenforced relationships. A relationship exists, but Enforce Referential Integrity is turned off, so nothing stops bad data.
• Hidden relationships. A field looks like a foreign key (its name matches another table's primary key), but no relationship was ever defined.
• Mismatched fields. A foreign key whose data type or field size doesn't match its primary key, so values can be cut off or never match.
• Orphan records. Child records that point to a parent record that doesn't exist, with a count for each relationship.

Why It's Safe to Run

• Report-only. It reads your tables and writes its findings to one results table, tblGhostTrackerResult. Nothing else is touched.
• Works with split databases. Run it from the front end and it offers to scan each back-end file. Back ends are opened read-only, so other people can keep working while you scan.
• Easy to remove. When you're done, remove the module and delete the results table.

What's in the Download

• modGhostTracker.bas: the Ghost Tracker scan. Import it into any Access database and run it.
• Hollow Creek Costume Co.accdb: a small sample database with ghosts planted on purpose, so you can see exactly what the results look like before scanning your own.
• modBuildGhostSample.bas: builds the sample database from scratch, if you'd rather create it yourself.
• TechMentors Ghost Tracker Quick Start Guide.pdf: step-by-step instructions, how to read the results, and how to fix the ghosts you find.

Requirements: Microsoft Access 2016 or later, including Access in Microsoft 365. No add-ins or extra references are needed.

Tip: Windows blocks macros in files downloaded from the internet. Before extracting the zip, right-click it, choose Properties, check Unblock, and click OK. The Quick Start Guide walks you through it.

Learn More About the Ghosts

• Orphan Records: The Ghosts Hiding in Your Database explains what orphan records are and how referential integrity keeps them out.
• Case Study: Busting the Ghosts in a College's Access Database shows how this kind of scan uncovered more than ten hidden relationships before a key field was widened.

🎄 Coming this December: a point-and-click version of the Ghost Tracker, with buttons to scan, review, print, and export your results.

Found more ghosts than you expected? Reach out to us. We'd be glad to help you clean things up safely.

Ghost Tracker and its Quick Start Guide are provided free of charge, "as is," without warranty of any kind. Use them at your own risk, and always back up your database first. TechMentors LLC is not liable for any data loss or damage resulting from their use. You may use and modify Ghost Tracker in your own databases; please don't resell it or redistribute it without permission.