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

Catadromous

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.

Bezant

this is a really useful guide, many thanks. Would you have any further advice on resolving ODBC issues with Access 2016? We're getting seemingly random crashes in A2016 for MDB databases using SQL backends via a DSN and linked tables. Deleting the DSN (of type 'SQL Native Client 11.0' System) in Windows' ODBC Data Source Administrator has helped in some cases, but not all. Do you have any more thoughts on ODBC issues? – Naevus

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.

Chowchow

we use error trapping very similar to this -- some errors seem to happen outside of our code, and in Microsoft's code so it skips our error handling. I am trying to determine an effective way to solve crashes that are happening outside of our code (they could still be caused by our code, but ours completes fully and then a crash happens) – Catadromous

Is your code compiled? Have you tried decompiling it using the -decompile switch, and then re-compiling it? – Chowchow

we have done that. Our policy is to de-compile and re-compile at every release. The crash that I illustrated above is typical -- a certain instance of us moving to the next record at the same time that Access is trying to move to the next record crashed the entire program. We only figured that out through elimination, and no error number was ever given other than the windows level error I pasted in my first comment. – Catadromous

Maybe you are filling a variable with an integer when it needs to be a long ? That can cause an "overflow" and could be obscure. – Melone

I know that this question is very old, but I was looking and wanted to add our last input. We have tracked a variety of these errors over time. Many of them are flaws in access itself that started after upgrading our office users from 2007 to 2010 and 2013. most we were unable to fix, but wrote code to avoid the situations causing the error. – Catadromous

Answer 3

For anyone else who stumbles across this thread in Google here are another couple of causes for random Access crashes:

Just for background, I have a form with a subform on it, loading data from linked tables on a server.

• If you have "_Enter" events on the fields on the parent form this may cause Access to go into an endless loop, repeatedly firing these events. This occurred when I was on the subform, and pressed a button on the parent form. I replaced all these with "_GotFocus" events instead which fixed the problem.

• Do not reference the subform in the onLoad even of the parent form. This one was random, the form would load fine and then the next time you went into the same form, with the same record, it would cause Access to crash. So I removed all references to the subform in the onLoad event of the parent form and it now seems to work fine. I especially noticed this when my internet connection was slow, it appeared that the main form was loading before the subform and so when it referenced it it hadn't loaded and so caused all sorts of issues with Access, which invariable ended in a random crash of Access.

Housemaster

Answer 4

2020 update and additional answer. I had the same "crash when opening macro" random problem on an app originally built in 2010 that I was trying to upgrade to 2019 Pro. Here's a simple way to fix the problem and help yourself out...Clean the app. When upgrading your access app from earlier versions to 2019 Pro or later, delete any and all development tables, queries, macros, forms, anything you do not use in the app...old, outdated, whatever, check everywhere in your app and GET RID OF IT! problem solved and your app is now less cluttered and easier to maintain...worked great for our upgrade situation.

Mcnally

Answer 5

I use Access 2010. I made a Report (a true copy of a given form) by going to File -> Saving object as ->The Name of the Form AS REPORT. When opening the Report in Design View, and after making some changes, I tried to save the changes. Access kept on crashing. After lots of hours finally, I found that the Report was having in it some buttons. When I replaced these with a control box. That was it. Everything is working great. Hope that this saves some people valuable time, and with no signs of what to look for.

Unprejudiced

Answer 6

Check for str() function call. After the update from 2010->2019 it crash sometimes with this call.

Topdrawer