Access 2016 "System Resource Exceeded" memory leak problem when running a series of queries using ODBC connection to a SQL server or MySQL back end server.

Question

Hi everyone,

We are facing a serious Access 2016 memory leak problem which ends up to a "System Resource Exceeded" message. 

We have not been able to find a way to address it in any of other forums or Microsoft documentation.  

Our Access frontend application can be linked to an external Access file as backend database or use an ODBC connection to connect to SQL server or MySQL backend database server.   

We are running an import data procedure using an external mdb file which contains the data to be imported. The procedure opens the external mdb Access file and for each record queries about 5 tables to retrieve information that is used to calculate a result which is then entered in another table on the back-end database.

This procedure runs without a problem when the application is linked to an Access file as backend. The memory consumption of Access during the procedure remains stable and unchanged although the import procedure might take several hours to complete. 

The problem manifests itself when the backend database is a SQL server or a MySQL server (V5.7.19) and the connection established through an ODBC. When the above-described procedure is running the Access memory consumption starts to increase progressively and when it reaches approximately 1GB the  "System Resource Exceeded" message appears and the import process is interrupted. The user is forced to kill Access and start again. 

We have tried various ways to overcome the problem but none have resolved it. We found that using ADODB recordsets instead of DAO there is a smaller memory leak in each import cycle but the memory continues to increase and the message appears later. 

We have not found in other forums any other similar post. We tried various tweaks suggested for other problems that lead to the  "System Resource Exceeded" message but none of them seems to work.

Thanks for taking the time to read my post.

Any help would be great.

Answer 1

well there are a lot of moving parts here.  It is definitely a red flag that your import takes several hours....although good that it works for an Access back end - it indicates a conceptual design issue.  Of particular note is your description "...and for each record queries..."    If this is a vba looping design - that should be changed to a record set design.

A sequence of queries will run much faster than a loop.  Without knowing what the 5 queries do it is not possible to give more precise advice.

Answer 2

I agree with PublicIdentity that you should be querying whole record sets instead of each individual record at a time. And as your tables become bigger, this problem will get worse. Change the queries that import and append the data so that they contain all the records that meet your import criteria instead of only one record at a time. At least, that's what it sounds like what you are doing. But like PublicIdentity said, without seeing your queries and your VBA code that runs them, we don't have enough information.

Answer 3

Hi Nikos_D,

I can see that you are getting an error ,"System Resource Exceeded".

generally this error occurs when you perform a complex query with many records.

so to confirm that try to use simple queries and check whether this same error occurs or not.

if error does not occur means your queries are too complex.

try to make them simple to avoid this error.

you did not post any query and we don't know about your database design and how much data it contains.

so we can't find specific issue there.

you can try to test this suggestion and let us know about the results.

we will try to provide you further suggestions to solve the issue , if needed.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

Hi, how can i check

FREESystem Resource CURRENT SIZE ?

---

dBase,FoxPro,MS Access 2003,(2010=Not rec.),Office 2010+ACC.2013 ,Symbian C++, AC.2013.SystemResource.GetCurrentFreeSize=?

Answer 5

PACALA_BA:

Open your Task Manager

Answer 6

Hi Lawrence Ellefson

thx, but EXACTLY what

WORKING SET (MEMORY)...?

MAX_MEMORY......?

FREE MEMORY = MAX_MEMORY  - WORKING SET (MEMORY)......?

MY OPINION: It is really very wrong, there is no EXACTLY TOOL...

---

dBase,FoxPro,MS Access 2003,(2010=Not rec.),Office 2010+ACC.2013 ,Symbian C++, AC.2013.SystemResource.GetCurrentFreeSize=?

Answer 7

Hi Nikos_D,

is your issue is solved now?

I find that after creating this thread, you did not done any follow up.

if your issue is solved then I suggest you to post your solution here and mark it as an answer.

if your issue is still exist then I suggest you to refer the suggestion given by community members.

it may solve your issue.

if then also you have any further question then let me know about it.

I will try to provide you further suggestion to solve the issue.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Hi everyone,

The error was finally resolved after finding the following post in the MySQL ODBC forum:

https://bugs.mysql.com/bug.php?id=84160 

As Sławomir Białkowski suggested, we uninstalled MySQL ODBC version 5.3.8  and installed an older MySQL ODBC version 5.2.7 and the memory leak disappeared.

There is a bug in the 5.3 version that causes the memory leak.

Thanks for your responses and your time.

Nikos