Search key was not found error

[sheusz]

Hi all,

I have a table that seems to contain a corrupt record but I can't delete it.

If I select the record in dataview and then try to delete it I get the error message "The search key was not found in any record"

The CustomerID field (a text field)is blank (it shouldn't be) and I am not able to make an entry in to the field without getting the above error. This field has as its Row Source CompanyName in the Customers table.

SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;

I notice also that the Memo field named Sectioning shows #Error that I can't remove. I feel that the problem may be related to the Memo field.

The database functions perfectly despite this corrupt record, however I would love to get rid of it.

I am using Access 2003 with SP2

Does anyone have any suggestions?

 

[Remou]

Are you saying that when you copy the corrupt database and run a compact and repair against the copy, you still cannot delete the problem record?

 

[sheusz]

Yes, if I try compact and repair Access crashes!

 

[Remou]

Import everything except the corrupted table into a new database. Copy it. With the copy, import "definition only" of the corrupted table. Now the awkward part. Can you create a query to exclude the corrupted record? If so, created an append query that gets the data from the external corrupted table and appends it to the definition only table. If not, there is more to do.

 

[TheAceMan1]

How are ya sheusz . . .

[ol][li]Right-click the table and select copy.[/li]
[li]Right-click a blank area in the tables window and select paste.[/li]
[li]A paste dialog will popup. Select Structure only. Enter a different name for the table and clock ok.[/li]
[li]Construct & run an Insert Query to insert all records, save the bad.[/li]
[li]Check that new table to verify the insert.[/li]
[li]Delete the old table[/li]
[li]Rename the new table the origional name.[/li]
[li]Done![/li][/ol]
Your Thoughts? . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[Remou]

Are you sure it is a good idea to copy a table in an unstable database?

 

[sheusz]

My knowledge of queries is very basic. How do I create an insert or append query that excludes the corrupt data?

 

[Remou]

Do you have a unique company name in the record? If so:

SELECT *.* FROM Customers WHERE Customers.CompanyName <> "The Incorrect Company Name

 

[sheusz]

I don't think there is a unique name for this record. The field CompanyID is blank and i can't populate it.

 

[Remou]

You mention that there is a field company name:

SELECT DISTINCTROW Customers.CustomerID, Customers.CompanyName FROM Customers ORDER BY Customers.CompanyName;


For the most part, companies have different names, so try the query I suggested.

 

[sheusz]

OK thanks. I'll try that out.

 

[sheusz]

Remou

Thanks for your help. I have made some progress. However I have found that because of the corruption in one of the fields the Jet Database thinks that the record is in use and will not allow the append query to run if I include that field in the selection.

If I sort the table with the corrupt record/s (there are 2 of them) is there a way that I can get the query to run on say records 3 to 800 (inclusive) and so prevent it from parsing the corrupt record at all?

Thanks for your help so far.

 

[Remou]

If the table is not very large, you could try a cut-and-paste, alternatively try:

http://www.mvps.org/access/tables/tbl0018.htm

 

[sheusz]

Thanks Remou,

Sure learnt a lot with this exercise. The link was especially useful.