MS Access Memory Issue

Multiple users at my company are experiencing this same issue, which seems to have emerged with version 2211.

We can reliably reproduce the issue by running make table queries that rely on several SQL-based back end tables. As we run these queries, we see that Access consumes additional memory (and does not release any when the queries complete successfully.) On the third or fourth query, when Access consumes ~975 MB of RAM, we get this warning: "There isn't enough disk space or memory to undo the data changes this action query is about to make. Do you want to run this action query anyway". Clicking "Yes" allows us to complete the query successfully, but any subsequent attempt to run a query results in a "Query is too complex" error.

The issue doesn't seem related to any particular query. The order we run the queries also doesn't seem to matter. The issue always presents itself when we approach the 1 GB memory threshold.

I realize that this issue seems related to queries using SQL/ODBC-linked tables.

What are the appropriate next steps? What's the best way to communicate this information to MS Support?

https://answersstaticfilecdnv2.azureedge.net/static/images/image-not-found.jpg

Hi,

I can also only guess so far, but since you asked for my thoughts:

My impression is that since the December update there has been no general increase in reports of memory problems, but only a good handful of reports in connection with SQL Server backends. If it were a general Access problem, I would expect more reports from Access-only users, who are in the vast majority.

Therefore, I think that "generalising" away from SQL Server is unlikely to take things further, and runs the risk of dispersal, unless there are palpable similarities in the processes, data volumes, symptoms, error message. Usually it's better to focus as specifically as possible on existing problem environments and cases.

In the other discussion here last week, affected poster Stuart Turner1 wrote that he could reproduce it consistently. Unfortunately, he did not answer again when I asked back. A reproduction scenario, a SQL script, even remote access to a consistent reproduction etc. This kind of things we would need for Microsoft, because for them, too, the scope of the problem is unclear so far, which makes it difficult to investigate.

Servus

Karl

The consistent thing we have seen in similar reports of memory problems all include using SQL Server backends, so that definitely is significant.

When MS wants to investigate such reports, the most efficient way to proceed is to supply them with a clear replication scenario that can help focus on the issue. Otherwise it could be an extensive, time-consuming discovery process.

Karl, as of now we don’t know where or what the issue is. We are using sql server but I’m not sure that is the issue. The process I put together was to show memory usage growth running a simple make table query with that office update. It doesn’t increase memory usage without the update. The issue seems to happen when I run processes with a lot of queries so think this query plus another 20 running which causes it to use up all ram.

My programs are 100-300 meg so tough to give you the exact replica.

I will look into the virtual test. Let me know your thoughts.

Hi,

I'm not sure if the example helps and it therefore makes sense to pass it on. It doesn't look like a "reproduction" scenario to me, as neither SQL Server is involved, nor the processes of the real problem cases, nor the error message, or are they?

As written in the other thread, the error message usually often happens due to lack of virtual memory (not necessarily RAM). Have you checked this? You can do that with the tool VMMap from Microsoft that you can run in parallel with your real problem scenario. Maybe it helps to find out what kind of memory problem it is and with which processes or amounts of data related to SQL Server it occurs.

Servus

Karl