How to diagnose MS access crashes

Question

We have a custom program written in Access that has odd crashes in it. We have added error handling that records and emails any crashes that happen inside of our own code and that has allowed us to fix most errors that we have generated, but sometimes the crashes happen outside of our code.

An example of one that we found that cropped up new in 2013 -- we have a form that would crash after editing data in a certain field -- new entry was fine but any edits after the record was created would result in a full crash and shutdown of MS Access. We spent time and eventually tracked down that some code of ours was forcing the form to move to the next record, this field was the final field on the row though so Access itself was also attempting to move to the next record. This had been in the system since 2007, but started causing program shutdowns in 2013.

is there any way to trap and diagnose program level crashes inside MS access?
The windows event viewer only shows the following:

Faulting application name: MSACCESS.EXE, version: 15.0.4454.1501, time stamp: 0x50a35ef4 Faulting module name: MSACCESS.EXE, version: 15.0.4454.1501, time stamp: 0x50a35ef4 Exception code: 0xc0000005 Fault offset: 0x00116452 Faulting process id: 0x1398 Faulting application start time: 0x01ce6e665043d8be Faulting application path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE Faulting module path: C:\Program Files (x86)\Microsoft Office\Office15\MSACCESS.EXE Report Id: 6cfcb0eb-da62-11e2-8966-842b2b86f028

Answer 1

This is an old thread, but it came up as one of the top results on Google so I thought I would give an answer. What steps can you take when you get "Access has encountered a problem and needs to close". Usually in the event log you will see:

Faulting application name: MSACCESS.EXE, version: 15.0.4869.1000, time stamp: 0x57e12b41
Faulting module name: MSACCESS.EXE, version: 15.0.4869.1000, time stamp: 0x57e12b41
Exception code: 0xc0000005

These can be frustrating to troubleshoot. Below is the list of actions I take, from least invasive to most invasive. I am not just inventing these fixes - over the years I have personally witnessed each fix resolve the issue.

Decompile Database

You indicated that it is the policy to decompile every release. Good policy - but do it explicitly after EVERY time you get an error. The reason is because you may be fixing the core problem, but not noticing due to a corrupt container.

• I create a shortcut that loads the database with the "/decompile" switch.
• hold down shift when double clicking on this shortcut so any auto runs are skipped and you go straight to the navigation window.
• Once the database is loaded, you will need to click the Compact and Repair button. Hold down shift again as the database reloads.
• Now go and compile the code and save. That's the process I use for decompiles.

Test Computer Memory

Especially if the crashes are restricted to one or two machines - do this.
Check the event viewer. Is there quite a few "Error" messages which describe an application crash, and the faulting module is different? If so then odds are good that if it isn't a corrupt windows install, you are looking at a memory issue.

I am sure there are many great memory testers, but I encourage you to use a proper test that will catch dropped bits. MemTest86 is an oldie but a goodie. There is the current version and some equally good forks.

Start the test and let it run during working hours. I have had bad power in the building cause memory errors, so keep the variables the same.

Remove Binary Data from Form

Sometimes the crashes occur in a single form or report. If it is corrupt binary data, then the crashes should be occurring on different computers, with different users. If this is the case then follow these steps. (Advanced Users Only)

1. In the immediate window save the object as text.

   Application.SaveAsText acForm, "MyForm", CurrentProject.Path & "\MyForm.txt"

2. Rename the original form item (e.g. rename to MyForm_Bak)

3. Open the exported file in notepad
4. Delete the "Checksum=" line (should be on line 3)
5. Clear out binary data
   • Look through the file.
   • There will be lines that start with "Parameter = Begin" and have lines of encoded binary data, ending with a line consisting of "End"
   • When you locate one of these lines, you will need to (inclusively) delete all lines from the Begin to the End.
   • The parameters you should delete are: NameMap, PrtMip, PrtDevMode, PrtDevNames, PrtDevModeW, PrtDevNamesW
   • All of these blocks should appear BEFORE your form control definitions
6. While you have the file open, scroll through the rest of the file and look for anything that catches your eye, especially in the VBA module code at the bottom.
7. Save the file

8. In Access, on the immediate window, load the form back in

   Application.LoadFromText acForm, "MyForm", CurrentProject.Path & "\MyForm.txt"

9. Decompile / Compact Repair / Recompile

