I've read several postings about the "Record is Deleted" Access error, but none of the solutions seem to address the problem itself, rather just the quick fix to get back up and running.
Here's the scenario: I used Visual C++ 6.0 with MFC and ADO to develop an application for one of my clients. This application has been running now for about 20 months. For the first 18 months, there was not a single database error. They are continuing to use it in exactly the same way with the same hardware, same OS (WinXP Pro), same number of users, etc. The error is that occasionally (about 1-2 times per week now) they will get a "Record is Deleted" error when trying to open a record that is in fact not deleted. It has all the classic symptoms of all the other posts like putting in all #######'s into one of the fields after performing a Compact and Repair, etc.
I'll try to answer some common questions first:
All 5 client systems (and the Server) are running WinXP Pro with all the latest updates, and I've personally verified that they are all running the same (latest) version of Jet 4.0.
When the error occurs, the database needs to have Compact and Repair performed so that the records can be viewed.
The field that gets corrupted is always a non-indexed text field with unicode compression = true. Sometimes it is a Text field, sometimes it's a Memo field.
No, they are not shutting down the server, or any hardware or software abnormally. This will occur during normal usage.
This is starting to occur more frequently, from twice a month to twice a week within the last 2 months.
No, there are no tables, queries, etc. that are left open and not properly cleaned up. Remember, this software worked flawlessly for 18 months.
This is a single .mdb Access database, with nothing fancy whatsoever. It has several Relationships, but nothing out of the ordinary.
I've been looking for 2 months for a reason as to why this is happening, and what I can do to prevent it. Could it be that the database is getting too big? It's about 5mb to 7mb prior to C&R, afterwards, it's about 3.8mb. I would think that was pretty small.
Thanks for anyone that can help me to *prevent* this problem rather than just fix it with Compact and Repair each time it happens. My client is getting really tired of having to log everyone off, perform a Compact & Repair, cleanup the messed up fields, etc.