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

DoOpenDatabase:

     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

           Else

                 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