How can I fix a System Resources Exceeded Message?
Hello Experts!
I'm running a Access 2K app that runs perfectly on XP-Pro computers with 2GB of memory on an WIn10 Machine using MS Office 365.
When I attempt to run a query with 800,000 records I am getting an Memory Resources Exceeded message and system operation fails. I need to use task manager to get out of the loop. The same query works fine if I perform a reboot and run the query as first-thing.
I'm obviously running out of something, but what? Is it system memory, hard drive space, temp file? What can I do to get this query fixed?
Thanks in advance!
p.
I'm running a Access 2K app that runs perfectly on XP-Pro computers with 2GB of memory on an WIn10 Machine using MS Office 365.
When I attempt to run a query with 800,000 records I am getting an Memory Resources Exceeded message and system operation fails. I need to use task manager to get out of the loop. The same query works fine if I perform a reboot and run the query as first-thing.
I'm obviously running out of something, but what? Is it system memory, hard drive space, temp file? What can I do to get this query fixed?
Thanks in advance!
p.
John
Access 2000 is not supported on Windows 10, and that may be causing your issue. Also that version of Access will be 32 bit and so will be limited to the overall 32 bit operating system limits
<<Access 2000 is not supported on Windows 10>>
Not supported, but it will install and run without issue.
<<I'm obviously running out of something, but what? Is it system memory, hard drive space, temp file? What can I do to get this query fixed?>>
Actually one thing it is not is system memory<g>. Go figure. "out of resources" is given for any number of situations by Access; running out of disk space, stack space, DB has grown to 2GB, running out of table ID's, internal heap, etc. It can be very difficult to pin down.
With 800,000 records, first thing to try is increasing the number of available locks.
In the startup code, add:
DAO.DBEngine.SetOption dbMaxLocksPerFile, 500000
and see if that works.
Jim.
Not supported, but it will install and run without issue.
<<I'm obviously running out of something, but what? Is it system memory, hard drive space, temp file? What can I do to get this query fixed?>>
Actually one thing it is not is system memory<g>. Go figure. "out of resources" is given for any number of situations by Access; running out of disk space, stack space, DB has grown to 2GB, running out of table ID's, internal heap, etc. It can be very difficult to pin down.
With 800,000 records, first thing to try is increasing the number of available locks.
In the startup code, add:
DAO.DBEngine.SetOption dbMaxLocksPerFile, 500000
and see if that works.
Jim.
ASKER
@Jim Many years ago I had a different problem with this same code, and you suggested I add a similar line to the form that was causing the error. I took your advise and this solved the problem.
Now I'm wondering whether I need to remove the similar code from the form that was bombing out previously, and add it to my startup code. Will executing this code repeatedly cause a problem? It seemed to me at the time that I would have to use the code after creating each dao database instance???
If I add it to the startup code, can I open and close dao database and recordset objects on my other forms?
I'm testing the code now. Last time we used 2000000.
Thanks again
@John You may wish to be cautious about issuing warnings that Access 2K is not be supported on certain Microsoft Operating Systems including Win10. Other experts on this forum were responsible for me sticking with XP comptuers to this date, because we thought our MS Access 2K app would not work on Win7 computers. Truth be said, I am running an extremely sophisticated Access 2K app on WIn7 and Win10 successfully... with some minor problems..I have 360MB of code with 20 MS SQL and MS Access tables and millions of records running on Win7 and Win10 computers 8 hours a day by my entire staff... and for the most part its work fine. I would be so much better off today if I had ignored the warnings an expert on this forum who convincingly told me that I would need to also update my MS Access app to run on newer computers running a newer OS. I'm not saying I didn't have problems, but must of the problems were really pretty minor compared to the advantage of using newer computers.
I now encourage Access 2K users still on XP machines to move forward. With this said, I also want to thank you for all of the excellent help you have rendered to me throughout the years. BTW.. the only big problem I had so far in upgrading from Access 2K to Access 2016 and Office 365 is that Microsoft no longer supports one of the dll's which I was using to send EMAIL (I think it was CDONTS or something like that). This really doesn't work with Win7 and later, so I had to rewrite my email routine using a different dll.)
Now I'm wondering whether I need to remove the similar code from the form that was bombing out previously, and add it to my startup code. Will executing this code repeatedly cause a problem? It seemed to me at the time that I would have to use the code after creating each dao database instance???
If I add it to the startup code, can I open and close dao database and recordset objects on my other forms?
I'm testing the code now. Last time we used 2000000.
Thanks again
@John You may wish to be cautious about issuing warnings that Access 2K is not be supported on certain Microsoft Operating Systems including Win10. Other experts on this forum were responsible for me sticking with XP comptuers to this date, because we thought our MS Access 2K app would not work on Win7 computers. Truth be said, I am running an extremely sophisticated Access 2K app on WIn7 and Win10 successfully... with some minor problems..I have 360MB of code with 20 MS SQL and MS Access tables and millions of records running on Win7 and Win10 computers 8 hours a day by my entire staff... and for the most part its work fine. I would be so much better off today if I had ignored the warnings an expert on this forum who convincingly told me that I would need to also update my MS Access app to run on newer computers running a newer OS. I'm not saying I didn't have problems, but must of the problems were really pretty minor compared to the advantage of using newer computers.
I now encourage Access 2K users still on XP machines to move forward. With this said, I also want to thank you for all of the excellent help you have rendered to me throughout the years. BTW.. the only big problem I had so far in upgrading from Access 2K to Access 2016 and Office 365 is that Microsoft no longer supports one of the dll's which I was using to send EMAIL (I think it was CDONTS or something like that). This really doesn't work with Win7 and later, so I had to rewrite my email routine using a different dll.)
<<Will executing this code repeatedly cause a problem?>>
No and there is no need to set it repeatedly.
<<It seemed to me at the time that I would have to use the code after creating each dao database instance???>>
Your setting this at the engine level, so no unless you are possibly using the privdbengine object.
Jim.
No and there is no need to set it repeatedly.
<<It seemed to me at the time that I would have to use the code after creating each dao database instance???>>
Your setting this at the engine level, so no unless you are possibly using the privdbengine object.
Jim.
ASKER
@Jim Okay, I'll compile now and give it a try. It sounds like I could set the looks to 2000000 in the start up form and leave the code in the other forms alone... if I understand correctly. Thanks
Yes, that is correct. Although if you set it to a higher value, then hit one of your forms that has a lower number, the limit would be lowered.
The number of locks allowed would be based on the last statement executed.
Jim.
The number of locks allowed would be based on the last statement executed.
Jim.
There is a big difference between not supported and will not work!
Not supported means that the support team (in this case inside Microsoft) have for whatever reason decided to not test A2K on Win10 and therefore not fix any bugs they find
Not supported means that the support team (in this case inside Microsoft) have for whatever reason decided to not test A2K on Win10 and therefore not fix any bugs they find
If you simply click and run the query then probably Jims recommendation is what you need...but if its used by code probably you have a recordset that wasn't closed properly from the previous time and maybe you have to check the code...
ASKER
@John. Keep in mind when I copy the mdb to an access 2K machine on XP the same programmatic even works without issue.
I click a button..it works in xp A2K and not office365 win 10
.. well it works on the win 10 if I reboot and then try the button before anything else.
I click a button..it works in xp A2K and not office365 win 10
.. well it works on the win 10 if I reboot and then try the button before anything else.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the info.
<<I changed the value of dbMaxLocksPerFile from 2000000 to 200000>>
2 million is certainly too much. 500,000 is something that I would do as a test, not as a normal production setting. 100,000 - 200,000 is probably where you want to be for a large DB.
<<'DBEngine.SetOption dbMaxBufferSize, 65536>>
This is the setting on the JET engine cache. In years past, 65535 was the max you could go. The default of 0 gives you a calculation based on available memory. But that calc was from days LONG gone by when systems had total memory measure in megabytes.
It's unknown at this time if that calc is still capped or not, or if it can be larger than 65535.
Jim.
<<I changed the value of dbMaxLocksPerFile from 2000000 to 200000>>
2 million is certainly too much. 500,000 is something that I would do as a test, not as a normal production setting. 100,000 - 200,000 is probably where you want to be for a large DB.
<<'DBEngine.SetOption dbMaxBufferSize, 65536>>
This is the setting on the JET engine cache. In years past, 65535 was the max you could go. The default of 0 gives you a calculation based on available memory. But that calc was from days LONG gone by when systems had total memory measure in megabytes.
It's unknown at this time if that calc is still capped or not, or if it can be larger than 65535.
Jim.
I checked with Microsoft on this and their recommendation on MaxBuffers is to leave it set to the default unless you have a very specific reason for changing it.
What it actually controls is the point at which JET starts doing garbage collection on its cache, not the size of the cache per say. The cache can actually grow larger, but when it passes this point, a thread is kicked off to clean-up.
Setting maxbuffers to a high value then can trigger the error because you may run out of cache before the clean-up process is complete.
MaxLocksPerFile is whatever works, but 200,000 is probably the max you should take it to. Again, this is a threshold value that triggers when JET does an implicit commit on what it's done so far. The default of 9,500 is probably too low for most DB's today. However if you have a large operation with a value set too high, then you could bring a server to a standstill or get "out of resources" (hitting the lock limit or the OS refusing to grant more locks are both a problem).
And that was the original intent for this setting. Under Novell Netware, any process consuming more than 9,500 locks could bring the server to a standstill, so they capped the number of locks the engine would take.
Jim.
What it actually controls is the point at which JET starts doing garbage collection on its cache, not the size of the cache per say. The cache can actually grow larger, but when it passes this point, a thread is kicked off to clean-up.
Setting maxbuffers to a high value then can trigger the error because you may run out of cache before the clean-up process is complete.
MaxLocksPerFile is whatever works, but 200,000 is probably the max you should take it to. Again, this is a threshold value that triggers when JET does an implicit commit on what it's done so far. The default of 9,500 is probably too low for most DB's today. However if you have a large operation with a value set too high, then you could bring a server to a standstill or get "out of resources" (hitting the lock limit or the OS refusing to grant more locks are both a problem).
And that was the original intent for this setting. Under Novell Netware, any process consuming more than 9,500 locks could bring the server to a standstill, so they capped the number of locks the engine would take.
Jim.