Access 2003 “Record is Deleted” error

 

Have you had Access 2003 spontaneously delete a record from a table for no apparent reason, leaving behind something like this in your data table:-

 

 

This renders the table, and any other linked to it, inoperable.

 

I don’t fully understand how or why this happens but I have a theory that it’s to do with orphaned .ldb record locks on a network.  But if anyone can elaborate on the cause I’d be most interested to know. It may well be that record locking is not enabled and the solution to this is at the bottom of the page.

 

One theory is that this problem occurs in a multi-user environment where a user has forced Access to close using Ctrl/Alt/Del and Task Manager, leaving behind the aforementioned orphaned record locks file and other unwritten data. Ask your users to get help from you to close Access down correctly.

 

I've also found another potential cause for this problem. If you are using the DAO.Recordset and ThisForm.RecordsetClone technique to detect that a form has its records locked by another user, I very strongly suggest that you remove such code and any references to it. As an experiment I removed it from my own applications and the Deleted Record error hasn't returned since. And curiously, when I think back, the Deleted Record error only started to occur after I'd been using this technique.

 

Although now retired (as of May 2016) I am available for working on legacy Access 2003 - 7-10 systems. So if you have an old Access database and need help with it, you can contact me, remove the extra 'z' from the email address.

 

This is how you fix the damaged data table(s):-

 

1                    Ensure all users have closed any Access or other application which references the poorly database.  There should be no databasename.ldb file present in the folder.  You cannot proceed until this is done.

 

2                    Open the database and open the relevant table, sort the key field A-Z and Z-A and scroll down looking for the #Deleted tell-tale record.

 

3                    Delete this record.

 

4                    Run a Compact and Repair (it’s accessed from the Tools, Utilities menu) and then reopen the database, reopen the table and again sort it A-Z and Z-A to find an apparently empty record at the top of the list.  Delete this empty record.

5                    Change to Design mode  and you will probably find that the table's Identifying Key Field no longer has its key icon showing like this .  If it doesn’t, click anywhere in the field name and click the Key icon  on the toolbar.  If it does have this key showing, you’re lucky.

 

6                    Run Compact and Repair again and the database should work normally.

 

7                    If the damaged table is related to other tables, there is sometimes a knock-on effect and you will have to check and possibly repair those related tables in the same way as this one.

 

8                    Have your database run the following code at the start of the application, probably in the startup form's Form_Load event, although it won't matter if the code runs more than once. This does assume Access 2003:-

 

' these settings ensure that record locking and file types are correct
Application.SetOption "Default File Format", 10
Application.SetOption "Default Open Mode for databases", 0
Application.SetOption "Default Record Locking", 2
Application.SetOption "Use Row Level Locking", True

 

This will make the setting in Tools, Options, Advanced behave like this:-

 

I was highly amused today (March 4th 2014) to receive this email - to preserve the sender's modesty, I won't identify who sent it!

"I worship you and THANK you from the bottom of my heart!. You don't know me but you have saved my life. I am NOT an access developer but have somehow stumbled into the position at my workplace. That being said while working with one of the companies DB's I somehow lost 15 years worth of data. My stomach turned inside out and I thought I was going to vomit and definitely lose my job. However, after reading your SAGE like advice at this link I was able to fully restore my DB and save the day. I hope you don't mind, but I took full credit at my workplace and let them think I am a genius. That being said, I wanted to thank you profusely and let you know that you have saved my livelihood and also my pride. I can't stress to you how thankful I am and I hope that this e-mail is able to at least bring a smile to your face knowing that you were able to pull me from the depths and give me life once again."

 

Good Luck

Rob Davis

Telford, Shropshire

Email (manually remove the extra “z”).