Unexplained Crashes of Access 2010 DB (1 Viewer)

Can anyone take a look at this and provide some insight? This started happening about two weeks ago, and I can’t seem to find the root cause. Here is the scenario
I have a set of causalities that can be attributed to an event. The event is documented as an Investigation Report{IR}. (Table InvReportT)
When creating an investigation report record, the user selects causes from a set of cascading combo boxes, (try either the “New IR”, or you can choose an existing one from the IR list). The causes are held in 4 tables (LevelOneCausesT – LevelFourCausesT)
I am trying to create a method for users to filter IR by cause. I have created a query (qryCFilter) and a form, (frmCFilter [“Cause Filter” Button]) to accomplish this.
If I run the query and manually populate the criteria for the data it should get from the form, it will run perfectly over and over. If I use the form data, it will randomly crash the whole shebang. It might run 20 times in a row, then boom, Access crashes.
I have recreated the all the tables in question, at least twice. I have recreated the query and form countless times. I have copied and pasted every object in the database to a new blank database one time. Editing the query or form, (especially the query) in question in design view will almost always cause a crash at some point.
I can interact with all other tables, forms, queries and reports all day long, and the DB will never crash.
I have the FE and BE set to compact on close, so it gets compacted regularly.
Here are the error details.
[FONT=&quot]Problem signature:[/FONT]
[FONT=&quot] Problem Event Name: APPCRASH[/FONT]
[FONT=&quot] Application Name: MSACCESS.EXE[/FONT]
[FONT=&quot] Application Version: 14.0.7162.5001[/FONT]
[FONT=&quot] Application Timestamp: 5626f514[/FONT]
[FONT=&quot] Fault Module Name: MSACCESS.EXE[/FONT]
[FONT=&quot] Fault Module Version: 14.0.7162.5001[/FONT]
[FONT=&quot] Fault Module Timestamp: 5626f514[/FONT]
[FONT=&quot] Exception Code: c0000005[/FONT]
[FONT=&quot] Exception Offset: 00482c42[/FONT]
[FONT=&quot] OS Version: 6.1.7601.2.1.0.256.48[/FONT]
[FONT=&quot] Locale ID: 1033[/FONT]

[FONT=&quot]Additional information about the problem:[/FONT]
[FONT=&quot] LCID: 1033[/FONT]
[FONT=&quot] skulcid: 1033[/FONT]

In my searching, I’ve found lots of possible explanations for this, and most of them refer to corrupted data, or code, but I can’t find anything that stands out, so I’m hoping someone more knowledgeable than I could take a look around. I’m open to suggestions. I have attached the DB in a zip file.

Haven't downloaded file, another suggestion, in case you haven't seen this one...It may not be due to corruption. Are there any calculations being performed (either in query, form, report or vba), sometimes if a calculation fails, it will cause access to crash. Such as dividing by zero or a null value or a negative value when it should be positive. If so, may need to edit the calculation to handle nulls or zeros such as nz() or an iif or if statement.

Convert the command button macro to VBA.
It's an option to include error handling, dont!

Run your query via the button until it breaks - click debug this should tell you where the problem is.

Thanks for your replies.
No calculations are taking place. I converted the Run Query button to vba with no error checking. It only has the one DoCmd.OpenQuery line with the two options for view and editing. I've probably ran it 100 times or more, and not a single crash. By way of testing, I tried to edit the query, by simply adding another field, and boom, crash.

Interesting. Try leaving it as VBA code and see if your users problems go away?
Do they have a run time or a full version of access?

This isn't even in the wild yet. I intend to have all the users have their own copy of a run time front end (accdr).

I see Access occasionally not responding, more often or not on a stupidly complex query, or report I've created with a billion sub form calls, but I don't think I can remember an instance of it actually crashing out.

When you edit the query - what causes it to crash? Running the query or simply switching to design view?

I'm pretty convinced your data layout is incorrect, and it would explain why you have so many repeated forms and queries. Any time you see Tables or Fields numbered Level1 Thing2, Reason4 etc you haven't normalised the data correctly.

Generally speaking identical items should be in the same table and identified in such a way to make them hierarchical if that is the desired structure.

In your current model if you decided to add a 5th level of causality you would have to completely rebuild your system.

Access crashes while I'm in design view, *if* I make any change. If I'm quick, and Save/Close the query, I can usually get away with it. Sometimes not. Here's an example. In the DB I attached, I added the InitiationDate field from the InvReportT table. Access immediately crashed. I reopened the DB, (it always leaves the lock file when this happens) and then closed the DB (This releases the lock file). Reopened the query in Design Mode, again add the InitiationDate field, quickly Save/Close, and now everything is peachy. After this, I can run the query from the filtering form endlessly. I created a continuous form, a report, and an Excel file based on the query, and can open these three objects repeatedly and they will populate according to the input in the filtering form, and query results as intended.

I repeated the example you gave. Added the field in design view, switched to datasheet view - No problem. Saved it re- run it both with the query form open and not.

Have you tried it on another machine ? Uninstalled then reinstalled Access?

No problem here either.
Do you've problem in other databases?
Follow Minty's advice, try another computer or do an Office (MS-Access) repair.

