"Access has stopped working" due to vba routine taking too long (1 Viewer)

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
Hello again,
I am making a database for a institutional reporting and error-finding tool in a Sharepoint table. I have a complicated vba query that runs on several thousand records (currently, and will grow with time) and a few dozen fields per record. I have ran this before successfully, but with a few modifications it now takes twice as long. It strongly appears that Windows is stopping the Access execution before it finishes, killing the process, after 10 minutes run time. I have confirmed this by adding text file logging during execution, Windows gives me the error message "Access has stopped working" and leaves me no option to continue it. What can I do about this? Access 2016. Windows 10 Enterprise.
 

Isaac

Lifelong Learner
Local time
Today, 02:29
Joined
Mar 14, 2017
Messages
8,738
Might be helpful to see the code.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 28, 2001
Messages
27,001
Technically, Access hasn't stopped working when you see that message. What HAS happened is that it has entered into a computational state where it stops making system calls. If you were to run Windows Task Manager and set it to show you "Processes" you would see that your process is accumulating CPU time - but no I/O.

EDIT: Sorry for the imcomplete thought - phone-call with my daughter-in-law.

There are two solutions.

1. Wait.

2. Break the query up into pieces-parts somehow (perhaps with a WHERE clause that does one sub-group at a time). If Access every now and then wakes up to do the next query in sequence, you would see fewer of the "Stopped Working" messages.
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
Too big to post inline, and has many dependents too. Attached.

EDIT: The attached text is now obsolete, I have reworked the function. The code has been changed quite a bit, the SQL recordset has been removed in favor of an append query which is now ran before this obsolete subroutine (which is also renamed). If I still am not getting this to work I will post an updated text file.
 

Attachments

  • 210405a VBA MakeIR_tbl().txt
    20.9 KB · Views: 515
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:29
Joined
May 21, 2018
Messages
8,463
Do you know where it is hanging? I would have this broken up into several sub procedures/functions. You are creating multiple recordsets, then filling the form, ... That is a lot to bite off in one big procedure IMO. I would probably have functions like

GettblIR() as dao.recordset
GettblPNA() as dao.recordset
And for sure
GetXl_qry() as dao.recordset
FillEditableForm(argument,argument2...)

Not that this is more efficient, but would sure a lot easier to code, debug, and work on optimizing.

That XL_query is pretty brutal. Does it return query results in a reasonable time? There are a lot of functions done in the query. I have not worked with SP for a long time, but it was notoriously slow in the old days. Would it make sense to simplify the query and do the processing on the fields in the procedure to fill the editable table? In other words do your if checks, trimming, nz, etc when filling the table. Obviously if this was something other than SP then doing it is SQL would probably be the efficient way.
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
Do you know where it is hanging? I would have this broken up into several sub procedures/functions. You are creating multiple recordsets, then filling the form, ... That is a lot to bite off in one big procedure IMO. I would probably have functions like

GettblIR() as dao.recordset
GettblPNA() as dao.recordset
And for sure
GetXl_qry() as dao.recordset
FillEditableForm(argument,argument2...)

Not that this is more efficient, but would sure a lot easier to code, debug, and work on optimizing.

That XL_query is pretty brutal. Does it return query results in a reasonable time? There are a lot of functions done in the query. I have not worked with SP for a long time, but it was notoriously slow in the old days. Would it make sense to simplify the query and do the processing on the fields in the procedure to fill the editable table? In other words do your if checks, trimming, nz, etc when filling the table. Obviously if this was something other than SP then doing it is SQL would probably be the efficient way.
Thank you for your responses.

Well first, I'm not sure what "SP" is, stored procedure? Secondly, I believe that breaking it up into more functions is not going to increase the speed of the result, if anything the overhead of the procedure calls should slow it down as far as I know. Third, I don't know how to use a function to open a recordset and then use fields from that recordset in the calling function - without using a complicated string return that will make it take much longer to process, and complicate the debugging. I have tried to return a recordset object before from a function and failed. I tried using vba code search results to do so without success. But I think that's going off on a tangent as it is not addressing the main problem.

The main reason I need to do this this way is I need to populate a field with results from one of two other fields, and only after comparing the text with an "alias" table and a "not a name" table - of things entered by the staff that should not have been entered. So I need to create a table that I can work with (edit). A secondary objective, also important, is to show the data for examination for proofreading (error checking). Yes, much of the data was entered incorrectly, but there is nothing I can do about that, I just have to deal with it. This function is immediately followed by another function that looks at some text fields and fills in another (unmentioned) text field with the appropriate text.


