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