10. Open the form and hopefully everything is working better.

Get rid of "OLE Object" fields

If you have images or other data stored in Access itself then you should find a better approach. When the OLE data is stored, it is stored according to the software (and version of software) on the computer storing it. When another computer goes to display that OLE Object data on the form, but doesn't have the exact software / version installed - you quite often wind up with a crash.

If you are storing image data, then a better approach is to store the filename, and instead save the images to a standard location. Newer versions of access have the native controls to make this the way to go.

Rebuild the entire database

This is a lot of work, so I would save this for when you have exhausted all other options. You only need to do this if the problem is occurring for all users. If it isn't occurring for all users, then it is not a corrupt database.

Similar to the steps in removing binary data, you are going to rebuild your database from scratch. By the time I reach this step I am in all-out-paranoid mode. Maybe its a bit ritualistic but I do everything meticulously with no short cuts and great care in not "preserving" the corruption through direct copying or import/export. As my last stand, I don't think this has ever failed to resolve the issue. Thankfully I haven't had to do this since the days of Access 2000.

• Create a new access database container.
• Do not use the Import / Export functions
• Tables:
  • For each table in the old access container, create a new table in the new container. From design view, copy/paste the field definitions.
  • Export the old data to XML or CSV, and then import from there.
• Queries:
  • Go into SQL view in the original query, copy and paste the SQL text into the query of the new database.
• Forms / Reports:
  • Use the Application.SaveAsText function to export the forms/reports
  • Strip binary data from the forms and review
  • Use the Application.LoadFromText function to reimport them
• Macros
  • Recreate the Macros.
  • In Access 2007 and newer, with the new Macro system you can simply open the Macro, Select all (Control + A) and paste into a blank Notepad document. Copy again from notepad and paste into a blank macro within the new access container
• Modules
  • Select all code (Control + A) and paste (Control + V) into the new database container
• Data Macros
  • I haven't had to do this since Data Macros have come out, but you would use the SaveAsText / LoadFromText functions to export the data macros off the tables.

When all is said and done - you should have a very clean database container.

Remove other variables from the test

Network Corruption

Do not load the client off of a network. Put it on the local drive and run it from there.

Corporate Builds

If you are in a corporate environment that is using "computer builds" and have had no success with Decompiling, Testing Memory, and stripping Binary data - then refuse to do further testing until the IT team can provide the user with a test machine that has only Windows, Office, and Service Packs installed. I usually prefer to do the install myself so I know I can trust it. All software and updates should be installed by hand without using unattended installs. Do not install antivirus on this machine.

I have had IT departments refuse this out of sheer F.U.D. and unreasonableness - if this is what you encounter then wash your hands of the issue under the "Help me help you" context.

Bad Power

As mentioned in the memory section - power fluctations can cause computer errors. If the database is in an industrial building - then try to get your hands on a power conditioner or a UPS that provides clean power (off the battery, not off the main passing through a Metal-oxide Varistor)

Also, check the power supply cable that is plugging into the power bar or outlet. Make sure that the gauge and voltage specs are sufficient. I say this because IT departments often leave power cables plugged in at the station and just remove the machine. After many years, they are using beefier power supplies, but haven't switched out the cable. It makes a difference. When in doubt, bring a new, thicker cable.

Addendum

Since originally posting this I have ran into a few new ones. One that has hit me multiple times is the ODBC drivers when moving to Access 2016. If you have a database that works fine under Access 2013, but crashes quite reliably in Access 2016, then the issue may be the ODBC driver. Off the hop, try to find out if there is an updated driver. If no success there, then confirm whether it is the ODBC driver by creating a new database, and making an ODBC call in VBS. If you get the same crash - its the driver. Without an updated driver, you'll just have to keep it at 2013. I encountered this with PostGreSQL ODBC driver with a few databases.

Answer 2

Every single function on every single form in every single Access database should have a flow that looks like this:

Private Sub btnMyButton_Click()
Dim MyVar as String
On Error GoTo ErrorHappened

'Do some stuff here...

ExitNow:
    Exit Sub

ErrorHappened:
    MsgBox Err.Description
    Resume ExitNow
End Sub

In the ErrorHappened section, you can have it write to your table that tracks bugs. If you change all of your Subs and Functions to flow like this, you should be able to trap every single issue your database has. Maybe write out the Err.Number as well as Err.Description.