Access Error Number Message Lookup

Have you ever had an Access error number, but not the error message that explains what it is?  

The AccessError() function is a quick way to get the error message associated with an Access error number:

The Pipe Placeholder Character

The pipe character (|) is a vertical bar (available via [Shift] + [\] {backslash} on an English keyboard).

The AccessError function uses this character as a placeholder for instance-specific text in an error message.

For example, Access Error 3211 is "...could not lock table '|' ..."  The pipe character in the generic message is replaced with the actual string that was used in the line of code that raised the error message:

Official Documentation

The AccessError function is actually a method of the Access.Application object.  Here's a description of what it does from the official documentation:

You can use the AccessError method to return the descriptive string associated with a Microsoft Access or Data Access Objects (DAO) error.

It's worth noting that this is a Microsoft Access-specific function.  In other words, you wouldn't be able to call it from another Office application, like Excel or Word.*

(* This is not technically true.  You could create an Access Application object via COM in any VBA environment and then be able to call the AccessError method from that object.  Of course, that would only be useful if you wanted to check Access-specific error message strings in that other Office application, which seems unlikely.)

Use Cases

Honestly, I can't ever remember using this method inside of my application code.

This is a feature that I use almost exclusively from the Immediate Window when I just want a quick way to determine what an Access error number represents.

This would be most useful if you came across an error handler that had a Select Case Err.Number block and all of the individual Case statements used numbers with no accompanying comments, like this:

    Select Case Err.Number
    Case 2452   
        'Do something
    Case 2455   
        'Do a different thing
    Case 2467   
        'Do something else
    End Select

Armed with the AccessError method, you could improve this code by adding a comment with a brief description of each error number:

    Select Case Err.Number
    Case 2452   'invalid reference to the Parent property
        'Do something
    Case 2455   'invalid reference to the property
        'Do a different thing
    Case 2467   'expr refers to object that is closed or doesn't exist
        'Do something else
    End Select

Bug Database Integration Considerations

If you are using a bug database–like FogBugz–that offers a feature to group errors based on their description, you may want to use the generic error message in the subject of the bug report (or whatever field does the grouping) and capture the specific error message in the bug event detail field.

I discuss this issue in the context of my custom error throwing function here: Throwing Errors in VBA: Bug Database Integration Issues.

Referenced articles

Image by _Alicja_ from Pixabay