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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s