#Deleted in every column except one - throws error 3167 - HELP!

[Edward]

ACCESS 2003 FE / ACCESS 2003 BE

My clients are worried that their data may be corrupt. They have
noticed that two of their records share the same StaffFileNumber and
so they're trying to delete the records from the front end to allow
them to recreate the correct entries, but attempting this (using a
button that runs a DoCmd RunSQL DELETE * FROM MyTable WHERE ID = Me!
ID) throws error 3167 and a message "The Record is Deleted".
Unfortunately they're 100 miles away, I have no car today, and their
firewall is preventing me using a tool like TeamViewer. So I got one
of them to run a query on the underlying data and to send me a
screenshot of the result. It's available to view here:

http://www.flickr.com/photos/30588773@N04/3817802900/

What we see here are two rows - the first row is fine, has good data,
the second row has #Deleted in every column except StaffFileNumber
where the value is the same as row 1.

Does anyone have any thoughts about the best way to proceed with this
and, in some ways more important, how it happened?

Many thanks

Edward

 

[Jerry Whittle]

The first thing to do is to get everyone out of the database. The .ldb file
should go away in the folder holding the database file when everyone is out.
If the file persists, that could mean someone is still in the database or
there are other issues.

Next create a backup of the database file(s) and put them somewhere like a
different folder for safe keeping.

Have someone run a Compact and Repair on the database. Chances are good that
the database is corrupt and hopefully the C&R will fix it. There might be
some lost records though.

Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[Allen Browne]

You need to use a string for the query, and concatenate the value of the ID
into the string.

Try code like this:

Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Me.ID) Then
strSql = "DELETE FROM MyTable WHERE ID = " & Me.ID & ";"
set db = CurrentDb()
db.Execute strSql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) deleted."
set db = Nothing
End If

For an explanation, see:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

 

[Edward]

You need to use a string for the query, and concatenate the value of the ID
into the string.

Try code like this:

Dim db As DAO.Database
Dim strSql As String

If Not IsNull(Me.ID) Then
    strSql = "DELETE FROM MyTable WHERE ID = " & Me.ID & ";"
    set db = CurrentDb()
    db.Execute strSql, dbFailOnError
    MsgBox db.RecordsAffected & " record(s) deleted."
    set db = Nothing
End If

Hi Allen

I don't think that's the problem - I've just tested it on some dummy
data and it works fine. The problem is caused by this residual record
which cannot be deleted (I think, though I don't know, that it's some
kind of duplicate.)

It may also be of interest that when I asked the user to Compact and
Repair the back end database, a new database called db1 was created.
When I did this on my machine compacting and repairing was done to the
database itself.

Did you take a look at the screenshot I posted on flickr?

Thanks

Edward

 

[Allen Browne]

No: I hadn't looked at the screenshot. It looks like this issue:
http://allenbrowne.com/ser-47.html#RowsShowDeleted

 

[Jerry Whittle]

That a db1.mdb was created and remains there points directly to a lack of
network permissions for that, and possibly other, users. It could have even
caused the problem.

Every user of the database requires Read, Write, Create, and Delete
priviliges for the database files AND the entire folder holding such files.

Most likely the db1.mdb file remained because the person doing the C&R
doesn't have Delete privileges for that folder. They also couldn't delete the
..ldb file which controls what multiple users are doing to the database. Have
you notices any other problems when multiple users are in the database such
as record locking issues?

 

[Edward]

No: I hadn't looked at the screenshot. It looks like this issue:
   http://allenbrowne.com/ser-47.html#RowsShowDeleted

Thanks Allen, that looks more like it! I'll try to get this done
tomorrow.

Best

Edward