Orphan child records (1 Viewer)

[tommac]

As a volunteer I built and maintain an Access database for a charity. It's front end back end configuration and the back end is stored on their office server. Users connect via a VPN and Microsoft remote desktop.
Among other functions the database records 1 to 1 contacts with clients and also records contacts at group events.
This is handled via a people table connected to a contacts table via a people/contacts table.

The problem is that I have discovered that in the back end file, the contacts people table is no longer connected to the contacts table. Presumably this was caused by the loss of the remote connection during transactions. I have reconnected the tables but can not enforce referential integrity because of the presence of 600 orphan records in the contacts people table. As there are approximately 40,000 records in this table it's not the end of the world as financial data is not involved.

I would like to know what is considered best practice in this situation. Should I:

Delete the orphan records to enable referential integrity.

Give them all a dummy contact id to enable referential integrity.

Remove them from the contacts people table and save them in a separate archive table for future enquiry.

Or something else.

All suggestions gratefully received.

 

[sonic8]

The problem is that I have discovered that in the back end file, the contacts people table is no longer connected to the contacts table. Presumably this was caused by the loss of the remote connection during transactions.

You meant referential integrity got removed due to dropped network connections?
Very unlikely.
Dropped connections can absolutely cause corruption in a database. However, it is extremely unlikely that this corruption manifests in just referential integrity being lost. Corruption usually is rather records or whole tables being unreadable, or the whole database file being considered invalid by Access.

I think, the cause for missing referential integrity was a developer of the database not enabling it in the first place or disabling it for maintenance work and forgetting to reenable it afterwards.

I would like to know what is considered best practice in this situation.

There is no general best practice. You must talk to the people who "own" the data and decide with them what to do to resolve the situation.
Nobody here can tell which option is best for you. It depends on the data affected, its content and importance, the chances of recovering the missing parent records, and maybe other factors unique to your operation.

 

[The_Doc_Man]

The immediate problem can be solved by creating a fictitious parent record and then finding all orphans. Then have the foster parent adopt them. That should allow you to reestablish RI and begin a more leisurely search for the proper home for the adopted child records.

 

[Tom Robinson]

Table relationships are a bit of a mess when you have a split database, because you can define relationships in both the frontend and the backend.
If you want relationships to work reliably, use a SQL Server Express backend (it's free) and define the relationships there.
I don't use any relationships in the front end.
That means I don't get any joins created automatically in Query Design, but I'm OK with that.

 

[sonic8]

Table relationships are a bit of a mess when you have a split database, because you can define relationships in both the frontend and the backend.

Well, I think the problem is that the term "Relationship" in Access is rather vague/ambiguous.

A Relationship is primarily a line on the screen indication a relationship between columns in tables. Beyond this visual indication and defining default joins in queries it doesn't do anything.
Only when you enable "Enforce Referential Integrity" in the properties of the relationship, it will actually create a Foreign Key Constraint and data will be validate upon being saved to tables.

Referential Integrity does work in split databases! It must however be enabled to work, and it can only be enabled in the backend file.

 

[The_Doc_Man]

Referential Integrity does work in split databases! It must however be enabled to work, and it can only be enabled in the backend file.

True indeed. The reason is that the tables exist in the backend. A relationship can only be enforced in the part of the DB where the tables live. Which is why if you have a split BE (two sets of non-overlapping tables in TWO back-end fiels) you cannot establish a relationship between two tables that reside in the different back-end files.

You could still write a query that had the same effect as if the relationships existed. But there you are relying on the DB engine to get the SQL right without benefit of a relationship table.

 

[isladogs]

Table relationships are a bit of a mess when you have a split database, because you can define relationships in both the frontend and the backend.
If you want relationships to work reliably, use a SQL Server Express backend (it's free) and define the relationships there.
I don't use any relationships in the front end.
That means I don't get any joins created automatically in Query Design, but I'm OK with that.

Not quite true.
Relationships for BE tables can only be properly defined and RI applied in the BE itself.
In that respect, it makes no difference whether the BE is e.g. Access, SQL Server, SharePoint, Azure SQL or Dataverse
If you try to create a relationship involving BE tables in the FE, it actually has no effect other than creating a visual join.

Similarly, relationships cannot be created between tables in separate BE files

 

[Cotswold]

Depends upon how important orphans without parents are I suppose. If it is an invoice then the money is lost, never to be found.
I used to have a button [Check For Lost Records] in the Utilities section. It would then locate orphaned records and recreate their parent. Sometimes if the routines failed then I'd allow the user to suggest what the parent should be. If there were orphaned records I don't think that it would bother the efficiency of the database to much. Just being filtered out I suppose.
I wouldn't start using a separate table. That's just adding to your workload and support issues. Just filter them using a query in the child table and deal with them there. Putting records somewhere for later usually means later never arrives because everyone is always too busy with today.

 

[tommac]

Thanks for all of your replies. They were very helpful. I've initially gone with The_Doc_Man's suggestion and created foster parents for the orphans and resolved to keep a closer watch for reoccurrences. ......so far so good.

 

[sonic8]

used to have a button [Check For Lost Records] in the Utilities section. It would then locate orphaned records and recreate their parent.

I would rather invest the effort to enforce referential integrity or other measures to prevent orphaned records in the first place.

 

[Cotswold]

I would rather invest the effort to enforce referential integrity or other measures to prevent orphaned records in the first place.

I would have preferred it is users didn't just switch off when they had two or three instances of the software open. But they did and always said they never did that, ever. It would have been good if some of the hardware guys had been capable of installing a sound network. I got fed up of putting their work right. Most if not all orphaned records were the result of BE corruption. Most corruption I found tended not to be in recent records.
My Utility section could locate, isolate and remove corrupted records. It would also restore lost records where possible. In most cases that worked but sometimes I'd need to pull them from old backups if important. Clearly tickets and charges three years, or more old aren't worth the time and effort. The client had to decide if they spent the money and what they wanted restoring. Most things were possible.

 

[Pat Hartman]

If you try to create a relationship involving BE tables in the FE, it actually has no effect other than creating a visual join.

AND the option to enforce RI is not available. That tells you that this is NOT actually a relationship but merely a join.

As @Cotswold alluded to - the only way to reconnect the orphans is to restore the original parents if the FK is not null. If the FK is null, then there is no parent to restore and so those records can only be deleted. They can never be fixed.