@cittakaro Sure. None of my DAX is too crazy and is probably pretty basic to someone who is an expert. There might be better ways to do most of what I did but it works and loads quickly so I got what I needed.

This is probably one of the more complicated measures I had in my report. I used to have everything here that you see as variables written into what is now the return statement. Sorry if it seems a bit like a jumbled mess. It's essentially just a series of nested if statements. After I started using variables, the performance of the report loading visuals was almost instantaneous rather than taking upwards of 10-15 seconds just to load a matrix/table visualization, if it loaded those visuals at all.

EarnedTitleLevel# = 
VAR SurveylessthanTitle = [SurveyScoreGoal]<=values(TMD[Title Sort])
var SClessthanTitle = [StatusCountGoal]<=values(TMD[Title Sort])
var TTlessthanTitle = [TurnTimeGoal]<=values(TMD[Title Sort])
var AnswerlessthanTitle = [AnswerRateGoal]<=values(TMD[Title Sort])
VAR SurveylessthanSC = [SurveyScoreGoal]<=[StatusCountGoal]
var SClessthanSurvey = [StatusCountGoal]<=[SurveyScoreGoal]
VAR SurveylessthanTT = [SurveyScoreGoal]<=[TurnTimeGoal]
var TTlessthanSurvey = [TurnTimeGoal]<=[SurveyScoreGoal]
var SurveylessthanAnswer = [SurveyScoreGoal]<=[AnswerRateGoal]
var AnswerlessthanSurvey = [AnswerRateGoal]<=[SurveyScoreGoal]
var TTGoal = [TurnTimeGoal]
var SurveyScore = [SurveyScoreGoal]
var MinSurveys = [SurveyReceivedMin]
var MinCR = [CRCountMin]
var AnswerGoal = [AnswerRateGoal]
var statusgoal = [StatusCountGoal]
var CurrentTitle = values(TMD[Title Sort])
return
if(values(TMD[Specialty])="Mainstream PS" || values(TMD[Specialty])="NY PS",
    if(isblank(TTGoal) || isblank(SurveyScore), CurrentTitle,
    if(MinCR = -1 || MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanTT,SurveyScore,
        if(TTlessthanTitle && TTlessthanSurvey,TTGoal,CurrentTitle)),
    if(SurveylessthanTT,SurveyScore,TTGoal))),
if(values(TMD[Specialty])="Hunt PS",
    if(isblank(statusgoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1 || [RONAgoal]=-1,
        if(SurveylessthanTitle && SurveylessthanSC,SurveyScore,
        if(SClessthanTitle && SClessthanSurvey,statusgoal,CurrentTitle)),
    if(SurveylessthanSC,SurveyScore,statusgoal))),
if(values(TMD[Specialty])="CCS" || values(TMD[Specialty])="Escalation CCS",
    if(isblank(statusgoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanSC,SurveyScore,
        if(SClessthanTitle && SClessthanSurvey,statusgoal,CurrentTitle)),
    if(SurveylessthanSC,SurveyScore,statusgoal))),
if(isblank(AnswerGoal) || isblank(SurveyScore), CurrentTitle,
    if(MinSurveys = -1,
        if(SurveylessthanTitle && SurveylessthanAnswer,SurveyScore,
        if(AnswerlessthanTitle && AnswerlessthanSurvey,AnswerGoal,CurrentTitle)),
    if(SurveylessthanAnswer,SurveyScore,AnswerGoal))))))

A slightly simpler example is below here. Just for a bit of context that would make the code make a bit of sense without seeing the report, on each page of this report I've added in drop down slicers that the end user can use as selectors to adjust their goals for different tiers. Each of those slicers is reading off of a simple table that just has values that range from 0-100.

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.

• 2

When the queries get really complicated,you get that message. Simplify the query.you may need to make many simple queries as substitute.

• 3

  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.

• 4

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.

• 5

  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?

• 7

Try running task manager on both computers and compare the resources or lack of.

• 8

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.

• 9

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"

• 10

  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.?

• 11

  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.

• 12

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.

• 13

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 : )

• 14

  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!

• 15

The "System Resources Exceeded" message is a real beast to try to chase down. We have seen several issues regarding it.

Hi all, I have the following problem which i cannot find solution in order to solve it. My database is about 150MB with enough complexity into the code I can said. My computer is Lenovo i5 - 8GB RAM and my database giving the error "System Resource Exceeded" only on the specific processes of...

www.access-programmers.co.uk

MS Access Disk Buffer Size

Researching a question for another thread, I came across an interesting article. All this time, I thought that an Access disk buffer was 2048 bytes. But that appears to be wrong. https://msdn.microsoft.com/en-us/library/windows/desktop/dn170506(v=vs.85).aspx Read the description for...

www.access-programmers.co.uk

Max Locks Per File

Some of you will be familiar with this error: File sharing lock count exceeded. Increase MaxLocksPerFile registry entry. There are 2 ways of increasing the MaxLocksPerFile: 1. Permanently by changing the registry 2. Temporarily using VBA code Both methods are explained in this...

www.access-programmers.co.uk

I have two different situation where I have not been able to solve the Error 3035 "System Resource" exceeded problem. I've looked for memory leaks, set the MaxRecordLocks to 1 million as suggested elsewhere, and tried various rewrites, all to no avail. The latest is a routine that moves data...

www.access-programmers.co.uk

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...)

• 16

  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.

• 17

  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.

• 18

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.

• 19

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.

• 20

Had the same issues. Way I fixed it was by using the above solutions, but because I needed it to open a specific database, I did the following:

Create a notepad document and put in the following:

@echo off C:\Windows\System32\cmd.exe /c start "NAME OF PROGRAM HERE" /Affinity 3 "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE" "C:\FilePathTo\Database.mdb"

I then saved this as FIX.CMD

Then I changed the file path of the shortcut to be pointing to FIX.CMD.

Now when I click on the Icon, It quickly flashes the CMD box, but will run the database and when it opens MSaccess, it will have affinity of 3 (CPU 0 and 1 only)