Record Is Deleted Error Message

By: Tony Hine (mail@TonyHine.co.UK)
Tel: +44 1635 522233
My profile on ecademy (http://www.ecademy.com/user/tonyhine)

I had a problem importing just one table I kept getting the message “Record is deleted” and no records were imported.

First I thought it may be because there were quite a few columns in the table so I tried importing just one column, however I got the same message “Record is deleted“ .

I considered it could be because the database was originally in a Company office workgroup. I wondered if the “Work group security” was causing the problem. I read up on this and found that importing the objects directly into a new database was one of the recommended ways of circumventing this problem. This was what I was already doing! So what was wrong?

Next idea, I exported all of the records into an excel spreadsheet. This was successful! So I then re-imported the records from the spreadsheet back into a table in the database.

There were a couple of problems, in that all of the text fields were converted to 255 character length. Although annoying I thought I could handle this problem. I proceeded making a few changes to this database, then I noticed that one of the fields that had been imported was originally a “Memo field” it had been converted to a text field with 255 characters. As soon as I noticed, I checked to see if any data had been lost by comparing this field with the original data. I found a significant loss of data so back to the drawing board!

All of the other tables had imported correctly, only this one seemed to be giving problems, I had tackled the size problem it was not a big table by MS Access standards, but I felt it could be the size, In particular the number of fields could be causing this error. But just trying to import one field at a time hadn’t worked?

Then it dawned on me what if I tried to import one record at a time --- that worked! So I wrote the query with a between statement I tried 10, then a 100, then a thousand records it worked fine! However when I tried 5000 records the error re-occurred?

I realized that I probably had some corrupt data in the table, so I set about systematically extracting a limited number of records until I found the area where the corruption was.

This worked but returned “0” records --- Between 11001 And 12000

“Between 12001 And 13000” Got error message “Record is deleted”

I got to 12800 all ok

12900 caused error

Error is in a record between 12800 and 12900

I will continue to whittle it down!

OK to 12819

The corrupt record is: 12820

I had found the corrupt record! Now all I had to do was create two queries one each side of this corrupt record to extract the information.

On inspection of this corrupt record I discovered that the memo field contained the following: “#Deleted “ I am going to attempt to modify the memo field and see if it fixes the corruption of the database.

My attempt at modifying the corrupt record, in particular the memo field containing “#Deleted” caused the error message to re-occur. “Record is deleted”

I have made a copy of the actual record 12820 and pasted it into a spreadsheet to send to the customer so that it can be checked in the existing data on their system.

Fortunately there were no related records in other tables affected.