Today I thought I’d try and cover the subject of Access reporting back errors like:

System Resource Exceeded

There isn’t enough memory to perform this operation

What’s the Source of the Problem?

Many report these issues occurring on databases that were working fine for years and once they upgraded Access versions, the newer version would report such an error and then crash.

First, it is important to understand that Access does not access all of a computer’s RAM, so having 16, 32, 64 GB RAM changes nothing to the problem.  Access has access only to a limited amount of RAM which depends on the bitness of your Access installation:

• A 32-bit installation of Access has a hard limit of 2 GB of virtual memory (4GB total virtual memory => 2GB for Windows and 2GB of processing) regardless of what is physically available on the system itself
• A 64-bit installation of Access has a hard limit of 8 TB of virtual memory (16TB total virtual memory => 8TB for Windows and 8TB of processing).

Note: The above information regarding the limitation of 32-bit and 64-bit installations comes from the youtube video on LAA given by Karl Donaubauer (Access MVP) for which a link is provided a little further in this post.

As such, a 64-bit version of Access can access orders of magnitude more RAM (if available) and it is thus highly unlikely to see such an error in an 64-bit installation.

Also, with each new version of Access, the core program itself consumes more and more of the memory leaving less and less for the database itself to use to perform operations. This is why databases that previously ran fine in prior versions suddenly start generating this error on more recent versions of Access or after some new update from Microsoft.  As such, upgrading Access versions can actually be detrimental!

For instance, I logged the RAM consumption by Access with nothing open but the main window at startup and the results are quite telling IMHO.

Here is a graphical representation of what has happened over the years.

So since version 2003, we have effectively lost almost half a GB of RAM (483 MB) for our processing!

So, if you were using 2003 and jump to 2016 the RAM being consumed by Access itself has gone up by 246% and now, instead of having 1878 MB (2GB – 170MB) of RAM available to you for your operations, you now only have 1460 MB (2GB – 588MB).  That is a substantial decrease in resource for handling heavy queries and other operations!

The data above also illustrates how MS updates seem to make this issue worse over time.  Both my installation of Access 2013 and 2019 (I haven’t validated my 2016 installation) have seen the available RAM diminish as various updates have been applied.

Some Reported Workarounds

Sadly, there isn’t any magic bullet to this issue, but there are a few workarounds that have helped some in certain scenarios:

• Remove Calculate Fields. Some user have reported that once they removed Calculated data type fields from their tables, and switched to creating the calculation in queries using standard expressions, they no longer receive such errors.
• Uninstalling/Disabling add-ins.  Don’t forget that your Access Add-ins take memory away from the available memory leaving you with less for your operations.  So if you don’t absolutely need/use add-ins disable/remove them from loading by default.  I did a quick test on my Access 2013 x32 installation and gained 125MB of RAM by disabling a single add-in!
• Some people have reported that changing their CPU affinity to a single CPU core in the task manager for the msaccess.exe process solved this issue.You can do so through the command line by doing something along the lines of:

  C:\Windows\System32\cmd.exe /c start "Microsoft Access" /Affinity 1 "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"

• Others had success by breaking complex queries into simpler operations and chaining them together or using temp tables as intermediary vessel for complex queries.
• Simplifying complex forms, breaking them into separate forms, using dynamic content loading techniques, etc…
• Closing all open objects prior to opening complex forms, running heavy queries.
• Another potential solution for 32-bit installations specifically is to try and make Access LAA (Large Address Aware) which enables it to use more memory, see: https://youtu.be/JTTsyNzY_i8
• You can try increasing your MaxLocksPerFile and MaxBufferSize values.
• Switching from 32-bit to a 64-bit installation can be the solution in some instances.  That said, switching bitness can lead to other issues and require updating ActiveX controls, APIs and coding, …
• For some, the only answer is to go back to using an older version of Access that doesn’t suffer from the same memory limitations.

All this to say this is the type of issue that will require some trial and error on your part to find an acceptable workaround.

As always, Compact and Repair is always a recommended starting point to reduce the file size to a minimum.  Don’t forget to make a backup prior to playing around with any of these settings (you’re always better safe than sorry!).

LAA Finally Here!

The real solution for 32-bit installation has finally arrived. It only took how many years (6+ years!), but LAA if finally here!

Sadly, once again, no official Microsoft Access Dev Team article on this new ‘feature’ release.
 

Windows 11 And Resources Exceeded

Well, just as we thought we were turning the page on this issue…

Just a heads up that there are reports that even running 64-bit installations of Microsoft Access, if on Windows 11, there are once again issue with resource exceeded.  Same db run on Windows 10 – no issue, run on Windows 11 error!

So be forewarned.
 

Other Resources On The Subject

Large Address Aware Flag

Getting a lot of “Out of memory” errors in newer versions of Access? This trick can more than triple your available memory in 32-bit versions of Access.

The /LARGEADDRESSAWARE (LAA) flag - Remedy for out-of-memory errors

How to apply the /LARGEADDRESSAWARE (LAA) flag to the MsAccess.exe to get rid of out-of-memory and resources exceeded errors.