Mendip Data Systems

Database applications for businesses and schools

LogoTransparent


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

Code Samples for Businesses, Schools & Developers

Updated 03/06/2018


The procedure below creates a table AccessErrorCodes and populates it with all 2976 errors (codes & descriptions) as used by Access 2010.
If you use it in
Access 2016, it lists a total of 3063 errors

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:

List Access Error Codes

Return to Code Samples Page