Locate and Recover Corrupt Access Database Records
Corruption in Access database is not only a menace for the IT professionals, but it is also one of the computing disasters of the worst form. Microsoft Access is a boon for the small and mid-sized enterprises and any forms of corrupt records in the Access database may lead to catastrophic results, with implications of financial loss.
In order to retrieve the corrupt records in an Access database, the preliminary step is to create a MDB file. The objects and the relationships of all the tables, except for the table, which has corrupted records, are imported. Only the definition of the concerned table with corrupted records is imported. The corrupted files are mostly MEMO and OLE files and multiple numbers of records can be corrupted. A MEMO field in Access stores alphanumeric data, in large amount. Notes, descriptions, comments and address fields are often stored in this type of field. The MEMO field has several options for the rich text formatting type of property set up. The field may contain 64,000 characters. The OLE objects can store Microsoft Excel sheets, Word documents, sound, pictures and other binary data. They can store up to 1 Gigabyte of data, constrained by the disk space.
The next step is to locate the damaged or the corrupted records and this can be done in the three following ways:
- Locating damages by scrolling
- Locating damages with the help of append queries
- Locating the damages using programs
1. The damages are detected by scrolling, through the records, until Access pukes. Then move slowly to find the record, which is causing all the problem.
The Import dialog is used for the purpose, but with the importing of the table definition of the questionable table and by using the table definition only option. Now an append query gets created, so that all the fields from the old field is added to the new table. The tablename.* option is being used and then the query is run. A key field value is stated in the query within a set value, excluding the key field value of the corrupted record. Thus, the uncorrupted records are appended successfully to the new database.
The minor corruptions in the MEMO field can be located by the use of a code, which loops through the MEMO fields. If there are child relationships with the table with the MEMO fields, then the ‘autonumber’ field plays an important role. A checkbox can be added to the table for the indication of a corrupted record. After this, a table query is run for adding all records, except the one with the corrupted MEMO field to another table. A delete query can delete all the records in the original table. Then all these records can be brought back in, with the help of an append query. The child relationships are all recreated.
The missing or lost records, if any can be inserted manually by the use of a backup. The system utility or the ‘Save Restore Modify Relationship Window’ are used for copying the table relationship windows layout, as the layout for the relationship window gets screwed up. The relationships of the table are recreated. All the uncorrupted objects like forms, macros, reports, modules and other tables are imported to the new file.
There are many reasons for corruption of the records. One such situation is when two users are trying to change the same record, at the same time. The Chinese character error appears. This may be due to network problem or due to some other factors like filter not working. After the filtering for the record is done, Access can find it but on pressing delete, the “Search key was not found in any record” was found. The same error message is displayed when a delete query is found to delete the record. One solution is the compacting and repairing of the Access database and the corrupted record disappears. A strategy adopted, so that one can avoid this error is to disallow two people to access the record at the same time.
The reason for the corrupted records in the Access table is mostly caused by a bad network connection or a server getting locked for a few seconds. The corrupted record problem seems to happen more for the databases, which grow larger over a period and those, which are accessed by multiple users. These are in fact the more critical databases in the world.
There is an easy way to detect the corrupted records. The field by field copy or append is done from the corrupted table to the fresh table. The ‘autonumber’ field has to be set to 1 and the autocorrect option has to be put off.
If the option for compacting and repair fails, then we go for a professional software, which will recover lost Access database records. There are some specialised software, which can go for the MDB file repair and can recover all the related tables, indexes, queries and relations.
However, there are some preventive methods for the avoidance of such tables with corrupted records. One of them is of course, the avoidance of the MEMO fields in Access tables. A periodic compact and repair can be run over regular intervals of time. Other means are to put the backend database on a dedicated server. The power conditioning of the server has to be a minimum and so also for the other end user and network components.