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 & Personal Use

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.

See Get complete List of Access error codes and descriptions

 

This created the same error codes as those used in the Access Error Codes example database (originally taken from an MS webpage which no longer exists)

 

 

Code:

List Access Error Codes

Return to Code Samples Page