How to repair corrupt MS Access tables in event of 'The search key was not found...' error?

How to repair corrupt MS Access tables in event of 'The search key was not found...' error

?

Table corruption in MS Access cannot be ruled out completely. A table's records can fall prey to corruption in many instances including bad network connection or server getting locked up for few seconds. In such situations, you may come across a table with #deleted or square boxes in the fields or may even receive an error message. While on some other occasions, you won't be able to delete or modify a record. The corrupted table problem can occur frequently in case of larger databases. However, you can troubleshoot the problem by employing various corrective methods like db.Execute method or compact and repair utility. In case, these solutions do not come to your rescue, you must consider switching to a third-party access recoverysoftware.

Consider a scenario, wherein, you are trying to delete a record in one of your Microsoft Access 2000 database table, and instead receive the following error message:

"The search key was not found in any record."

Cause

The above error clearly indicates that the MS Access table is corrupt. Corruption can sneak in due to many factors, and hence it is important to consider some steps in order to decrease its possible chances. Some steps are described below:

Maintain the database on a dedicated server

Ensure the server has power conditioning

Avoid memo fields in Access tables among others

Resolution

Now, that the corruption has occurred in a table. It is important to isolate the issue by adopting the following access database recovery steps:

To fix corruption issues, use inbuilt Compact and Repair utility. You can follow the below-mentioned steps to repair an Access file which is not open:

Close the current MS Access file

Go to Tools menu, point to Database utilities, click Compact and Repair Database

In the Database to Compact from box , specify the Access file you want to compact, and click Compact

In the Compact Database Into dialog box, specify a name, drive, and folder for the compact Access file.

Click Save

Or you can use the Microsoft Data Access Objects (DAO) db.Execute method. Below are the steps:

Make a copy of the corrupt database, exclude the corrupt table (s). Name the database as Recovered_Database

Click Modules and then click New

Put the following code in the module:

Sub Recovery()

Dim db As Database

Dim sql As String

' Set the database to current database.

Set db = CurrentDb()

' Set sql to a string that represents the query to recover data from

' the corrupted table. is the name of corrupted table.

sql = "SELECT .* INTO in " & _

"'Recovered_Database.mdb'" & _

" FROM "

db.Execute sql

End Sub

On the File menu, click Save, and Save the module as Module1

Go to Run menu, click Run Sub/UserForm

Repeat steps 3-6 for each corrupt table

After completing the above-mentioned steps, test the recovered database to the production environment (It is suggested not to delete the damaged database until you confirm the recovery)

If you are still not able to recover Access database, then opt for a professional Access Recovery utility. Such tools repair corrupt Access databases and recover data efficiently.

Stellar Phoenix Access Recovery is a powerful application which repairs a corrupt .mdb or .accdb file and stores it to a user-defined location. Compatible with MS Access 2007, 2003, 2002 and 2000, the access database recovery software recovers all components including tables, forms, reports, queries, macros, and modules etc.

POST COMMENT

Mac And Pc Computer Repair In Playa Del Rey From West La Computer Services PC Repair Online: Ensuring Quick Technical Assistance Open up your living space with sliding doors & Windows Hp Laptop Repair Parts Flush Dns To Repair Internet Connectivity Windows Phone is the nice gadget Pc Repair Business: Know About It Marina Del Rey Premier Mac And Pc Computer Repair: West La Computer Services Our registry cleaner will be your best and last choice Common iPod Defects That Need a Repair How To Tell If A Laptop Repair Is Great Option Or Not Podnutz Laptop Repair Videos Change your home look by sliding windows and doors