"System Resource Exceeded" On One PC But Not Other PC's

[The Brown Growler]

Hi,

I am getting a "system resource exceeded" message when running a macro in a database on one PC but the same database and macro do not generate the message on other PC's.

The PC that is giving the error message is a Win7 64bit PC with i7-2600k CPU, 16gb RAM using Office 2003.

Two other PC's both running Win7 64bit with Office 2003 and less RAM and less powerful CPU run the macro OK without any error message.

Is there some trick to configuring the BIOS or Access for use on a sandybridge motherboard/CPU that I am not aware of? All PC's are clean fresh installs of Windows and Office 2003 with all microsoft updates applied.

If it is not some configuration issue that I am not aware about then I am assuming I have a hardware issue from either the motherboard or RAM or CPU on the PC that yields the error message?

Any feedback most appreciated.


Rgds
Growlos

 

[vbaInet]

Why don't you tell us what the macro is? Or better still, convert the macro to code and paste it here.

 

[The Brown Growler]

Why don't you tell us what the macro is? Or better still, convert the macro to code and paste it here.

The macro is just a collection of queries that run in sequence. It halts at a specific query and generates the error but the specific query it halts on is not a complex query.

 

[vbaInet]

First thing to try is to put DoEvents after each query execution. I'm not sure if that's in a macro too, but it should be.

How many queries are you running?

 

[The Brown Growler]

There are 25 queries that run in sequence in the macro, the first 16 are update queries and data definition queries then the remeaining 9 are more update queries which populate newly created columns with data.

As I said in my original post, it all works OK on two lower spec PC's but on the best spec PC it halts the macro when it starts to process the final 9 update queries that populate some blank columns. I have pasted below the code of the query that generates the error message

UPDATE T_BGRBv2_Import LEFT JOIN T_Standard_Times_Dated_UK ON T_BGRBv2_Import.TrackDistDateID = T_Standard_Times_Dated_UK.[TDD ID] SET T_BGRBv2_Import.SectRating = Int(100-([EqvScRF]*([CalcST]-[EqvST])))
WHERE (((T_BGRBv2_Import.Grade) Not Like "H*") AND ((T_BGRBv2_Import.Distance) Not Like "*H*") AND ((T_BGRBv2_Import.ST) Is Not Null) AND ((T_BGRBv2_Import.CalcST) Is Not Null));

The issue that is puzzling me is why is it occurring on one PC only and not the two other PC's, that is why I am thinking it could be a hardware isse? I understand that I might be able to improve the design of the database but it still does not explain why one PC fails but others run the query OK.


Rgds
Growlos

 

[vbaInet]

1. Have you tried the DoEvents I mentioned earlier? Placed before or after each execution.
2. What's your reason for saving calculated values?

Int(100-([EqvScRF]*([CalcST]-[EqvST])))

3. Try setting that field to a value like 1 (i.e. no calculations) and tell us if it fails.

 

[The Brown Growler]

I have tried the query setting the value to 1 and it runs OK.

The calculated values are saved out of personal choice.

The macro I am running is not a vba directly coded macro, ie, I set it up using the actions column and with actions like openquery rather than write the macro directly in vba code due to lack of coding skill. In this case, the DoEvents command is not available to the macro, I think it is a vba only option.

However, none of the above touches on my original question which was why would the macro not run on one PC when it does run OK on other PC's. That is the issue I wish to resolve as it could have a global impact on other databases on the impacted PC.


Rgds
Growlos

 

[vbaInet]

However, none of the above touches on my original question which was why would the macro not run on one PC when it does run OK on other PC's. That is the issue I wish to resolve as it could have a global impact on other databases on the impacted PC.


Rgds
Growlos

We can't just tell you what's going on without getting background. If you asked a question related to code syntax then we would have given you answer straight away.

I believe the problem lies with the calculation. Tell me the datatype of the fields in that calculation and I'll re-write it to one that should always work, and show me some values from the fields too.

 

[The Brown Growler]

...Tell me the datatype of the fields in that calculation and I'll re-write it to one that should always work, and show me some values from the fields too.

The data types of the fields in the calculation are as below:

[EqvScRF = Number as double
[CalcST] = Number as double
[EqvST] = Number as double

Sample data as below:

[EqvScRF], [CalcST], [EqvST]
69.21, 19.95, 20.25
204.34, 3.95, 4.15
200.00, 3.98, 4.24
127.99, 10.74, 10.95

I made a copy of the database and changed the number types to single, compacted the database and then tried to run the macro but it still failed.

Thanky you for the time you have spend looking at this for me, it is most appreciated.


Rgds
Growlos

 

[vbaInet]

Let's try these:

Int(100-(CDbl([EqvScRF])*CDbl([CalcST]-[EqvST])))

Int(100-(CDbl([EqvScRF])*(CDbl([CalcST])-CDbl([EqvST]))))

 

[The Brown Growler]

Re: "System Resource Exceeded" Due To BIOS Memory Configuration

I resolved this and the method might be useful to others who get this message.

It was nothing to do with the query design at all as I suspected, after all, why would it run on seveal PC's of lower spec and then not run on a high spec PC.

The issue was the memory configuration in the BIOS, I have 4 x 4gb XMP memory sticks but the BIOS configuration for the CPU profile was set to Auto. There were two other profile options for the CPU, one was Manual and the other XMP.

By changing the CPU profile in the BIOS to XMP the "system resource exceeded" message dissapeared and everything runs OK. On the two other PC's that the query ran OK on, none of them used XMP memory.

Whenever people have asked about the "system resource exceeded" message they do not often get a resolution, check out your memory profile in the BIOS and if you have XMP memory configure your BIOS to XMP mode.


Growlos

 

[boblarson]

Thanks for posting that.

 

[The Brown Growler]

Thanks for posting that.

Bob,

You're welcome, I have received good advice from the forum over the years and am happy to help others on the occasions that I can.

Rgds
Growlos