I haven't had the problem in other databases. I haven't tried it on another PC yet. That is definitely worth exploring. The receptionist has Access 2013 on her PC. I'm going to see if she will let me take over her desk for a bit. I will also take a look at Office Repair.

Exception Code: c0000005

Click to expand...

Error C0000005 is an "addressing exception" error. In essence, it is telling you that you have tried to touch some part of memory that isn't within your allowed memory space at the Windows memory management level. You developed an invalid virtual address in some obscure means. It COULD also mean that you tried to write to a read-only segment of memory, but that somehow doesn't "feel" like what you are describing.

The "C" in that code means "Severe error" and usually means that the Access Trap Handler system would have been unable to recover from this anyway. But Access didn't handle this one - Windows did.

This is often (but not always) caused by calling something "by reference" but supplying the actual argument "by value." In the cases I've seen, something is 0 when it should not be. (And 0 in the relevant calling context is Null or, for addresses, Nothing.) It can even be caused by a bad reference. Check for missing or broken references because they are notorious for not getting caught until execution (as opposed to compilation).

This CAN be a problem with a corrupted database where the corruption happens to be in a code segment. The fact that it works sometimes and doesn't work other times (your post #8 in this thread) only means that IF it is a corruption problem, you don't always use the corrupted "thing" (whatever it is that is busted.)

I am not familiar with Access Run-Time requirements, but you said in one of the responses that it even does this in design mode. Therefore, this should be possible to narrow down using "ordinary Access" methods.

If it is not too much code, single-step from your triggering action until it breaks. OR, if it is a lot of code, create a simple recordset that you can uses for your own private event log. Put a "progress marker" in it. I.e. every so often, write to a recordset with a brief message that shows where you are in the code, some unique number or word or entry point name so that you know you executed that update. Once you know the last marker that executes before the crash, you can set a breakpoint and single-step from that point in the code.

You need to see exactly which line of code triggers the crash. The solution is always tedious but I have never seen this approach fail. And of course, once your problem is resolved, you can remove the progress marker updates.

Update: I ran Office repair on my PC, and it still crashes. I ran the DB on another PC with Access 2013, and it crashes. Now it *only* crashes when the query is in design mode. I can run the query and its associated objects as part of the application, either via an accdb, or accdr FE, and I can run the query from the filtering form over and over and over, get proper results, and it never fails. I can even open the filtering form, make a selection, and double click on the query, and it will run over and over, if instead of double clicking, I open the query in design mode, and press the "Run" button then it will crash within 2-3 runs. It will also crash on any change to the query in design mode. I'm presently working through all the VBA code looking for anything irregular.

Strange, very strange - I post your database back, try it and see if you still get problem, just be aware of you don't mixed it up with your original database.
I've run it with both databases placed on C drive, on a Windows 8 system and a MS-Access 2010 version.
I know the Front-end crashes, but the problem could lay in the Back-end.

Thanks. On my end the behavior is the same. Running the query from the form works flawlessly, any attempt to edit the query in Design Mode causes Access to crash in the same way.

I'm assuming when you say it crashes when opening in design mode that you are opening it in the graphical view, or does it show up in sql view. If it is in graphical view, try this code in vba and the immediate window in order to obtain the sql statement.
Then in the immediate window type

? GetQuerySQL("qryCFilter")

Click to expand...

--put the name of the query in quotes if that isn't the correct query name.

Create a new query and be sure it is in SQL view, copy and paste the sql statement into this new query. Save it (be sure it is saved in sql view, do not change to graphical view). See if it now can be opened in SQL design view rather than graphical view without crashing. If you are comfortable editing in SQL view then continue to use and see if it permits edits without crashing.

If you need it to be in graphical view, then Copy the sql statement to a new query, save it, then change design view to graphical and see if it crashes. If that is the case, you may want to start by building up the query from its simplest and then add one element at a time to see if it continues normally or crashes and then you may be able to identify the field or table that is causing the problem.

You could also try copying the sql statement from the immediate window, pasting into a plain text editor (not word or notepad) and see if you notice any strange characters, if all looks good, then select all and copy and then paste into a new query in access and see if that will subsequently open in design view.

syschech,

I am going to try that if for no other reason than to learn something, but here is my latest update. I started playing around with other queries tied to the tables in question, and what do you know, a different query tied to the InvReportT table exhibited the same behavior, of crashing Access in design view. This points to that table somehow being corrupted. So, I started a new blank database, and created a fresh identical copy of the offending table. I started copying other objects as needed, to see if I could recreate the problem. So far, I've successfully gotten all the tables either recreated or copied. I have moved some of the forms and a few of the queries. Enough to create new Investigation Reports, provide causes for them, and filter the causes via qryCFilter. So far not a single crash in design view or otherwise. I'm not ready to blow the final whistle just yet, but maybe I've made it into stoppage time.

That is good that you have narrowed the problem down to a specific table. Now that you mention it, I had a problem like that 2 years ago and turned out there was a strange character in one of the fields. Had to import data in sections to uncover the issue, so if the data still crashes after creating a new table and importing the data, you may want to try bring in data row by row or manageable blocks of rows and running the query until you find the offending row(s). If the data came from csv or excel, you might be able to load in a text editor and see if anything strange is visible somewhere in the list.