How to diagnose MS access crashes

by

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.

More Related Contents:

  1. How does one decompile and recompile a database application?
  2. What are the rules governing usage of parenthesis in VBA function calls?
  3. Autonumber value of last inserted row – MS Access / VBA
  4. Code to loop through all records in MS Access
  5. Bang Notation and Dot Notation in VBA and MS-Access
  6. How do I force VBA/Access to require variables to be defined?
  7. VBA: Difference in two ways of declaring a new object? (Trying to understand why my solution works)
  8. Equivalent cURL in VBA?
  9. How do I access the selected rows in Access?
  10. Run-time error ‘3061’. Too few parameters. Expected 1. (Access 2007)
  11. Prevent enabling of shift bypass – Access 2010
  12. VBA procedure to import csv file into access
  13. Transform multiple rows into columns with unique key
  14. how to show query results in a datasheet in the same form?
  15. Access VBA: Find max number in column and add 1
  16. MS Access prepared statements
  17. Create sequential ID value based on the year that a record is added
  18. UrlDownloadToFile in Access 2010 – Sub or Function not Defined
  19. Null values for variables in VBA
  20. Using a centralized VBA module in multiple Access databases
  21. How to execute a query in ms-access in VBA code?
  22. How to add a menu item to the default right click context menu
  23. VBA – Run Time Error 3271 using DAO object
  24. Trouble with InputBoxes
  25. Get Name of Current VBA Function
  26. Combine rows / concatenate rows
  27. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  28. Microsoft Access condense multiple lines in a table
  29. What is the equivalent of Select Case in Access SQL?
  30. Exporting MS Access Forms and Class / Modules Recursively to text files?

Leave a Comment