Query run crashes Access

[The_Doc_Man]

Exception code: 0xc0000005

This is the key line. Exception code 0xc0000005 is (a) a task-fatal error due to the 0xc0" part and (b) is a memory management fault due to the "005" part and (c) an error detected by the Windows O/S itself (due to the "000" that is between the other two parts.)

ACECORE.DLL is the interface routines to the ACE database engine, code which is pre-compiled into machine code in what is sometimes called a "pure segment", which is almost certainly protected by the memory management hardware to prevent code modification, and therefore cannot be easily modified by anything you can do. I'd be willing to bet that within an unmodified Access not using a home-made or 3rd party library, you CAN'T intentionally modify that code.

It must be a caller of ACE that is doing it. This can sometimes be caused by poorly managed subroutine or function calls. However, I don't see a function of any kind in the middle of the SQL you presented to us, and you denied that any functions are involved, so it can't be a "user's faulty call argument" type of error. That leaves it as some type of corruption.

I'm going to list a few things to look at or try. I'll also give you my estimates on how likely it is to be your culprit.

1. Check your FE code references to assure that none of them are broken. (Low odds - but a fast/cheap/easy test)
Normally, your code wouldn't compile if you had broken references, but if the breakage occurred AFTER compilation, that could do it. I'm not betting on it but it is SO easy to check.

2. Compact & repair the FE file - and separately do the same for the BE file. (Slightly higher odds - and a fast/cheap/easy test) I am assuming that this is a split FE/BE type database, but even if it isn't split, the use of ACECORE.DLL tells me it IS a pure Access DB. It isn't something using an active SQL for a back-end server. That eliminates a pot-load of possibilities right there.

3. Presume that the corruption is in the code. Make a virgin database and migrate everything over to it. Look in Database Tools ribbon to find the External Data >> Import/Export option. (Medium odds) This isn't something you would do often, because unless you have somehow triggered a more extreme type of corruption, the Compact & Repair should help.

4. If you have the Office Kit available, try to do a repair on MS Office including Access. (Medium odds) Kits for Office DO have the Repair option. If you get this far, use "repair" first. It's faster - if it works.

5. If you have the Office Kit available, remove and reinstall Access. (Medium odds)

 

[arnelgp]

before You do some Serious stuff, consider creating a new db with a single table and a simple query.
if the query run as expected, there is no need for you do a Fix on your mso (if not broken, don't fix it!).
break the query to simpler ones.

 

[mishash]

This is the key line. Exception code 0xc0000005 is (a) a task-fatal error due to the 0xc0" part and (b) is a memory management fault due to the "005" part and (c) an error detected by the Windows O/S itself (due to the "000" that is between the other two parts.)

ACECORE.DLL is the interface routines to the ACE database engine, code which is pre-compiled into machine code in what is sometimes called a "pure segment", which is almost certainly protected by the memory management hardware to prevent code modification, and therefore cannot be easily modified by anything you can do. I'd be willing to bet that within an unmodified Access not using a home-made or 3rd party library, you CAN'T intentionally modify that code.

It must be a caller of ACE that is doing it. This can sometimes be caused by poorly managed subroutine or function calls. However, I don't see a function of any kind in the middle of the SQL you presented to us, and you denied that any functions are involved, so it can't be a "user's faulty call argument" type of error. That leaves it as some type of corruption.

I'm going to list a few things to look at or try. I'll also give you my estimates on how likely it is to be your culprit.

1. Check your FE code references to assure that none of them are broken. (Low odds - but a fast/cheap/easy test)
Normally, your code wouldn't compile if you had broken references, but if the breakage occurred AFTER compilation, that could do it. I'm not betting on it but it is SO easy to check.

2. Compact & repair the FE file - and separately do the same for the BE file. (Slightly higher odds - and a fast/cheap/easy test) I am assuming that this is a split FE/BE type database, but even if it isn't split, the use of ACECORE.DLL tells me it IS a pure Access DB. It isn't something using an active SQL for a back-end server. That eliminates a pot-load of possibilities right there.

3. Presume that the corruption is in the code. Make a virgin database and migrate everything over to it. Look in Database Tools ribbon to find the External Data >> Import/Export option. (Medium odds) This isn't something you would do often, because unless you have somehow triggered a more extreme type of corruption, the Compact & Repair should help.

4. If you have the Office Kit available, try to do a repair on MS Office including Access. (Medium odds) Kits for Office DO have the Repair option. If you get this far, use "repair" first. It's faster - if it works.

5. If you have the Office Kit available, remove and reinstall Access. (Medium odds)

Thank you very much for such a detailed reply.
My DB is not split, so I don't have FE and BE.
I tried both Compact & repair and migrating to a new DB - no effect.

I was trying to run a query built from other query, which contains subquered Running Total of products sales linked to a table of Purchased products batches. The expected result would not be final - I was just playing with presentation of Running Totals sold vs batches purchased in order to figure out how to automate orders' cost of goods sold.
It crashes - and now, with your inputs, I realize there is no obvious way to understand, why it crashes (I understand, Access is not supposed to crash even when it is required to run illogical query)..
I guess I will have to look for another way to control FIFO inventory accounting in my DB, maybe less automated.
Thank you very much!

 

[mishash]

before You do some Serious stuff, consider creating a new db with a single table and a simple query.
if the query run as expected, there is no need for you do a Fix on your mso (if not broken, don't fix it!).
break the query to simpler ones.

Created a new DB, exported only 3 tables queried in qrySalesCount (running total query).
qrySalesCount runs fine, just like in the original DB.
Created new Query1, added all the fields from qrySalesCount - runs fine.
Merely added tblPurchaseDetails, no joining, no fields added to Query1 - CRASHBOOMBANG!
Misterious are Access's ways))

 

[mishash]

before You do some Serious stuff, consider creating a new db with a single table and a simple query.
if the query run as expected, there is no need for you do a Fix on your mso (if not broken, don't fix it!).
break the query to simpler ones.

BTW, just tried to add tblPurchaseDetails into the RunningTotals query itself ("super slamdank") - runs fine!

 

[The_Doc_Man]

Merely added tblPurchaseDetails, no joining, no fields added to Query1 - CRASHBOOMBANG!

I think this unequivocally points to tblPurchaseDetails as having an issue. Not that I know what it would be at the moment. Sometimes it takes more head-scratching than other times.

 

[mishash]

I think this unequivocally points to tblPurchaseDetails as having an issue. Not that I know what it would be at the moment. Sometimes it takes more head-scratching than other times.

I wonder what might be corrupted in 78 lines long simple table

 

[isladogs]

Someone has already suggested you post a cut down version of your db with any confidential data removed or modified.
All we need are the tables/queries related to this issue.
Without that, this thread could go on for ever without a solution.