"Record is Deleted" Access error PLEASE HELP!
Hi,
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.
Douglas Holt
douglasholt@cox.net
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.
Douglas Holt
douglasholt@cox.net
ASKER
As I said, the database gets corrupted, so we do a Compact and Repair, and the database is fine for several days. Are you suggesting that the database may still be corrupted after the C&R, but that it may work correctly for a while until the symptoms show up again?
Is there a way to import all the the tables into another empty database file while keeping their relationships? It would be a lot of work to rebuild all the relationships...
Yes, sometimes (about 50/50) the fields are of type Memo, the other times they are of type "Text". Is there any option that will allow large amounts of text (up to 32k) other than Memo?
Thanks...
Is there a way to import all the the tables into another empty database file while keeping their relationships? It would be a lot of work to rebuild all the relationships...
Yes, sometimes (about 50/50) the fields are of type Memo, the other times they are of type "Text". Is there any option that will allow large amounts of text (up to 32k) other than Memo?
Thanks...
I am on Dave's side. This is the beginnings of a corruption event.
If you are using memo fields anywhere,. assure that the edits are being done as a single paste to the field. See how I explained this previously.
https://www.experts-exchange.com/questions/20761181/Memo-field-corruption-in-Access-2002.html
and
https://www.experts-exchange.com/questions/20736310/Memo-Fields-Unreliable-Too-Big.html
If you are using memo fields anywhere,. assure that the edits are being done as a single paste to the field. See how I explained this previously.
https://www.experts-exchange.com/questions/20761181/Memo-field-corruption-in-Access-2002.html
and
https://www.experts-exchange.com/questions/20736310/Memo-Fields-Unreliable-Too-Big.html
ASKER
Also, is there any way to make Memo fields safer? I.e. Unicode Compression setting, or ???
Unicode Compression is for using multiple languages in a database. mess with the defaults at your own peril.
Access isn't the "safest" database around. Seeing you're doing all you data accessing ia C ( I assume you are using disconected datasets ) the "usual" problems with memo's * shouldn't* be a problem in your case. They still can cause havoc with your db not matter how "careful" you are. I'd say the most probable cause of all the errors is something is corupt, and the mdb is in need of a "clean - up" (re my first post about importing into a fresh container)
Dave :-)
<off topic>
Hey Jake!
</off topic>
Dave :-)
<off topic>
Hey Jake!
</off topic>
Jack.. shirt.. what am i saying.. too tired and its only monday
ASKER
Jake,
I agree, I've left the Unicode Compression set to true, the default.
Jake and Dave,
I read your other two articles and since I am using VC++, using custom modal dialogs and using BeginTrans, CommitTrans, etc. and locking records that are being edited, etc. that shouldn't be an issue. Correct? I am also overwriting the data in the Memo and Text fields via binding classes. Which brings up a question: I'm using Ansi strings, not unicode... is the single 0 a the end of the string enough, or should I memset the entire string to 0 before copying in the new value?
Thanks!
I agree, I've left the Unicode Compression set to true, the default.
Jake and Dave,
I read your other two articles and since I am using VC++, using custom modal dialogs and using BeginTrans, CommitTrans, etc. and locking records that are being edited, etc. that shouldn't be an issue. Correct? I am also overwriting the data in the Memo and Text fields via binding classes. Which brings up a question: I'm using Ansi strings, not unicode... is the single 0 a the end of the string enough, or should I memset the entire string to 0 before copying in the new value?
Thanks!
ASKER
Oops, sorry Jack (not Jake). I took a bad cue from Dave (bad Dave!). <g>
It's unlikely but this *might* be a symptom of failing network hardware - are you having any other file access problems?
I did have this problem several times over the years (Access 2, 95 and 97, not yet in higher versions), and it was always when I had VB code in the database... I never, unitl now, managed to do it in a clean BE database with only tables and relationships.
If you have anything else in the mdb file, import tables to a fresh database, as has been suggested already (simply choose "File / Get external data / Import", the default is to import relationships as well, see the [Options] button in the dialog box).
If you really need VB in your BE file, do not import. Create new modules and cut'n'paste the code.
For VB, you might also consider the /decompile command-line option. Saved me several times: found out that class modules had been changed to normal modules, for example...
Whatever the cause is, it has probably nothing to do with your coding or the setup. The first suspect *is* the file.
Cheers!
If you have anything else in the mdb file, import tables to a fresh database, as has been suggested already (simply choose "File / Get external data / Import", the default is to import relationships as well, see the [Options] button in the dialog box).
If you really need VB in your BE file, do not import. Create new modules and cut'n'paste the code.
For VB, you might also consider the /decompile command-line option. Saved me several times: found out that class modules had been changed to normal modules, for example...
Whatever the cause is, it has probably nothing to do with your coding or the setup. The first suspect *is* the file.
Cheers!
I have two questions for 'jadedata'.
I have the impression that the management of text fields and memo fields is not that different, as they are both stored in the "variable length" zone of the table. I know that memo fields are stored in the same way as OLE objects, long binary data or hyperlinks, but the Access GUI treats text and memo in the same way.
If there is a "special way" to edit fields to avoid the problem for memo fields, the same should apply to text...
My question here is: what makes memo fields different for the GUI?
I have read your previous posts about the EditMemo function, which is, I think, bogus. It is true that you can run into problems when using .GetChunk and .AppendChunk with uneven chunk sizes and strings, but this is only when you confuse a VB string with an array of bytes (it's an array of integers). It has nothing to do with the interface. When you edit a memo (or any other editable data type), the entire field is rewritten. Access will not fool around with the .OldValue and the .Value of a control to devide a clever .AddChunk method of updating memo fields.
My question here is: what do you think is the effect of the EditMemo() function?
Now as we are talking about a C++ front-end, the question is valid:
For Douglas:
Do you use something like .GetChunk and .AppendChunk on your memo fields?
Cheers!
I have the impression that the management of text fields and memo fields is not that different, as they are both stored in the "variable length" zone of the table. I know that memo fields are stored in the same way as OLE objects, long binary data or hyperlinks, but the Access GUI treats text and memo in the same way.
If there is a "special way" to edit fields to avoid the problem for memo fields, the same should apply to text...
My question here is: what makes memo fields different for the GUI?
I have read your previous posts about the EditMemo function, which is, I think, bogus. It is true that you can run into problems when using .GetChunk and .AppendChunk with uneven chunk sizes and strings, but this is only when you confuse a VB string with an array of bytes (it's an array of integers). It has nothing to do with the interface. When you edit a memo (or any other editable data type), the entire field is rewritten. Access will not fool around with the .OldValue and the .Value of a control to devide a clever .AddChunk method of updating memo fields.
My question here is: what do you think is the effect of the EditMemo() function?
Now as we are talking about a C++ front-end, the question is valid:
For Douglas:
Do you use something like .GetChunk and .AppendChunk on your memo fields?
Cheers!
ASKER
shanesuebsahakarn,
No other file access problems. The network is quite simple and streamlined. Since there are only 6 systems, it's setup as a Workgroup.
harfang,
This database is as simple as it gets: Tables and Relationships only. No code whatsoever in the DB itself. It doesn't even have a single custom Query or Report.
I am not using GetChunk or AppendChunk. I don't use any VB <cringe>!!! I only use ADO via binding classes from VC++.
All,
I'll do the Import into a new empty database and see if that helps. Oh, please, oh, please...
Thanks for all the help. If Import is the solution, I'll have to figure out how to divide up the points. Perhaps mostly to the first person who suggested Import... (which would be Dave). Any suggestions on how the points should be split? Oh, wait, I still have to see if that is the solution! Thanks again!
No other file access problems. The network is quite simple and streamlined. Since there are only 6 systems, it's setup as a Workgroup.
harfang,
This database is as simple as it gets: Tables and Relationships only. No code whatsoever in the DB itself. It doesn't even have a single custom Query or Report.
I am not using GetChunk or AppendChunk. I don't use any VB <cringe>!!! I only use ADO via binding classes from VC++.
All,
I'll do the Import into a new empty database and see if that helps. Oh, please, oh, please...
Thanks for all the help. If Import is the solution, I'll have to figure out how to divide up the points. Perhaps mostly to the first person who suggested Import... (which would be Dave). Any suggestions on how the points should be split? Oh, wait, I still have to see if that is the solution! Thanks again!
ASKER
Question, does anyone think I should memset the string fields to 0 in the binding classes prior to copying the new strings? Or is that simply not an issue. I would prefer not to have to do that for efficiency's sake.
> "memset the string fields to 0"
no need. However, make sure you don't end up writing 0's to the strings. Never tried it, but it sounds dangerous!
As for points, first good answer is fine, of course.
Good Luck
no need. However, make sure you don't end up writing 0's to the strings. Never tried it, but it sounds dangerous!
As for points, first good answer is fine, of course.
Good Luck
ASKER
> "memset the string fields to 0"
Sorry, what I mean is that the binding classes contain char arrays of a fixed length. When I initialize them, they all get zero'd out. But when I edit them, I just use lstrcpy to copy the new string into the array (checking for length of course). My question is where I should zero them out completely, or is the trailing 0 that lstrcpy puts in enough? I don't have much experience with unicode, but my guess is that it may need at least two 0's at the end. Could this be the case? Thanks.
Sorry, what I mean is that the binding classes contain char arrays of a fixed length. When I initialize them, they all get zero'd out. But when I edit them, I just use lstrcpy to copy the new string into the array (checking for length of course). My question is where I should zero them out completely, or is the trailing 0 that lstrcpy puts in enough? I don't have much experience with unicode, but my guess is that it may need at least two 0's at the end. Could this be the case? Thanks.
Well, I can't answer that. I have a strong feeling that VB does *not* use the 0 at the end, because it has to be added then passing a string as parameter to C, but "better safe than sorry", so I won't just try to guess. I do know that a VB/JetEngine 4.0 string is an array of integers, but that's as far as my system knowlege goes.
Cheers!
Cheers!
ASKER
All,
Sorry I didn't post this sooner. I found the solution, and I haven't had a single corruption since.
The problem wasn't in the binding classes, except that my bug exposed a bug in ADO only with binding classes. Basically, I had a rare scenario that resulted in calling Update twice on the same record. The second Update caused the corruption, but only for tables with memo fields, and only if I was using binding classes.
I hope this helps someone. It sure was a frustrating situation!
Sorry I didn't post this sooner. I found the solution, and I haven't had a single corruption since.
The problem wasn't in the binding classes, except that my bug exposed a bug in ADO only with binding classes. Basically, I had a rare scenario that resulted in calling Update twice on the same record. The second Update caused the corruption, but only for tables with memo fields, and only if I was using binding classes.
I hope this helps someone. It sure was a frustrating situation!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hrrrmm...while I appreciate the gesture, I did not participate in this question, and you should have your points refunded to you. Please post a request to PAQ/Refund this question at:
https://www.experts-exchange.com/Community_Support
https://www.experts-exchange.com/Community_Support
ASKER
I know, but I'm lazy, and since I have unlimited points, you gave me a great way out! Thanks, and Merry Christmas!
Maybe the db is corrupt, have you tried to import all objects (tables, queries etc) into a new access container (mdb file) and see how that goes? Do you use memo data typed fileds? these can be a source of many problems..
Dave