This is all made more complicated by the fact that some of the fields that are passed directly to access from the Sharepoint table are not simple fields, "complex data", "type2/object" data type, and other fields are text of >255 characters. This means I have to first query with Excel, then link my database to that Excel table, because Excel converts these "complex data" to dates for me, but will not capture all the long text. So I have to add it with vba, from two different sources. I tried a SQL "inner join," and "left join," but using them made my database unstable, and I had to restore from previous saves many times trying to work with that. Since then, recently, I have rebuilt it from scratch carefully to restore stability. It seems that working with tables that include the "complex data" type in one table, and simple fields in the corresponding field of the other table, with "join," causes the db to become unstable, and occasionally it corrupts the db making it unusable.

Now I am thinking maybe I need to try using a "maketable" query (not in vba), as since my code has matured a bit and I am now doing things differently it may be feasible as an alternative. The problem with this a few weeks ago was I was trying to limit my query to a date range to speed things up a lot, but that did not work out (first it was not feasible because I was trying to do it with that "complex data" field directly from Sharepoint, then later I learned more info from my internal customer that made me abandon this). Would an internal Access maketable query run significantly faster than the vba SQL? And yes, I could (and did previously) do a lot of those calculations after the main SQL query, but I don't know if I would be wasting my time trying it as I have no idea of whether or not it would speed it up.


I would appreciate any advice here. I consider the responses in this forum to be of the best quality, in comparison to a few other Access forums. The guys here seem to know more and hit the mark with the responses better. Thank you.
 

Isaac

Lifelong Learner
Local time
Today, 02:29
Joined
Mar 14, 2017
Messages
8,738
Thanks for posting the code. One thought I had was that, if there is any chance you are opening DAO recordsets in a loop, I know I've experienced that it's very important to close them and set them to nothing when finished. Numerous DAO recordsets running in some loop scenario (etc), can actually cause things like memory problems or Access to be generally overloaded. This would apply to any aspect of your MAIN code that does this, OR to any VBA functions that get used in a query, where the Function opens a DAO recordset, which means a recordset is being opened (and hopefully closed and set to nothing) for every record in the query, or more.

That's the only thought I had so far. Good luck.
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
Technically, Access hasn't stopped working when you see that message. What HAS happened is that it has entered into a computational state where it stops making system calls. If you were to run Windows Task Manager and set it to show you "Processes" you would see that your process is accumulating CPU time - but no I/O.

EDIT: Sorry for the imcomplete thought - phone-call with my daughter-in-law.
Thank you for your response, I only just now saw your edit.


Problem: this is not for me, it is for another coworker (my customer), and I don't want them to discount the tool and just tell me "it doesn't work."

2. Break the query up into pieces-parts somehow (perhaps with a WHERE clause that does one sub-group at a time). If Access every now and then wakes up to do the next query in sequence, you would see fewer of the "Stopped Working" messages.

Well how would I do that? If I break it up, the application still has to wait for the vba to execute completely before allowing the user to look at the reports. I don't see that it matters "where" in vba code it is, if it takes >10 minutes in vba code it's still going to time out. And I don't want to create something that is a PITA to my customer, asking for input and then wait several times before it allows access to the reports. Pardon me if I am missing something here.
 

isladogs

MVP / VIP
Local time
Today, 09:29
Joined
Jan 14, 2017
Messages
18,186
Does it always reach the same place in the code when it hangs?
See you add message boxes after each section of code to help identify the location

Its possible you are running out of available connections. You can monitor this using my utility Available Connections - Mendip Data Systems
Another possibility is that you have hit the limit of MaxLocksPerFile or (less likely) MaxBufferSize. See this thread for possible solutions: MS Access Disk Buffer Size | Access World Forums (access-programmers.co.uk)
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
"Oops! We ran into some problems. Please try again later. More error details may be in the browser console."
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
Does it always reach the same place in the code when it hangs?
See you add message boxes after each section of code to help identify the location

Its possible you are running out of available connections. You can monitor this using my utility Available Connections - Mendip Data Systems
Another possibility is that you have hit the limit of MaxLocksPerFile or (less likely) MaxBufferSize. See this thread for possible solutions: MS Access Disk Buffer Size | Access World Forums (access-programmers.co.uk)
It doesn't hang. If Windows did not shut it down, it would complete. See the attached log file. It would be complete when reaching ID = 555.
 

