Is it possible to programmatically detect corrupt Access 2007 database tables?

Question

Hi

Is it possible via code to programmatically (from .NET for example via SQL query) to ask an Access database if it is corrupt or have tables with corrupt rows in it?

//Andy

Answer 1

Here is some VBA that can be used to check for an error in previous releases of Access. It may also suit 2007.

Sub CheckForErr(tablename)
Dim rs As dao.Recordset
Dim db As Database

Set db = CurrentDb

Set rs = db.OpenRecordset(tname)

With rs
   Do While Not .EOF
        For Each fld In rs.Fields
            If IsError(rs(fld.Name)) Then
               Debug.Print "Error"
            End If
        Next
        .MoveNext
    Loop
End With

rs.Close
Set rs = Nothing

End Sub

Answer 2

None of the application/database level objects have such an "isCorrupted" property.

Furthermore, corrupted databases do not have a standard behaviour. Depending on the situation, database might not open at all (file is not recognized as a valid mdb file). If it opens, error might occur immediately or when using/opening a specific object (table, form, or VBA code).

In these conditions, I do not think there is a positive answer to your question.

Note: In addition to the standard compact/repair option of Access, exporting object to other databases (or importing them from the corrupted database) as well as the non-documented .saveAsText command can be of great help.

Answer 3

We had network issues causing corruption in an access database on our shared drive and I spent a lot of time cleaning up corrupted rows from a couple specific tables.

The memo fields in particular were a good indication of corruption because they are not stored inline like the rest of the data but are kept in a separate table. I could open up the corrupted table in datasheet mode and try to determine which rows were corrupted by putting the focus on a corrupted memo cell--if there was corruption then I would immediately get an error.

Corrupted Int and Date columns would have odd values (3/18/1890, -11100910, etc.) but would not actually throw any errors when I read their values.

This is why cycling through all records and fields in the database as shown in the VBA from a previous answer makes some sense but will perform most reliably only if you have memo field corruption.