System Resource Exceeded - Access 2016

Question

Hi,

I do work with an Access Database with 1.6GB, with several queries and rules (logic using data from table attributes). It has been working fine until I made a small modification on one of the attributes of a query, adding additional logic. Once I did that, I was not able to make design changes to queries that read data from the original/base one, and the message "System Resource Exceeded" is displayed. If I reverse the changes I made, Access is back to normal. Was wondering if I reached a limit from a complexity standpoint. If so, would appreciate ideas for workarounds.

From a hardware standpoint, I do work with an i7 from 2016, Windows 10, 64 Bits with 32GB RAM.

Thanks for all the help.

Answer 1

Can you show the SQL of the query?

What change did you make?

Answer 2

Hi,

It sounds like you are running into a little known problem with Access queries that can really cause performance issues and "System Resource Exceeded" problems. If you are using calculated or looked up information in your where clauses this is the problem. And, unfortunately these type of where clauses are shown as "good" examples all over the internet.

If you have a where clause like:

Forms("My Form").Form.MyControl     Or      Left("My Information",4)

is this your problem.

Here is what happens; to spite that a look up to a form is one static value that is the same all the records, Access reevaluates this value for every record that the query reads. This mean that if you are looking at 1/4 million records, Access if doing 1/4 form look ups to support this query. All that code accessing uses up your RAM and you end up with "System Resource Exceeded". You are out of usable RAM.

Instead, use query parameters to pass your form variables to the query or better yet build your query from a string putting in the actual values in your where clause and NOT the reference to the value.

Hope this helps.