MS Access - How to prevent crashes in VBA?

Question

I'm new to VBA in Access. I have set up a query that uses some VBA to requery on a combo box selection. This works great however every so often Access will randomly crash.

Is there any "if error" statement I can put in my VBA code that will pop up a message rather than completely crashing?

Look at decompile and compact & repair as well as error handling. Mostly crashes need a repair or decompile rather than an error handler. — Fionnuala, Nov 28 '16 at 05:40
See this answer for a full [Decompile](http://stackoverflow.com/a/3268188/3820271) — Andre, Nov 28 '16 at 10:14

score 1 · Answer 1 · answered Dec 06 '16 at 19:20

Error handling in your application is a separate issue from Access crashing. Even if you reach the low limits of Access (https://support.office.com/en-us/article/Access-2010-specifications-1E521481-7F9A-46F7-8ED9-EA9DFF1FA854), it shouldn't be crashing, but giving you an error message. That's a bug not of your doing. I've used Access for a while, and sometimes you have to work around the bugs, as well as the limits.

Sometimes, the VBA compiler fails to get things right after you make a change. If you Cut and Paste the text of an entire Module, it seems to force the compiler to recompile that whole Module. Always use the Debug > Compile menu item to compile and check your code. Then save.

Sometimes, doing the Compact and Repair will resolve problems. In fact, you need to Compact and Repair, because it seems that the file will bloat non-stop otherwise, and it has a 2GB limit. Do a backup first, because the Compact and Repair sometimes fails, leaving you with garbage.

Magisch gave an example of error handling in VB, which is something you should do to write a robust application (see http://www.vb6.us/tutorials/error-handling).

score 0 · Answer 2 · answered Nov 28 '16 at 08:24

First of all, your an ordinary error shouldn't cause access to crash. If it does, you probably need to clean up your file some, for instance by repairing or de/re compiling it.

Secondly, if you want to handle errors in a simple way you can do it roughly like this:

Public Function doStuff (myInput as Integer) as Integer
    On Error GoTo Error_Handling

    'something that may cause an error to occur

    Exit Function 'Important so your error handling doesn't get executed every time the function runs regardless
    Error_Handling:

    'something that you want to happen when the error occurs

End Function

MS Access - How to prevent crashes in VBA?

2 Answers2