Intermittent Record Loss

[Sean O'Halloran]

I'll try to keep this brief: my users are losing certain records; approximately 1 record lost every 2 weeks for past several months. Users create 'Contact Notes' which are part of a larger record; the larger record remains intact when loss occurs. Sometimes, not always, certain table relationships are broken when this occurs.

I've looked at some of the MS KB articles, but they are not always talking down at my level.

Any advice on what could be causing this would be appreciated. I know that some problems are inevitable, but am I wrong in thinking this is happening too often?

I recently disabled the 'X' button following advice found in this forum (thanks to all, especially Richard Rensel, Quantum Data Solutions); other searches over the past several years led me to split the fe / be, correct object names, disable mouse wheel function and manually compact daily - again; your concatenated wisdom and willingness to share has been awe-inspiring for me.

I am a social worker with limited Access experience, but my state agency employer recently lost funding for the designer / programmer with whom I collaborated in building the DB, and, for the foreseeable future, I'm what we've got.

Environment: Access 2k database with max concurrent 35 users, split fe / be, Win NT, 3Com NIC cards. Current DB size is @100 mg, bloats to @108 mg by day's end. Just started process of upgrading JET 4 from SP5 to SP8 on our 130+ PC's. Small data-pipe which users also use to get to the Internet.

Thanks in advance, not just from me but from all the neglected children and troubled families my users serve.

 

[The_Doc_Man]

OK, I am going to approach this from some totally different directions, all of which I hope to be productive.

1. PROCEDURAL ISSUE: Advise your users that until you can identify the source of this problem, they should preserve their paper trail for a specific number of days or to specific dates. These dates should correspond to days AFTER a verification process in which you look for lost records and the other symptoms that you say you sometimes see.

In particular, you might consider publishing dates of database backup to whatever medium you use, and tell users it isn't safe to trash their paper until after that date passes.

2. SECURITY ISSUE: What kind of security setup do you have? If it is workgroup security, you need to determine feedback on how each user is coming in to this database. What I am trying to guard against is a common security error: Allowing users to join a database as though they were the administrator account, which has the ability to do things to the database that less powerful users could not do.

Do you have a startup form? If so and if you are up to it, you might want to hide some logging code in that screen's OnLoad event so that you could see how everyone enters this database.

3. USER AWARENESS: Advise users that you are having a problem and ask them to advise you any time they identify a lost record. While it is fresh, ask them what they were doing when they saved the record. TAKE WRITTEN NOTES. This will force you to organize and clarify your thoughts.

4. PROCEDURAL NO-NO'S: Advise users that they must NEVER NEVER NEVER turn off their machines without first exiting from the database. Advise users that they must NEVER NEVER NEVER turn off their machines without first clicking the SHUT DOWN option on the START button's pop-up menu. Advise users that they must NEVER NEVER NEVER leave their systems connected to your back end database overnight.

5. INVESTIGATIVE: When you detect a broken relationship, it is usually possible to simply re-assert it. However, Access will check the tables involved to assure that it doesn't (re)start with an impossible relationship. Have you tried simply reasserting the relationship and has it ever failed? If so, with what error message(s)?

6. INVESTIGATIVE: When you lose a child record but keep the parent, that can occur in cases of destructive interference. In brief, this occurs when two users hit the same record at the same time and both attempt an update. Check the settings for record locking on the tables and forms involved. Also check the properties of any underlying queries. What you are looking for is something that has turned off all record locking on your table that is the parent of the parent/child pairing.

7. INVESTIGATIVE: Is this loss occuring for all users distributed equally or does it appear to have some preference for specific users? If specific users, you have a couple of possibilities:

7.a EDUCATION: Offer specific training in the correct use of the application as you think it should be used

7.b OBSERVATION: Watch the error-prone users for a while for procedural errors

7.c OBSERVATION: Look at the records that get lost. See what, if any trends can be observed. You have both data correlations and DATE correlations to consider. (Like, only lose records on a particular day of the month or a particular time of day.)

 

[Sean O'Halloran]

Issue 0 WOW - Thanks!

Issue 1: Will do
.
2. SECURITY ISSUE: “What kind of security setup do you have?” I will explore this issue. I’m not sure if we set up workgroup security using the built in Access functions, or if we simply imposed it by structuring user access to forms, etc. How would I tell if someone has admin rights?

”Do you have a startup form?” Yes. The users have to provide name and password. No other way to get in. Is there an entry issue that I’m missing here?

3. USER AWARENESS: Doing this – I’ve asked users to identify field, time, and write verbatim error messages if I’m not immediately available. Forgot to mention, this error only occurs while they are actively in the record – never a situation of ‘finding it later’.

4. PROCEDURAL NO-NO'S: “NEVER NEVER NEVER turn off their machines without first exiting from the database...use shut down option” – Done, and our network admin automatically severs all connections every night at 10 p.m to run maintainance.

5. INVESTIGATIVE: “Have you tried simply reasserting the relationship and has it ever failed? If so, with what error message(s)?” – Has failed, but not always and not not recently; I can’t recall specific message but I what I believe it was “Can’t establish referential integrity because there are orphan records.” Deleted records, reset the relationships, compacted, done.

6. INVESTIGATIVE: I will look into this. Speaking broadly users NEVER need to write to the same record at either parent or child level. You are suggesting I look at forms also? The affected records are entered in a subform.

7. INVESTIGATIVE: “Is this loss occurring for all users distributed equally…” An interesting question, because I THINK it is only occurring on PC’s in a geographical area of the office…which leads me to strongly suspect a hardware issue, but preliminary investigation shows that not all the affected machines are on the same patch panel / switch. I’m looking into possible NIC card variation among PC’s (all 150 Dells purchased as one batch, but some have slight hardware differences Also, the few users who are not in that geographical area are not daily users, so the “geo” evidence may be spurious.
I’m also looking for parallels in user installed software, which is a no-no but they keep doing it.

7.a EDUCATION: Will do again

7.b OBSERVATION: Doing

7.c OBSERVATION: “Look at the records that get lost…Like, only lose records on a particular day of the month or a particular time of day.” Doing. We do have a couple of IT guys to keep the server and PC’s running, but they know little about Access, and although I’m fairly sure there are ways to monitor server activity that would help diagnose this problem, I can’t get them to “own’ the challenge.

...can’t tell you enough how much I appreciate your comprehensive response. Thank you.