data missing from one field in one record

[jan001]

I inherited responsibility for a couple of databases about a year and a half ago. The one in question is in Access 2003, and contains +/- 150 records of active projects (as projects are closed, they're moved to a different db, but the last time that happened was the end of September). About 40 people have permission to use the db but only about 7 actually do so as a routine part of their jobs. Each record includes a Notes field where the users enter free text describing the project's progress.

A user (one of the 7 routine every-day users) reported yesterday that one of his records was suddenly missing all the notes he had typed in over the past many months. I opened the record and all that was in the Notes field was a series of about 12 # signs. I had to tell him that I wasn't aware of how the contents of just one field in one record could be deleted and then replaced by Access. He re-entered his notes, and then reported to my boss this morning that the same thing had happened again. (I learned that this afternoon; he didn't come back to me about it.)

My boss had him re-enter his notes again and has asked ITS whether they can tell who was doing what when in the db. I don't expect they'll be able to tell us that since there's no log-in required, hence no audit trail, but it's possible ITS has some kind of log of network activity and MIGHT be able to tell who opened the db and when, but I don't expect even that to be able to say who edited which record in which way.

I've checked the Notes field on the other records in the db and they all appear to be fine.

How like is it, in your opinions, that Access is somehow trashing this one field of this one record? I can't stop suspecting a human hand in this one, although I really hate to think like this, so I'm asking, to be sure. I don't want to stand up and say Access couldn't be responsible (never say never, etc.), until I check with people with more Access expertise than I have.

Thanks.

 

[SydneyGeek]

Not a human hand, most likely some corruption creeping into the database.

Memo fields are very convenient -- they let you put all that data in them -- but Access doesn't seem to handle them very well. They cannot be indexed, and some operations with queries break when you include memo fields.

Examples:
I have built Totals queries in tables that have got memo fields. When you try to Group By a memo field ( or apply most of the other operators like First or Last), you will more likely than not get an error. Remove the memo fields and the query works fine.

I have seen those #### marks in memo fields that were imported from one Access table to another. Not in all records of the table, just some. And not in all memo fields either.

I would recommend exporting the contents of the table out to a text file, and seeing whether the data is OK in the exported file.
Also, make a copy of the database (with no other users in it), and compact and repair the DB; try doing it a few times.
Finally, try importing all of the tables to a new, blank database. That can often repair a slightly damaged table / query / form / report.

If you haven't split the database into a front end (application) and back end (data), do it. Give all your users a copy of the front end database that they can use to access the data. Separating the data from the application can go a long way to protecting your database from corruption.

Some tips:
Always make your users quit Access by closing the database and the application. Flicking the switch at the end of the day is sure to cause you problems.
If the DB hangs, DO NOT force-quit if you can avoid it. Do something else in another app; go get a coffee; go to lunch; whatever. Let the database find its own way back. It usually does, but can take maybe 10-20 minutes to do so.
Compact and repair the database (front and back ends) regularly. Back up daily.

Sorry, a bit of a stream-of-consciousness rant, but I hope it helps.

Denis

 

[jan001]

Thank you, Denis. Interesting stuff.

I haven't done any of the things you mentioned that were likely to cause that error (group by fields, etc.) but who knows what other users may have played at.

I'm pretty sure the every-day users are good about closing down correctly when they shut down but I'll make a point of bringing it up as a reminder. Ditto about not doing a hard shutdown if it hangs. I haven't seen that happen with this particular db, but perhaps others have and just didn't report it to me.

As for compacting and repairing, and backing up, I've become more consistent about both over the past couple of weeks, following a fairly hard crash of a much larger database in mid-November. At the end of every day, I now do a compact and repair and also make a backup of the db to my C: drive. Our ITS does do nightly backups that do catch this particular db because of where it is on the network, but those backups are stored off-site as they should be, so can take a couple of days to get delivered and applied. This way there's at least another backup and it's current and readily at hand.

I've noticed the db throwing out "db1.mdb" files which I only recently learned can be harbingers of doom (gotta love learning with live ammo), seven since June, but none since I began the daily compact-and-repair. If those and/or the #### entries in the Notes field of that one record are in fact signals of incipient corruption, is it the kind of thing that may be fixed by using compact-and-repair consistently, or do you think I need to get ready to roll out some larger guns?

(PS: Stream of consciousness is nothing to apologize for! I do it too, so I understand. )