Case Study: Busting the Ghosts in a College's Access Database

A simple request to widen one field uncovered a database full of hidden relationships.

To protect our client's privacy, "Sagebrush Valley Technical College" is not the college's real name. The details of the project are real.

Some of the scariest stories start with a perfectly ordinary request.

Sagebrush Valley Technical College uses a Microsoft Access database to keep track of the companies it works with. Each company is identified by a short company code, and that code is stored in a five-character text field. The college needed room for longer codes, so the request was simple: increase the company code from 5 characters to 10.

On the surface, that's a two-minute job. Open the table, change the field size from 5 to 10, and save. But in a relational database, a primary key is never alone. Somewhere, other tables are holding that same code to show which company each record belongs to. If you widen the key and miss even one of those related fields, the longer codes won't fit there, and the connection between the tables quietly breaks.

So before changing anything, we went looking for every field related to the company code. That's when we found the ghosts.

The Ghosts in the Machine

The database was split: a back-end file holding the tables, and a front-end file holding the forms, queries, and reports that staff use every day. That's a smart, common design. But when we opened the back end's Relationships window, it told only part of the story.

Throughout the tables were fields that clearly held a company code and clearly pointed back to the company table, but no relationship had ever been defined for them. They looked like ordinary fields. They were really relationships in disguise. All told, we found more than ten of these hidden relationships.

Hidden relationships are dangerous for two reasons:

• Nothing protects them. Without a defined relationship, Access can't enforce referential integrity, so nothing stops a record from pointing to a company that doesn't exist. That's how orphan records are born.
• Nothing reveals them. When you need to change a key, as we did here, the Relationships window can't warn you about fields it doesn't know exist. You either find them by hand, or you find them later when something breaks.

The good news? The college had been fortunate. Despite all those unprotected relationships, we found at most one orphan record. The ghosts were everywhere, but they hadn't done much haunting yet.

How We Busted the Ghosts

1. Back up both files first

Before touching anything, we made copies of both the back end and the front end. In a split database, the table changes happen in the back end, so that's the file that matters most, but backing up both makes it easy to roll everything back if needed.

Outcome: Every later step could be done with confidence, because nothing was unrecoverable.

2. Let a ghost tracker do the searching

Checking every field in every table by hand is slow, and it's easy to miss one. Instead, we used a custom VBA module that scanned the entire back end automatically. It looked for:

• Relationships that existed but didn't enforce referential integrity.
• Fields whose names matched another table's primary key but weren't part of any relationship: the hidden relationships.
• Records in those fields that pointed to a parent that didn't exist: the orphans.
• Related fields whose data type or field size didn't match the primary key.

The last check was especially important for this project. Every related field still at 5 characters would have to grow to 10 along with the key.

Outcome: In minutes, we had a complete list of every field that needed attention, instead of hoping we'd spotted them all.

3. Clean up before locking the door

Access won't enforce referential integrity on a relationship while orphan records exist, so the cleanup had to come first. With at most one orphan to deal with, this step was quick: we reviewed what the scan found and fixed what needed fixing.

Outcome: The data was clean and ready for the rules that would keep it that way.

4. Widen the key and every related field together

With the full list in hand, we increased the company code from 5 to 10 characters in the company table, and then made the same change to every related field the scan had found. A primary key and its foreign keys should always match in data type and field size, so they have to change as a set.

There's a quiet irony here. If those relationships had been defined, Access would have refused to let us change the key's field size until we dealt with them, which is a helpful reminder of exactly what's connected. Because they were hidden, there was no warning at all. Without the scan, it would have been easy to widen the key, miss a few related fields, and never know until longer codes started causing trouble.

Outcome: Every table could hold the new, longer company codes, with nothing left behind.

5. Bring the ghosts into the light

Finally, in the back end's Relationships window, we created a proper relationship for every hidden one and turned on Enforce Referential Integrity for each. The ghosts weren't just removed; they became real, visible, protected relationships.

Outcome: Access now prevents orphan records on its own, and the next time a key needs to change, the Relationships window shows exactly what's connected.

The Result

The college got what it asked for: company codes with room to grow. But it also got something more valuable that it hadn't asked for: a database whose structure finally matches how the data is really used. Every relationship is visible, every key and related field agree, and the database itself guards against orphan records going forward.

Lessons for Your Own Database

• Before changing a key, find everything connected to it. The Relationships window only shows the relationships someone remembered to create.
• Change keys and related fields together. Same data type, same field size, every time.
• Hidden relationships are common. Databases that grow over years, built by several people, almost always have a few.
• Clean first, then enforce. Referential integrity can't be turned on while orphans exist, and that's a feature, not a bug.
• In a split database, scan the back end. That's where the tables and their relationships live.

Ran the Ghost Tracker and found more than you expected? Reach out to us. We'd be glad to help you clean things up safely.