System Resource Exceeded

[gstylianou]

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 the database

Looking at the issue enough to find a way to resolve it, I found the following code line which in essence saids that can solve the problem, but unfortunately even after applying this line code to the point where the problem occurs, the problem still persist.

DAO.DBEngine.SetOption dbMaxLocksPerFile, 1000000

Is there any idea from ​you who are more experienced than me? Keep in mind that i have tried i7 - 16GB Ram and the problem still persist.

 

[jdraw]

See this thread for possible actions/ideas.

 

[theDBguy]

Hi. Did you say the error only happens when you do a particular process? If so, can you describe this process? Thanks.

 

[gstylianou]

See this thread for possible actions/ideas.

Hi and thanks,

They said must change the HKEY_LOCAL_MACHINE\
SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE\MaxBufferSize to 50000 but i'm using Ms Access 2016 and i cannot see where is the Access Connectivity Engine\Engines\ACE\MaxBufferSize . Any idea?

Thanks again

 

[gemma-the-husky]

Are you running a very complex query? you may be getting a genuine error, in which case you might need to find a way to change the process, perhaps.

 

[The_Doc_Man]

The problem with the "System Resources Exceeded" error is that you run into real headaches trying to find the culprit because it is buried in the bowels of Windows, not Access. A system "resource" includes anything that might involve a graphic rendering. For instance, each desktop icon consumes ONE resource. Each file or folder icon (not limited to the desktop) uses a resource. Individual .EXE files might consume several resources.

Not all resources involve icons. I have seen evidence that file handles can also do it. And the few times we have seen here on this forum, it was several different causes. One of the ones that stand out is that if you have a query with a domain aggregate inside the query (as opposed to SQL aggregate), that would chew up a lot of resources and wouldn't let them go until the query is complete. Do you have a query with DLookup or DSum or DCount in it?

 

[gstylianou]

The problem with the "System Resources Exceeded" error is that you run into real headaches trying to find the culprit because it is buried in the bowels of Windows, not Access. A system "resource" includes anything that might involve a graphic rendering. For instance, each desktop icon consumes ONE resource. Each file or folder icon (not limited to the desktop) uses a resource. Individual .EXE files might consume several resources.

Not all resources involve icons. I have seen evidence that file handles can also do it. And the few times we have seen here on this forum, it was several different causes. One of the ones that stand out is that if you have a query with a domain aggregate inside the query (as opposed to SQL aggregate), that would chew up a lot of resources and wouldn't let them go until the query is complete. Do you have a query with DLookup or DSum or DCount in it?

Morning The_DOCMan

Yes, i have some queries DSum as well as DCount..! Really interested what you said but any idea on what can we do in this case?

 

[isladogs]

Hi and thanks,

They said must change the HKEY_LOCAL_MACHINE\
SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE\MaxBufferSize to 50000 but i'm using Ms Access 2016 and i cannot see where is the Access Connectivity Engine\Engines\ACE\MaxBufferSize . Any idea?

Thanks again

I see nobody answered this. The easiest thing to do is do a registry search for MaxBufferSize. You may have several locations. IIRC last time I had this issue there were 5 registry entries and I modified each in the same way.

You should also consider modifying all entries for MaxLocksPerFile rather than just the one that you referenced in post #1

 

[Minty]

PMFJI, but I think we should still know the actual query involved that causes the error and the number of underlying records? 150Mb isn't a huge database if that includes table data.

DSum and DCount used in queries will ramp up the resources used, as they are effectively a sub query run individually against every record. I suspect you could improve the performance of those with some suitable joined queries or sub queries.

 

[gemma-the-husky]

First, I assume you do not have a corrupt form, which sometimes can give the same message.

So, assuming there is no error, and it's just a very complicated query -

What I would do is go back to the queries, and try to build up gradually to your final query. You may have too much data for the query. Your relationships may be wrong, or your table joins may be wrong, and you may be getting a cross-product cartesian join that us trying to produce too many rows.

One way of getting to your end result might be to produce a temporary table part way through, and then continue from that table.


Hope this helps.

 

[The_Doc_Man]

A query that contains domain aggregates is essentially a query within a query. The issue is that you have to open a new channel because Access is actually a file-oriented system. Each new query opens the file on another file channel because the other / outer query is still open and thus its file channel is not available. As a result, if your query potentially involves tens of thousands of records, that is also tens of thousands of file-open actions. They will all close eventually, but the file handle allocation routines internal to Access PROBABLY take the easiest path, which is to just allocate a new handle each time rather than trying to find one to re-use. (I say probably because Access is a "black box" to us. We don't know for certain what it actually does inside.)

A way around this is to see if you can devise an SQL Aggregate query that does the COUNT or SUM that you need, then use a JOIN between the query you wanted and this aggregate query. That way you open only two queries/two file channels for all rows combined. Show us one of your queries with that Domain Aggregate and maybe one of us will see the way to do that JOIN that doesn't involve the implied queries of the domain aggregates. And yes, doing a JOIN of queries is legal - and often quite productive.

 

[InstructionWhich7142]

I went through this thread and some others, for reasons of ODBC compatibility with Foxpro i'm stuck on 32bit, and Access 2016/office365 has only recently been made Large Address Aware, so you may find your issues have resolved themselves, if you're still on 2010 it turns out this simple utility lets you change it to 4GB without issue, I've been running like that for a year or two on 2010 without issues,

Large Address Aware.exe

you can check your memory usage with

vmmap

amusingly I just found this as well

Access 32-bit Is Getting Large Address Aware (LAA) | DEVelopers HUT

Announcing that Microsoft has finally added making Microsoft Access 32-bit Large Address Aware (LAA) to their Microsoft 365 Roadmap.

www.devhut.net