System Resource Exceeded (1 Viewer)

gstylianou

Registered User.
Local time
Today, 19:04
Joined
Dec 16, 2013
Messages
357
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.
 

Attachments

  • error.JPG
    error.JPG
    17.2 KB · Views: 544

theDBguy

I’m here to help
Staff member
Local time
Today, 10:04
Joined
Oct 29, 2018
Messages
21,358
Hi. Did you say the error only happens when you do a particular process? If so, can you describe this process? Thanks.
 

gstylianou

Registered User.
Local time
Today, 19:04
Joined
Dec 16, 2013
Messages
357
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

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Sep 12, 2006
Messages
15,613
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

Immoderate Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 19:04
Joined
Dec 16, 2013
Messages
357
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

MVP / VIP
Local time
Today, 17:04
Joined
Jan 14, 2017
Messages
18,186
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

AWF VIP
Local time
Today, 17:04
Joined
Jul 26, 2013
Messages
10,354
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

Super Moderator
Staff member
Local time
Today, 17:04
Joined
Sep 12, 2006
Messages
15,613
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

Immoderate Moderator
Staff member
Local time
Today, 12:04
Joined
Feb 28, 2001
Messages
26,999
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

Registered User.
Local time
Today, 17:04
Joined
Feb 24, 2010
Messages
199
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,

Code:
Large Address Aware.exe

you can check your memory usage with

Code:
vmmap

amusingly I just found this as well

 
Last edited:

Users who are viewing this thread

Top Bottom