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 used 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.
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
Good Luck
Telford,
Email (manually remove the extra “z”).