The Case of the Vanishing Records...

[ed333]

It was brought to my attention that records are randomly disappearing from various tables on the shared back end of my Access 2002 database. For example, on 9/4 one user entered about 10 new files into the files table. Today, I went to look up some info regarding one of those files, only to be shocked by its disappearance (No, I have NO filters). What puzzles me, is that this missing file was entered right in the middle of a session, yet ONLY this ONE is gone! This is not the only table where data has disappeared , either. Also, NO error messages or crashes have occured. Can anyone shed some light here?

Thanks
Ed

 

[Dwight]

I am not an expert but I would take a look at your referential integrity settings. For example, if you have cascade deletes set then if you delete a record in the primary table then all related records in the linked tables will also be deleted.

Dwight

 

[ed333]

Well. I don't have any referential integrity enforced, so I'm pretty sure thats not the problem. Thanks, though.

Ed

 

[ed333]

I think this is a related issue, as well:

On Friday, one of my users ran a report. On Monday, she ran the same report, and one record was missing. Thinking it had been deleted, she reentered that record. Afterwards, she ran the report again. This time, both the original and the reentered records appeared! WTF?

Ed

 

[Dwight]

I hope you find a solution to your problem.

It is my understanding that referential integrity is a core ingredient to a solid database design. You should take the time to set it up.

Good luck.

Dwight

 

[ed333]

Can you explain this "Referential Integrity" to me? I have attempted in the past to set it, however it always says that my data violates the rules! Each table has a primary key, what else do I need?

Thanks,
Ed

 

[Dwight]

Referential Integrity (RI) helps prevent orphaned records. For example, it won’t allow you to delete the one side of a one to many relationship.

The textbook example is if you have a Customer table and then a separate CustomerOrders table. If no RI is set then I can delete a Customer which leaves all of their orders “abandoned” in the second table.

You probably already have orphaned records because once you do you can’t establish RI. I have never done it but my Access Inside Out book says you should run the “Find Unmatched Query Wizard” to track down the records causing the problem.

Dwight

 

[ed333]

Ok. Thanks, I will look into it. Maybe if I can get the RI set, then I will stop losing records.

Ed