Access File Corruption

Introduction

If you use Access for any length of time you are likely to come up against a corrupt database at some point.

Corruption can occur in pretty much any Access object (tables, modules, forms, etc) but because (as a good developer) you always split your apps into front end (forms, modules, queries, reports) and back end (tables) and only ever work on a copy of the production front end, you only really need to worry about corruption in your back end files.

If you do have corruption in a front end production file, try running a normal Compact & Repair (see below) and if that does now work, try opening the file via Start and Run and specifying the command line parameter - decompile. A Google search on 'access command line decompile' should get you started. The rest of this article assumes a corruption in a backend data file so only considers problems with table objects.

How do files become corrupt?

Corruption problems usually occur in the back end files as these are usually accessed over the network and are continually being written to so it only takes one 'network glitch' to cause a record to be damaged.

Depending on the extent of the damage, the file may become so corrupt it can not be opened at all, or you may only receive an error message when someone attempts to access a damaged record (usually through a form or a report). If the record is infrequently accessed, the problem may not become apparent for some time.

Repairing Corruption

The first step is to use Access' built in 'compact and repair' tool. If the file with the corruption cannot be opened at all, open Access without opening a file, then choose Tools, Database Utilities and select Compact and Repair - you will then see the Open dialog and can select the corrupt file.

If a corrupt file will still open in Access, you can open the file and then choose Compact and Repair.

If the Corrupt and Repair tool fails, try as many other versions of Access (2000, XP, 2003, etc) as you can to repair the corruption. With each new version the repair tool is modified/updated somewhat so it is possible that while one version cannot repair the corruption, another one can.

If 'Compact and Repair' fails

If the corruption prevents the file from being opened, then you are pretty much at the end of the road. You now need to try one of the services available on the internet for repairing corrupt files or refer to a backup copy (it is worth stating here that your Access backend files should be backed up automatically every night as part of the general network backup procedures).

If the corruption is such that the file will open, but some of the records cause errors to appear when you attempt to read them, you can try deleting (and then re-entering) the corrupt records. Of course, you will need to identify all of the corrupt records first. You can use the code below to perform this check.

Public Function CheckForCorruption()

Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim rs As DAO.Recordset

For Each tbl In CurrentDb.TableDefs
    Debug.Print , ""
    Debug.Print , "------" & tbl.Name & "------"

    Debug.Print , , tbl.RecordCount

    Set rs = CurrentDb.OpenRecordset("select * from " & tbl.Name)

    Do Until rs.EOF
        For Each fld In rs.Fields
            Debug.Print tbl.Name, rs.AbsolutePosition, fld.Name, rs.Fields(fld.Name)
        Next
        rs.MoveNext
    Loop

Next

End Function

The code above loops through (and prints to the debug window) the value in each field for each record for each table in the database. Should a corrupt field be encountered, the code will stop with an error, but you can see from the debug window the details of the field prior to the one with the corruption. Because the debug window shows the absolute position (record number) you can open the relevant table and move directly to the problem record.

Having identified a problem record, you will probably need to delete it and re-enter it.

Keep re-running the code until it completes without an error. You can then be sure there are no more corrupt records in the database.

Do note that in a large database, the code will take some time to run as every single field is being checked.

If you would like to comment on this article (good or bad) please send me an email at the address at the top of the page.