Losing Entries

[NicoleGW]

There've been a few threads about similar issues in the past, but I couldn't find anything that would solve the problem I'm having at the moment.

I manage a big complex Access database for my lab. I didn't design it, but I add forms and tables as they're needed. It's always been perfectly stable, but in the past week a couple of entries have sporadically disappeared. My initial thought was that someone had deleted them by accident, but the only person who's accessed these records recently is the one person in the lab who I would absolutely trust not to screw that sort of thing up.

As far as I can tell, multiple entries for two different people (just these specific two, as far as we can tell) have gone missing from the main tracking table. Unfortunately, this means that the associated records in other tables are also getting deleted.

Any ideas?

ETA: This has made me realize that I really need to split the DB into a back and and multiple front ends, but this is the first time we've had entries go missing and people have been using the database with the same frequency going back several months now.

 

[RuralGuy]

Without an audit trail it is difficult to say what might be happening but splitting the db and giving everyone their own FrontEnd is definately a good idea.

 

[georgedwilkinson]

Remove any cascade delete constraints and ensure that referential integrity is enforced. Do this in addition to splitting the database.

 

[NicoleGW]

Sorry, this is going to sound stupid, but when you say remove cascade delete constraints do you mean that I WANT to have cascade deletions, or that I DON'T want cascade deletions?

I'm really careful about setting up relationships, and haven't changed any in the past few weeks.

Thanks for the replies!

 

[georgedwilkinson]

If someone is deleting records which is deleting other records and you don't want that to happen, you do not want cascade deletes, do you?

I never, ever turn on cascade deletes in a transactional system. It's too easy to screw things up.

But be sure to keep the referential integrity (sans the cascade delete) so the DBMS has something to complain about when a user accidently deletes a record. This will give them feedback that they can't do that, allowing you to further troubleshoot your problem.

But you really need to split your database anyway. You get all kinds of problems with a single DB file.

 

[NicoleGW]

Ah, I see, that makes sense.

I'm definitely going to split the database, I just am worried that there's some other issue going on, and if I don't track it down we're not going to be able to trust our records in the future.

The problem is that no one is deleting these records that are causing the cascading deletion. Unless there's some malevolent individual who's just enjoying screwing with people, I don't see how it's possible. The first time, I could see it being an accident and no one wanted to fess up, but the fact that the exact same thing has happened again to multiple records and I know that the only other person using the database in this time period is Access-savvy means that it had to have been something else.

I guess I'm concerned about things like corruption or a faulty auto-number or whatever.

My other thought is that the server might have burped and rolled back a few hours, but I'm still waiting to hear back from IT on that one.

 

[georgedwilkinson]

I totally agree. That's why it is imperative you remove the delete on cascade flag. You don't know where the problem is coming from and you definitely don't want cascades to happen. It may also provide you (or one of your users) with a clue where the bug is coming from.

 

[NicoleGW]

Sounds good! I will go ahead and give it a try.

Coincidentally, I just got a new shell from the original designer, in which she fixed little bits of code here and there to make sure it's fully compatible for MS 2003 users, so maybe that will help.


Thanks so much!