System Resources Exceeded - MS Access 2016 Error

[Sonu]

Dear All,




Need some quick inputs. I am using MS Access 2016 on a 64 bit Windows machine. I am trying to run a report, that eventually runs a whole bunch of queries. In between the queries execution, an error pops up with message "System Resources Exceeded".


To fix this, I have gone through many searches and have tried the below so far:


1) Task manager -> processes -> change the Affinity setting of MS Access.exe to just one CPU. ==> Once I did this, I could see that this time more number of queries executed than earlier but again failed with the same error.


2) I could see some patches available for this issue, but none for Access 2016, hence couldn't do anything here.


Also, I noticed that the required registry keys are not at the desired locations ( I am not sure, if that's the cause of the issue; any of the below paths do not exist in my machine):


HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\ Access Connectivity Engine\Engines


HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\ Office\14.0\Access Connectivity Engine\Engines


Any help in this regard will be appreciated!


Thank you.

[ranman256]

You may have to restructure your queries. Simplify.

[Minty]

How many queries is a "Bunch" ?

If the report is based on bits of all of them and they are nested then effectively they will all trying to be opened one after the other in the background.
How many records would be accessed by the process?
How much memory do you have on the machine in question where the error is happening?

You may need to use some temporary tables if the queries are overly complicated, and you are running out of memory.

[isladogs]

Access 2016 is version 16.0 so you wouldn't expect to find entries for 14.0 which is A2010.
The wow6432Node entry is for 32-bit Office on 64-bit Windows.
If you have both with the same bitness, the other path would be appropriate (but 16.0)…
BUT if you have Office365 the path will be different again with a ClickToRun component

Confused yet...?

Anyway there are various ways of dealing with that error including
1. Checking for available connections and ensuring objects including recordsets are closed/destroyed after use
See http://www.mendipdatasystems.co.uk/a...ons/4594418530
2. Increasing the MaxLocksPerFile setting in the registry
3. Increasing the MaxBufferSize
A forum or Google search should throw up many hits for items 2 & 3