Corrupt Record Removal

[Chilli1]

I have a number of linked tables in a database written in Access 2000 which for 6 months has worked fine.

However one record in one table is corrupt. In Tbl view it reads simply as #Error in each field. The Jet Engine Error is #3001.

Is there any way this record can be removed from outside of access? I have tried exporting the table to excel and text delimited but get failure messages on each attempt.

I have also tried compacting and repairing, recreating the database and trying to import the table which also doesn't work.

Any help anyone could give me would be greatly appreciated.

 

[Maquis]

That's happened to me before. It's a real pain isn't it.

What I did was create a copy of the table (structure only) and then append all the records except that one to the new table. Then delete the old table and rename the new table.

 

[rbowes]

This happened to me too! and I was darn mad about it. The most important table in the database and one screwy record crashed thousands of records. How did you determine the corrupt record?

%-(

 

[Maquis]

I determined it through trial and error. I just kept making select queries which returned different recordsets until I had narrowed down which record was causing the problaem.

 

[Chilli1]

Thanks Maquis. We've tried your suggestion but no matter how I run the selection criteria the error record goes with it.

This one is simply a classic case of using the last backup I made (only a month old thank heavens) and cut and pasting indvidual records from the tables that aren't corrupt. Hopefully I'll be able to get this done over the weekend if I don't eat, sleep or drink!

With regard to seeing the error in my case it was easy. In tbl view every field has #error written through it. You've got to be pretty quick to get to the place where the error is before the system falls over.

Thanks Chris