unable to delete record in corrupt 2003 database table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a client with an Access 2003 database. He had a network problem,
and one record got corrupted.

I can see the record if I create a query that searches for it by a unique
Job id:

SELECT Job_Main.*, Job_Main.[JOB ]
FROM Job_Main
WHERE (((Job_Main.[JOB ])=499));

But, I cannot delete the record:

DELETE Job_Main.[JOB ]
FROM Job_Main
WHERE (((Job_Main.[JOB ])=499));

It reports 'the search key was not found in any record'.

I can locate the record by double clicking on the table. But, am unable to
delete it that way.

What tool can I use to resolve this. Repair database does not.
 
Did you try Compact and Repair on the MDB that the table is located in?

If not, back up the MDB and try
 
I've got a client with an Access 2003 database. He had a network problem,
and one record got corrupted.

I can see the record if I create a query that searches for it by a unique
Job id:

SELECT Job_Main.*, Job_Main.[JOB ]
FROM Job_Main
WHERE (((Job_Main.[JOB ])=499));
Click to expand...

I'd suggest creating a *new database* (a complete new .mdb file) - this kind
of corruption can be deep-rooted and hard to evict.

Use File... Get External Data.. Import to import all the tables *except* for
this one.

Import this table, design mode only, to get an empty table with the same field
definitions.

Link to this table and run two Append queries - one with a criterion on JOB of

<= 498

and the other with a criterion of
= 500
Click to expand...

Note, I'm carefully avoiding any "touching" of the damaged record!

If necessary, you wiill have to drop and later recreate any relationships
between this table and related tables; you will probably also need to identify
and manually reenter records in related tables for JOB 499.

John W. Vinson [MVP]
 
--
Thanks in advance, Les Caudle


John W. Vinson said:
I've got a client with an Access 2003 database. He had a network problem,
and one record got corrupted.

I can see the record if I create a query that searches for it by a unique
Job id:

SELECT Job_Main.*, Job_Main.[JOB ]
FROM Job_Main
WHERE (((Job_Main.[JOB ])=499));
Click to expand...

I'd suggest creating a *new database* (a complete new .mdb file) - this kind
of corruption can be deep-rooted and hard to evict.

Use File... Get External Data.. Import to import all the tables *except* for
this one.

Import this table, design mode only, to get an empty table with the same field
definitions.

Link to this table and run two Append queries - one with a criterion on JOB of

<= 498

and the other with a criterion of
= 500
Click to expand...

Note, I'm carefully avoiding any "touching" of the damaged record!

If necessary, you wiill have to drop and later recreate any relationships
between this table and related tables; you will probably also need to identify
and manually reenter records in related tables for JOB 499.

John W. Vinson [MVP]
Click to expand...
John - that is basically how it got resolved. Initially had tried an append
query 'where job <> 499'. If it were a select query, it would run ok, but
as an update query, it would fail (and only the 2 queries <= and >= would
work). Why is that?
 
John - that is basically how it got resolved. Initially had tried an append
query 'where job <> 499'. If it were a select query, it would run ok, but
as an update query, it would fail (and only the 2 queries <= and >= would
work). Why is that?
Click to expand...

I'm not sure anyone (even in Redmond) really understands all the ways an
Access database can get corrupted - I sure don't! But in my experience, when a
record is corrupted in this way, if you "touch" the record at all (say by
retrieving the record to see if its JOB field is equal to 499), you bring in
the corruption and your query is toast. The <= and >= criteria work off the
indexes, and the record itself is never actually loaded.

John W. Vinson [MVP]
 
Back
Top