System Resource Exceeded (1 Viewer)

alexfwalker81

Member
Local time
Yesterday, 21:20
Joined
Feb 26, 2016
Messages
82
Bit of a strange one... I've been building a few databases this week, none of which are overly complex. They all happily run on my i5 8GB laptop, but having moved them to run on an i7 16GB, I now get a 'System Resource Exceeded' message each time they run.
 

Ranman256

Registered User.
Local time
Today, 00:20
Joined
Apr 9, 2015
Messages
3,640
When the queries get really complicated,you get that message.
Simplify the query.you may need to make many simple queries as substitute.
 

alexfwalker81

Member
Local time
Yesterday, 21:20
Joined
Feb 26, 2016
Messages
82
When the queries get really complicated,you get that message.
Simplify the query.you may need to make many simple queries as substitute.
They can't be any simpler I'm afraid - they're quite simple anyway. What I can't grasp is why a machine which is demonstrably more powerful is struggling with these DBs, compared to my laptop.
 

OldeCow69

New member
Local time
Today, 16:20
Joined
Sep 16, 2020
Messages
6
Unfortunately I don't have an explanation, but ran into a similar problem this week.

This simple SQL, which is being generated via VBA, fails with a System Resource Exceeded error message on my work machine (i7, 16GB, Win 10, Access 365 64-bit):
Code:
SELECT SIGHTING_ID, LOCN_PRECISION, VETTING_STAGE 
FROM TBL_SIGHTING 
WHERE ((SITE_ID = 9860) AND SITE_TITLE = 'INCIDENTAL');

This same code worked fine on this work machine last month, when it was still running Access 2016 32-bit.

This same code runs fine today on my personal laptop (i5, 4GB, Win 10, Access 365 32-bit).

Surprisingly I was able to work around this problem on the Access 365 64-bit work machine by altering the SQL to this:
Code:
SELECT SIGHTING_ID, LOCN_PRECISION, VETTING_STAGE 
FROM TBL_SIGHTING 
WHERE ((SITE_ID = 9860) AND SITE_TITLE Like 'INCIDENTAL');

Which produces the same result, but doesn't throw the System Resource Exceeded error.
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:20
Joined
Sep 21, 2011
Messages
7,145
They can't be any simpler I'm afraid - they're quite simple anyway. What I can't grasp is why a machine which is demonstrably more powerful is struggling with these DBs, compared to my laptop.
What else is it doing though?, different to the laptop?
 

AccessBlaster

Custom title here:
Local time
Yesterday, 21:20
Joined
May 22, 2010
Messages
2,858
Try running task manager on both computers and compare the resources or lack of.
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:20
Joined
Mar 14, 2017
Messages
2,738
make sure you don't have any LOOPS that open dao recordsets repeatedly without explicitly closing them / setting to nothing. known problem. not sure if pertains to you, just mentioning.
 

alexfwalker81

Member
Local time
Yesterday, 21:20
Joined
Feb 26, 2016
Messages
82
Here's the solution; affinities have to be set so that it runs on just one processor. This needs to be added to the command line when you boot access.

C:\Windows\System32\cmd.exe /c start "Microsoft Access" /Affinity 1 "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"
 

Gasman

Enthusiastic Amateur
Local time
Today, 05:20
Joined
Sep 21, 2011
Messages
7,145
Here's the solution; affinities have to be set so that it runs on just one processor. This needs to be added to the command line when you boot access.

C:\Windows\System32\cmd.exe /c start "Microsoft Access" /Affinity 1 "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"
How did you discover that please.?
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:20
Joined
Mar 14, 2017
Messages
2,738
Here's the solution; affinities have to be set so that it runs on just one processor. This needs to be added to the command line when you boot access.

C:\Windows\System32\cmd.exe /c start "Microsoft Access" /Affinity 1 "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"
And that solved your original problem w/no other changes?
If so - that's awesome, glad you got it working & thanks for posting the solution.
 

alexfwalker81

