How To Check An Access Database For Errors

Also, if data is entered into a database that has errors, that data (and more) might be loss forever.

Below is a Function that can be used every time a program using an Access database is loaded. This code will not catch 100% of the errors — I have yet to discover any methods that do. In fact, I’ve had corrupted databases and this code did not even tell me there was an error.

This Function returns a 0 if no errors were found, or, the error code if there were.

Function iCheckMDB (sDatabase As String) As Integer
     Dim bDidRetry As Integer
     Dim dbTest As Database
     On Error Resume Next
     bDidRetry = False
     iCheckMDB = 0
     Err = 0
     Set dbTest = OpenDatabase(sDatabase)
     If Err = 3043 Then
           'Disk or Network Error
           iCheckMDB = Err
           Exit Function
     ElseIf Err = 3049 Then
           'Database corrupted
           If Not bDidRetry Then
                 'Try to repair database
                 RepairDatabase sDatabase
                 bDidRetry = True
                 GoTo DoOpenDatabase
                 iCheckMDB = Err
                 Exit Function
           End If
     End If
End Function

NOTE: Remember to run this code before you use the database or load any forms with data controls on them.


This tip is reprinted from the VB Tips & Tricks Volume 1 book.
Parts of this tip was submitted by: David McCarter