Woe is me -- can't delete record in Access 2000

[Julie]

I'm dealing with this Access 2000 database that has a troublesome
record which cannot be deleted.

The database has 56 tables and dozens of relationships. The table
where this record resides has about 7,000 records.

When I try to do an outright delete on the record, I get "The search key
was not found in any record." I also get this error when I run an SQL
DELETE command.

If I try to import the table into another empty database, I get "Record is
deleted" and nothing is imported and no table is created.

If I try Compact and Repair, the progress bar hangs at 50 %. Nevertheless,
I can exit Access without trouble and the new database still contains the
bad record.

I read where a possible fix involved temporarily removing the primary key
and
deleting the record. However, this requires removing the relationships
which
I'd rather not recreate by hand. I tried it anyway, though, and still got
the same
error "The search key was not found in any record."

Any ideas for cleaning up this mess?

Thanks.

 

[John W. Vinson]

Any ideas for cleaning up this mess?

See the suggestions at

http://www.granite.ab.ca/access/corruptmdbs.htm

In this particular case, your best bet is to create a new, empty
database. Import all the objects EXCEPT this table. Create a new,
empty table with the same field definitions, and run an Append query
from the (linked) corrupt table, using criteria on the Primary Key
which omit this record - but they must not *REFER* to this record's
value (e.g. (PK BETWEEN 1 AND 3122) OR (PK >= 3124).

It's a major pain in the neck but it is salvagable.

John W. Vinson [MVP]

 

['69 Camaro]

Hi, Julie.

Any ideas for cleaning up this mess?

First, make a backup of your database, just in case something goes wrong.
Sort the table on the primary key. Note the value of the primary key of the
record that precedes the bad record, and the primary key of the record that
comes after the bad record. In the following example, the primary key is
ID, where the primary key of the record that precedes the bad record is 497,
and the primary key of the record after the bad record is 500.

Create a new table with the same structure as the table with the bad record.
Create two new append queries:

INSERT INTO tblNewTable
SELECT *
FROM tblOrigTable
WHERE ID <= 497
ORDER BY ID;

.. . . and . . .

INSERT INTO tblNewTable
SELECT *
FROM tblOrigTable
WHERE ID >= 500
ORDER BY ID;

Execute both queries and all but the bad record should be appended to the
new table. Manually recreate the data from the bad record in the original
table, and put it in the new table. Next, apply all of the same
relationships from the original table to the new table. Now delete the
original table and compact the database. You'll be prompted on whether or
not you want to drop the relationships to the original table when you delete
it. Drop them.

I would also recommend creating a new database and importing these good
tables and the rest of the objects into it, so that you leave any corruption
behind.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Julie]

Thanks to both John and Gunny for their suggestions.

I tried it and, unfortunately, both append queries abend with
"Record is deleted" even though neither refers to the value in
the bad record. (Target table is empty after the abend.)

Too bad as it sounded so promising!

Any other ideas?

 

[Guest]

Just an idea Julie.

Thought for today

Might this be because you are only deleting the top level record and not
deleting the sub record associated with that master on the linked tables???

But hey, I'm no expert

Really hope you get to the bottom of the problem

ATB Mike B

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all,
you have shouted at and blamed the innocent,
and asked everyone you know,

READ THE INSTRUCTION MANUAL.

 

['69 Camaro]

Hi, Julie.

I tried it and, unfortunately, both append queries abend with
"Record is deleted" even though neither refers to the value in
the bad record. (Target table is empty after the abend.)

This means that either you have multiple bad records, or you have a
corrupted table. If it's multiple bad records, experiment with appending
smaller chunks of records, and don't forget the ORDER BY clause on the
primary key. For example:

INSERT INTO tblNewTable
SELECT *
FROM tblOrigTable
WHERE ID <= 100
ORDER BY ID;

If that works, try the next chunk:

INSERT INTO tblNewTable
SELECT *
FROM tblOrigTable
WHERE ID BETWEEN 101 AND 200
ORDER BY ID;

.. . . and so on, avoiding the primary key of the record you know is bad,
until one of the chunks of records fails to append, which is where you'll
find more bad records. When it does fail, skip to the next chunk and try to
append those records. If it works, keep trying to append the chunks of
records that come after the bad records.

Then come back to the chunks that failed, split those chunks in half and try
appending the lower half. If it doesn't work, split the lower half in half
again and try appending again. Keep at it with these binary splits until
you find which records are bad so you know to avoid them in the append
queries.

If, on the other hand, you have a horribly corrupted table, you won't be
able to append records into a new table. If that's the case, you need to
restore from a known good backup.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Julie]

Thanks, Gunny. I had already started such an exercise
and discovered that there were, in fact, three records
that have trouble, although only one of them "looks" bad.

I know what I have to do now. Thanks again.

 

['69 Camaro]

You're welcome, Julie. Good luck with it.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blog: http://DataDevilDog.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.