Disappearing Data in Split Database - Access 2007

[Nettie]

I work with an Access 2007 database that I split into a front end and back end. The back end holds all of the tables and the front end holds the queries, forms and reports. It is also a multi-user database, in that we have 14 people having access to the same back end. Each of the 14 people have their own front end. I have one main table in the back end listing clients and their individual ID numbers. That table has a One to Many relationship with several other tables, such as Case Notes and Authorizations, to name a couple.

Here's one of my problems: Sometimes data that is entered by a user will disappear.



Example 1: User creates a new record in the Authorizations form. Sometimes it is created by duplicating an existing record, sometimes it is completely new. User then prints a report from that new record, which looks like it has been saved. A couple minutes later, or a couple hours later, or maybe even the next day, user goes back into the database and cannot find the record that was previously created. At that time I look in the back end and cannot find the record either.

Example 2: User creates a new record in the Case Notes form on Day 1. These are always completely new records. User returns to the database on Day 2 and the new record sometimes does not show up in the form or in the back end table.

I cannot MAKE these things happen and they are sporadic, which makes it hard to diagnose, but I was wondering if anyone knew anything about why this was happening or if there was a way to retrieve the lost data.

[John_G]

Hi -

It could be a couple of things. First, a record created by user A may be overwritten by user B, without any notification. When a user creates a new record, how is that record identified (uniquely), and does the user see that identification? Have there been any cases where a record created by User A is still there, but with different data?

How do users create records? Is a record created just by filling in data on a form, or must a user click a button to do the save? If so, if they forget to click and close the form, what happens?

If users have the ability to delete records, could records created by one user be accidently deleted by another user?

If you have a regular network backup (daily), have you ever tried restoring to an earlier version to see if the missing records were ever there? Example - today is Monday, and a user complains that data entered on Friday is no longer there - if you go to Friday's backup, is the missing data there?

You are right, sporadic things like this, that you cannot replicate on demand, are very hard to diagnose - some of these ideas may help.

John

[Nettie]

When a user creates a new record it is identified by an auto-number. In the Case Notes, the user does not see the number; but in the Authorizations, the user can see the number of the new record. They create the record by just filling in data on the form, which is actually a subform. They do not have to click anything to save it. If they close the form, the record is saved because they have clicked outside of the subform. The users are not aware of how to delete records. We do have a network back up and I will try looking for the data that way. But I'm still wondering how to prevent it from happening.

Thanks for your help.

[Nettie]

The missing data is sometimes within an hour of entering it into the form, so daily backups are not helpful in that case. I would appreciate any other advice on this matter of disappearing data.
Thanks

[John_G]

This doesn't make sense. If the problem is within Access, then the back-end may be corrupted to the point where compact-and-repair won't fix it. Try creating a new blank database, and then import all the back-end tables into it, and use that new one for a while to see if the problem goes away.

One thing you could do is to take all the data entry/update forms, and set the Allow Deletions property to No. That would at least eliminate accidental deletions.

If neither of those work, then I hate to say it, but you may have a "people" problem. You said that users were not aware of how to delete records - but could they, if they knew a little bit about Access? If they knew where the back-end was on the network, could they open it directly?

Is there anything in common about the records that are disappearing? Same person entering data, same case number, related cases,... anything?

Do you have a log in - log out facility that tracks activity? Maybe you need one.

Beyond that, I don't know what else I can offer; as I said, sporadic things like this are often very hard to resolve.

John