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.
Does it seem like the "Out of memory" and "System resource exceeded" errors are cropping up more than they used to in 32-bit versions of Microsoft Access?
Holy Bloated Access, Batman!
It's not your imagination. With each new version, the Access engine itself uses more memory than the version before. In fact, with the latest 32-bit version of Access (Version 2110 (Build 14527.20276 Click-to-Run)), over half of the memory available gets gobbled up by Access:
To demonstrate this, I'm using the GlobalMemoryStatus API. Here's the sample code (UPDATE [2021-11-22] the code below only works in 32-bit VBA; for 64-bit VBA-compatible code, see 64-bit Unsigned Integers in VBA):
Option Compare Database
Option Explicit
Declare PtrSafe Sub GlobalMemoryStatus Lib "kernel32" (lpBuffer As MEMORYSTATUS)
Public Type MEMORYSTATUS
dwLength As Long
dwMemoryLoad As Long
dwTotalPhys As Long
dwAvailPhys As Long
dwTotalPageFile As Long
dwAvailPageFile As Long
dwTotalVirtual As Long
dwAvailVirtual As Long
End Type
Sub ShowMemStats()
Dim Mem As MEMORYSTATUS
Mem.dwLength = Len(Mem)
GlobalMemoryStatus Mem
Debug.Print "Total memory:", BytesToXB(Mem.dwTotalVirtual)
Debug.Print "Memory used: ", BytesToXB(Mem.dwTotalVirtual - Mem.dwAvailVirtual)
Debug.Print "Memory free: ", BytesToXB(Mem.dwAvailVirtual)
Debug.Print "Pct used: "; Format((Mem.dwTotalVirtual - Mem.dwAvailVirtual) / Mem.dwTotalVirtual, "0.00%")
End Sub
'Convert raw byte count to a more human readable format
Private Function BytesToXB(Value As Long) As String
Select Case Value
Case Is > (2 ^ 30)
BytesToXB = Round(Value / (2 ^ 30), 2) & " GB"
Case Is > (2 ^ 20)
BytesToXB = Round(Value / (2 ^ 20), 2) & " MB"
Case Is > (2 ^ 10)
BytesToXB = Round(Value / (2 ^ 10), 2) & " KB"
Case Else
BytesToXB = Value & " B"
End Select
End FunctionI created a new, empty database. I copied the above code to a new code module. I closed and reopened the application, then ran the ShowMemStats routine. The results are shown in the screenshot above. I encourage you to try this yourself and post the results in the comments below (to get the build info, go to File > Account > About Access).
Take note that out of 2 GB of total virtual memory at our disposal, Access gobbled up nearly 60% of that in pure startup overhead.
The upshot? On a device with 32 GB of physical memory and a 64-bit O/S, our Access application has less than 1 GB of memory to work with.
What's all this 2 GB talk? I thought 32-bit programs had access to 4 GB of memory.
In theory, yes, that is true. In practice...it's complicated.
When we talk about 32-bit vs. 64-bit programs, we are really talking about the number of bits available to address memory. The largest 32-bit number is about 4 billion. Thus, with 32-bit memory addresses, there is a hard cap of 4 GB of memory. It would be impossible to uniquely identify individual blocks of memory beyond the 4 GB limit.
However, operating systems tend to reserve about half of the available memory for their own use. On a computer with a 32-bit operating system, the memory available to applications is typically only 2 GB (half of the max).
The most succinct explanation I could find comes from the Excel LargeAddressAware feature announcement (emphasis added):
In the Windows 32-bit architecture, the address space for any program is shared between the application (user mode memory) and the operating system (system or kernel memory). For a 32-bit process, the total amount of addressable memory is 4 GB. By default, this memory is evenly divided between the process and the system. To support programs that may require more memory, Windows supports the LAA memory layout. This functionality is used only if the program can support it and identify itself as providing this support. LAA lets the system allocate more process memory at the expense of keeping less memory for itself.
The current design change to 32-bit Excel makes it LAA-supportable and identifies it to Windows as an LAA program. The maximum amount of memory that Windows can provide to the program depends on the system bitness. 32-bit Windows systems can allocate no more than 3 GB for user mode memory. This shrinks available system memory to 1 GB. (A 32-bit system cannot exceed 4 GB total RAM). On 64-bit Windows systems, the addressable memory space for the system is much larger, and the system memory can be located outside the 4-GB limit. Therefore, the maximum available user memory for a 32-bit process that's running on a 64-bit system is the full 4-GB addressable range.
Since most operating systems these days are 64-bit and most computers have more than 4 GB of physical memory, we could double the total amount of virtual memory available (from 2GB to 4GB) if msaccess.exe was large address aware.
Tripling the Available Memory (and then some)
Let's go back to the screenshot above.
There are 2 GB of virtual memory. At startup, however, the latest version of Access consumes 1.2 GB of that amount. This leaves us with only 800 MB of memory for our application code. It's surprisingly easy to chew through that much memory when you start doing moderately complex things in Access.
Now, let's imagine we go from 2 GB of total memory to 4 GB. The amount of memory Access consumes for itself should remain at 1.2 GB. That means the amount of memory available to our application will go from 800 MB to 2.8 GB.
That is a 350% increase in the amount of available memory!
Sounds Great! How Do I Take Advantage?
I've got good news, bad news, and dangerous news.
The good news is that the LargeAddressAware feature is on the Access roadmap.
The bad news is that it's not estimated to be delivered until September 2022. And it's already been pushed back at least once (I think the original estimate was Q4 2021).
The dangerous news is that you can do this yourself if you are feeling frisky. Former Access MVP Philipp Stiefel has all the details here: The /LARGEADDRESSAWARE (LAA) flag demystified.
Referenced articles
Mike Wolfe
External references
Image by Jackie Williamson from Pixabay
UPDATE [2021-11-22]: Added a link to my 64-bit Unsigned Integers in VBA article with sample code that works correctly under 64-bit VBA. Thanks for reporting, Jörgen!