Member
Local time
Yesterday, 21:20
Joined
Feb 26, 2016
Messages
82
I was moaning about it to one of our IT guys, who said he vaguely remembered something about affinity. You can set affinity through Task Manager, but it 'forgets' when you next load Access, so if you use that command either in a desktop shortcut, or a .bat file then it works without a problem.
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:20
Joined
Mar 14, 2017
Messages
2,738
Some problems have more than one solution. For example, it's still possible that something about the way the data or code is stored/running is the ultimate "cause", even though the affinity solution is preventing it from showing bad symptoms. Why do I point that out? Well ... I think of it this way. If starting up access with that affinity command is uncommon (i.e., most Access developers don't need to do that), and you're running that on a regular, modern machine commonly used including Access, then that tells me that common denominator of avoiding the problem isn't the startup method, it's something else. Just a thought in case you decide to dig deeper.
Don't mind me - I'm not being critical, just theorizing. I like to do that sometimes : )
 

alexfwalker81

Member
Local time
Yesterday, 21:20
Joined
Feb 26, 2016
Messages
82
Some problems have more than one solution. For example, it's still possible that something about the way the data or code is stored/running is the ultimate "cause", even though the affinity solution is preventing it from showing bad symptoms. Why do I point that out? Well ... I think of it this way. If starting up access with that affinity command is uncommon (i.e., most Access developers don't need to do that), and you're running that on a regular, modern machine commonly used including Access, then that tells me that common denominator of avoiding the problem isn't the startup method, it's something else. Just a thought in case you decide to dig deeper.
Don't mind me - I'm not being critical, just theorizing. I like to do that sometimes : )
Yes, you're probably right!
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 23:20
Joined
Feb 28, 2001
Messages
18,351
The "System Resources Exceeded" message is a real beast to try to chase down. We have seen several issues regarding it.


Have to admit I haven't seen the "set affinity" trick before this, and to be honest, it shouldn't make a "resources" difference. The CPU "resource" is not in the same category as an ordinary Windows resource. The word "resources" in Windows context is fairly specific, referring to a data structure somewhere in the bowels of Windows. (Yes, I used "bowels" intentionally to consider what else is inside Windows...)
 

OldeCow69

New member
Local time
Today, 16:20
Joined
Sep 16, 2020
Messages
6
Some problems have more than one solution. For example, it's still possible that something about the way the data or code is stored/running is the ultimate "cause"
Happily I managed to track down the cause in my case.

Seems another table not even accessed by this query was corrupted in some obscure manner (no db corrupt error messages, Corrupt and Repair function has been run several times without identifying/resolving it).

Importing all tables into a fresh database generated a Name AutoCorrect Save Failures table for one table though.

Rebuilt this table and the query now runs without throwing the System Resource Exceeded error.
 

Isaac

Lifelong Learner
Local time
Yesterday, 21:20
Joined
Mar 14, 2017
Messages
2,738
Happily I managed to track down the cause in my case.

Seems another table not even accessed by this query was corrupted in some obscure manner (no db corrupt error messages, Corrupt and Repair function has been run several times without identifying/resolving it).

Importing all tables into a fresh database generated a Name AutoCorrect Save Failures table for one table though.

Rebuilt this table and the query now runs without throwing the System Resource Exceeded error.
Wow awesome - glad to hear it!!
... More Reading Material - on name auto correct
http://www.allenbrowne.com/bug-03.html
Not really sure if this is "just as true today" as it used to be, since I just got in the habit of always turning it off years ago and never looked back.
FYI.
 

OldeCow69

New member
Local time
Today, 16:20
Joined
Sep 16, 2020
Messages
6
Seems like it's still solid advice, the MS docs even recommend turning it off in applications where the schema doesn't change much.

Good thing I wasn't following it though, or I would have struggled to find this particular problem.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Yesterday, 23:20
Joined
Feb 28, 2001
Messages
18,351
The problem with table corruption is that you never know exactly what effect it will have. Therefore, I cannot claim total surprise that this case triggered the "Resources" errors. I could guess at the mechanism but it would an idle guess. Glad you found it and fixed it. Thank you VERY much for posting back with the solution, since that is one way you feed back to the community.
 

Users who are viewing this thread

Top Bottom