A single row has #DELETED across it. How did that happen?

R

raymartin

I have a small table in MS Access 2003, using the default MS Access 2000
format - not linked or joined to anything. There are about 4,000 records in
it, and I found a single row with #DELETED all across it. I understand that
if I delete a row it is immediatley deleted and I don't see, or at least I'm
not supposed to see it. Yet here is a single row with this problem. Why would
this happen? Is there an option to view deleted records? Is there an option
to purge or drop deleted records? Is this because I'm using Access 2033 with
the default Access 2000 format?

I had written about this table in a previous post - it has a prime key with
no dups - but sometimes also has duplicate records. I have recreated the
database from scratch as advised, but sometimes this still happens. All
opinions are that the index gets corrupted, but if so it gets corrupted
easily (every day) without any effort on the part of the users, so I don't
think that's the problem anymore. I have no doubt that the indexes are NOT
corrupted - just that there is a way to insert duplicate records into a table
with PRIME KEY (No DUPS), and somehow I m doing this via VB.NET. It actually
appears that the duplicates are not caused by a user or program attmepting to
insert the same key twice - it really appears that what is happening is that
Access has some kind of temporary record that is not being properly removed
or cleared up, perhaps in a similar manner to the #DELETED record above.

Any help with this would be appreciated.

I am thinking of writing a script to run nightly that would copy/rename the
database using the same technique recommended by a previous post in how to
recover from corruption, but I don't know how to automte this and to do it
manually each night is not an option.
 
N

Naresh Nichani MVP

Hi:

This means that some other user (or) code on your machine has deleted the
record while the table was open.

Yoo open a table - it has some records, some code running ona form or module
deletes the record - you come back to table view and you see this.

Regards,

Naresh Nichani
Microsoft Access MVP
 
R

raymartin

So what you are saying is that if I delete the row while the table is closed,
via say a SQL DELETE, then I won't see the #Deleted.

OK - but given that I have the #DELETED in the table now, how do I get rid
of it - without having to copy the entire table. The problem that the
#DELETED creates is that I do a INSERT....SELECT...WHILE.. and for some
reason Access picks up the #DELETED record (because it "remembers" the data
value that cooresponds to the WHILE condition), even though it shows
#DELETED", and the #DELETED record of course fails to be inserted into the
target table and the entire operation fails, not just the single failing
record.
 
T

Tony Toews

raymartin said:
So what you are saying is that if I delete the row while the table is closed,
via say a SQL DELETE, then I won't see the #Deleted.

OK - but given that I have the #DELETED in the table now, how do I get rid
of it - without having to copy the entire table. The problem that the
#DELETED creates is that I do a INSERT....SELECT...WHILE.. and for some
reason Access picks up the #DELETED record (because it "remembers" the data
value that cooresponds to the WHILE condition), even though it shows
#DELETED", and the #DELETED record of course fails to be inserted into the
target table and the entire operation fails, not just the single failing
record.
Click to expand...

I'm a bit unclear on the sequence of events here.

Do you always see the record when you open the table or form? Even
when you close down the database and start it again.

Or do you see this when you delete a record while somewhere else and
you come back to this form?

If the first then it sounds like corruption. Try importing all the
objects into a new MDB and see if that solves the problem. If the
import pukes on that particular table then you'll need to copy all the
records before that record and after that record.

If the second situation then you can do a requery on the form showing
the deleted records from the other code or form.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
R

raymartin

Tony what happens is this:

I'm not using any forms - I'm just using the table view to look at the
records in the table.

I don't know how the #Deleted record gets into the database, and I can't
seem to force it to occur.
Once it's there I CANNOT see the record while browsing the table while under
the Prime Key sequence. However if I change the sequence to anything else,
then I can see the #Deleted record. If I "select * from table where key = 1",
then I can see the record. And it's under these conditions that the #Deleted
record creates the problem. Also if I delete large blocks of records
sometimes other #deleted records suddenly appear. I have a small table I can
send you if it would help.

When I import into a new database the record is automatically dropped, In
fact that's what I did to fix it. I can also fix it by manually simply
deleting the record. However neither of these is satisfactory. For the first
option it means I would need to re-import into a new databse after every
transaction, and in the second it takes too long to find the record manually.
So I would like to try something else:
(a) Why is is occuring? (so I can prevent it from happening)
(b) If I cannot prevent it from happening, is there a way to mitigate it's
effects (for example can I use a program or someting to actually delete a
#Deleted record?)
(c) Since MS Access obviously knows the record is deleted, why doesn't it
....delete it?
(d) Following on (c) How do you search for the #Deleted record using MS
Access SQL? - what key field value can I use" (Hint - you can't actually use
#DELETED)
(e) If it's corruption then how is it happening - this happens every week. I
have the latest MS and JET updates, and I'm using VB.NET to insert the
records.
 
Top