Code Samples for Businesses, Schools & Developers

Updated 3 June 2018

The ListErrors procedure below creates a table AccessErrorCodes and populates it with all error codes and descriptions for your current version of Access.

The number of error codes listed depends on the Access version.
For example, there are 2976 error codes for Access 2010 and 3063 error codes in Access 2016.

The other 2 procedures CheckTableExists and MakeErrorCodesTable are used as part of this procedure.

Place all of these in a standard module and run the ListErrors procedure

NOTE:
This was based on code originally by Hans Vogelaar which I adapted to obtain various missing error codes.
The modified code below creates the same error codes as those used in the Access Error Codes example database.



CODE:

Option Compare Database
Option Explicit

Sub ListErrors()

'Purpose : Populate an AccessErrorCodes table with all 2976 current Access error codes and descriptions
'Author(s) : Colin Riddington - adapted from code originally by Hans Vogelaar
'Date : 3 June 2018
'Requires: table ErrorCodes with 2 fields ErrNumber (PK - integer) & ErrDescription (Memo/LongText)
'If table doesn't exist, it will be created

On Error GoTo Err_Handler

       Dim rst As DAO.Recordset
       Dim i As Long, N As Long
       Dim strErr As String
       'make table if it doesn't exist
       If CheckTableExists("AccessErrorCodes") = False Then MakeErrorCodesTable

       Set rst = CurrentDb.OpenRecordset("AccessErrorCodes", dbOpenDynaset)

       For i = 1 To 65535

            'get generic VBA errors
             strErr = Error(i)
             'omit unwanted codes
             If strErr <> "" And strErr <> "Application-defined or object-defined error" Then
               '  And strErr <> "|" And strErr <> "|1" And strErr <> "**********" _
              '   And strErr <> "0,0" And strErr <> "(unknown)") Then
               rst.AddNew
               rst!ErrNumber = i
               rst!ErrDescription = strErr<
               rst.Update
             End If
       Next i

       For i = 1 To 65535

        'now repeat for Access specific errors
             strErr = AccessError(i)
             'omit all unwanted codes
             If strErr <> "" And strErr <> "Application-defined or object-defined error" _
                 And strErr <> "|" And strErr <> "|1" And strErr <> "**********" _
                 And strErr <> "0,0" And strErr <> "(unknown)" Then
               rst.AddNew
               rst!ErrNumber = i
               rst!ErrDescription = strErr
               rst.Update
             End If
       Next i

       N = rst.RecordCount
       rst.Close
       Set rst = Nothing

       MsgBox "All " & N & " Access errors have been added to the table AccessErrorCodes", vbInformation, "Completed"

Exit_Handler:
   Exit Sub

Err_Handler:
   If Err = 3022 Then Resume Next
'continue where code already exists
   MsgBox "Error " & Err & " : " & Err.description & " in ListErrors procedure"
End Sub

'=============================

Public Function CheckTableExists(TableName As String) As Boolean

On Error Resume Next


'If table exists already then strTableName will be > ""
   Dim strTableName As String
   strTableName = CurrentDb.TableDefs(TableName).Name
   CheckTableExists = Not (strTableName = "")

   'Debug.Print strTableName & ": " & CheckTableExists

   'next 2 lines added to allow more than 1 table to be checked successfully
   strTableName = ""
   TableName = ""

End Function


'=============================

Sub MakeErrorCodesTable()

On Error GoTo Err_Handler


'create new table
           Dim tdf As DAO.TableDef
           Dim fld As DAO.Field
           Dim InD As DAO.index

           Set tdf = CurrentDb.CreateTableDef("AccessErrorCodes")

           'Specify the fields.
           With tdf
               Set fld = .CreateField("ErrNumber", dbLong)
               fld.Required = True
               .Fields.Append fld

               Set fld = .CreateField("ErrDescription", dbMemo)
               fld.Required = True
               .Fields.Append fld
           End With


           'create primary key
           Set InD = tdf.CreateIndex("PrimaryKey")
           With InD
               .Fields.Append .CreateField("ErrNumber")
               .Unique = False
               .Primary = True
           End With
           tdf.Indexes.Append InD


            'Save the table.
           CurrentDb.TableDefs.Append tdf
           Set fld = Nothing
           Set tdf = Nothing
           Set InD = Nothing

Exit_Handler:
   Exit Sub
   
Err_Handler:
   MsgBox "Error " & Err & " : " & Err.description & " in MakeErrorCodesTable procedure"

End Sub



Return to Code Samples Page Return to Top