Attachments

  • Log.txt
    114.8 KB · Views: 486
Last edited:

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
I just confirmed that Access actually does stop working, it won't help to ignore the error message, the logging stops, and CPU usage drops to zero. The application grey's out, the db has saved itself, and the attached error message comes up. The making of the table stops. There is no choice but to kill the (now idle) process. Also: I am working on a locked-down computer, I cannot alter the system registry, I do not have admin privileges on my work computer.
 

Attachments

  • 210405 Access has stopped working.png
    210405 Access has stopped working.png
    12.4 KB · Views: 488

isladogs

MVP / VIP
Local time
Today, 09:29
Joined
Jan 14, 2017
Messages
18,186
OK...when Access 'has stopped working'..
My comments should still be relevant
 

HalloweenWeed

Member
Local time
Today, 05:29
Joined
Apr 8, 2020
Messages
213
OK...when Access 'has stopped working'..
My comments should still be relevant
Yes, re-read my last post, I edited it, it's moot, if that's the problem I can't do anything about it anyway. Thanks for the thought.
I may just have to tell them I can't do it, and abort the project.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:29
Joined
Feb 28, 2001
Messages
27,001
When you break it up, you are correct that the individual parts would take as long - but the system would be less likely to declare an idle process due to the (probably) idle network connection between your system and the file server. Also, if you break it up into parts - and MEASURE how long each part takes, you might be able to figure out where your "real" bottleneck is.
 

isladogs

MVP / VIP
Local time
Today, 09:29
Joined
Jan 14, 2017
Messages
18,186
Yes, re-read my last post, I edited it, it's moot, if that's the problem I can't do anything about it anyway. Thanks for the thought.
I may just have to tell them I can't do it, and abort the project.
Yes I did read that reply before I posted though I think you may have edited it again since then.
I've suggested 3 things that may be responsible for your issue and that may well allow you to prevent it.
MaxLocksPerFile can also be increased for the current session using VBA

However there is a limit to the number of times I will suggest you follow up those links which you seem to have dismissed without investigation.
 
Last edited:

Isaac

Lifelong Learner
Local time
Today, 02:29
Joined
Mar 14, 2017
Messages
8,738
I'd seriously look into the numerous dao recordsets too - it seems like a distinct possibility, given all that you have going on in that code - plus the code that we can't see. But it's up to you.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:29
Joined
May 7, 2009
Messages
19,174
you should think that maybe somebody is having the db open also and
using some of your table?
issueing update/insert query, access might try locking the whole table before
applying your update/insert.
it will retry if it cannot lock it (in the case that somebody is also in the process of
updating the same table).
so it is not enought to just run the query (run and forget).

if you use same db (and opened the table using forms). you can
implement a Locking routine that will Lock a certain table first
before he can execute the query.

this will prevent your code clashing with another update (from another user).

EDIT:
just seen your query, it has never-ending IIF().
 
Last edited:

FrankRuperto

Member
Local time
Today, 05:29
Joined
Mar 6, 2021
Messages
182
Hello again,
I am making a database for a institutional reporting and error-finding tool in a Sharepoint table. I have a complicated vba query that runs on several thousand records (currently, and will grow with time) and a few dozen fields per record. I have ran this before successfully, but with a few modifications it now takes twice as long. It strongly appears that Windows is stopping the Access execution before it finishes, killing the process, after 10 minutes run time. I have confirmed this by adding text file logging during execution, Windows gives me the error message "Access has stopped working" and leaves me no option to continue it. What can I do about this? Access 2016. Windows 10 Enterprise.
Agree with arnelgp's remark about never_ending IIF.
What does the Windows Event Log say about why Access stopped working?
SP means SharePoint, not Stored Procedure. The native Access ACE db engine does not support stored procedures.
Enable the hourglass for long running processes.
Me thinks you would be better off importing all the raw data into Access temp tables and doing all the crunching in exclusive mode.
 
Last edited:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:29
Joined
Sep 12, 2006
Messages
15,614
@HalloweenWeed

That must be some query. Normally access reports the query as too complex! I am sure you will get it sorted with a few staging queries, and a temporary table along the way.
 

Users who are viewing this thread

Top Bottom