Virtual vs. Physical Memory in the Context of Microsoft Access

Virtual memory is very important in 32-bit Access (but you can ignore physical memory).

Physical memory is important in 64-bit Access (but you can ignore virtual memory).

Let me explain.

Physical vs. Virtual Memory

Physical Memory

Physical memory refers to actual hardware known as Random Access Memory (RAM).

RAM is several orders of magnitude faster than a hard disk. That's because it uses several hardware techniques to maximize short-term speed while sacrificing the ability to retain data when it loses power. It's also much more expensive on a per-GB basis than a hard disk.

For performance, most of your computer's working memory is stored in RAM.

Virtual Memory

Virtual memory refers to the total amount of addressable memory.

It includes both physical memory (RAM) and the so-called "paging file", an area of the hard disk set aside to handle temporary reading and writing when RAM gets used up. From your application's perspective, the only difference between RAM and the paging file is speed–but it can be a BIG difference.

With 32-bit memory addresses, you can use, at most, 4 GB of memory (even if you had 16 GB of RAM).

Physical Memory: The Limiting Factor in 64-bit Applications

Applications use memory addresses to store and retrieve data while they are running.

These memory addresses are also known as pointers. When we talk about the "bitness" of an application or operating system, we are referring to the length of the pointers. A 32-bit application uses pointers that are 32-bits long. VBA7 introduced the LongPtr type as a convenient way to declare a variable that is 32-bits long when VBA is running in 32-bit mode and 64-bits long when it is running in 64-bit mode.

Why does any of this matter?

The length of the pointer determines how many places in memory can be "addressed." The largest 32-bit (unsigned) number is 2 ^ 32 = 4,294,967,296. In terms of bytes, that is equal to 4 GB. Thus, a 32-bit application can use no more than 4 GB of memory. Even on a computer with 16 GB of memory, the extra 12 GB would be inaccessible.

A 64-bit application also has a cap on how much memory it can address. It's just a much bigger cap. How big? Well, 2 ^ 64 = 18,446,744,073,709,551,616. In terms of bytes, it's 16 EB (exabytes). Which is more than 16 billion GB. Suffice to say we won't be seeing computers with that much physical RAM in a good long time. (Which is what people said when we went from 8-bit to 16-bit operating systems and from 16-bit to 32-bit operating systems. But this time I mean it!)

It turns out that the virtual memory available to your Access application is nowhere near 16 EB. It appears to be artificially capped at 128 TB, based on the results of the GlobalMemoryStatusEx API function:

According to the above screenshot, I have nearly 126 TB of free virtual memory. Something tells me my actual limit is much lower. On that particular system, I have fewer than 5 TB of hard drive space alongside 32 GB of physical RAM.

Ultimately, physical memory (RAM) will be the limiting factor in 64-bit Access.

That said, even if you exhaust your physical memory, Access will continue to run. Instead of writing to RAM, it will write to the paging file on your hard disk. This is much slower than RAM–and will kill your application's performance–but at least it shouldn't error out.

Virtual Memory: The Limiting Factor in 32-bit Access

If you've started noticing your users are getting more "System Resource Exceeded" and "Out of Memory" errors after updating to newer versions of 32-bit Access, it's not your imagination.

As the price of memory continues to fall, it's rare to come across a computer with fewer than 4 GB of RAM these days. That means the limiting factor for 32-bit Access is almost certain to be the amount of addressable memory.

As we discussed above, a 32-bit application can only address 4 GB of memory. Now, it would be bad enough if you were limited to 4 GB of memory in 32-bit Access, but the situation is actually much worse than that.

Because Access is still not Large-Address Aware, the application is only able to address 2 GB of memory. Now, it would be bad enough if you were limited to 2 GB of memory in 32-bit Access, but the situation is actually much worse than that.

As of Version 2110, Access itself consumes more than half of the available 2 GB of memory. That means your Access application would only be able to use about 850 MB of memory. Now, it would be bad enough if you were limited to 850 MB of memory in 32-bit Access, but the situation is actually worse than that.

At startup, Access itself has already consumed more than half of the available memory, leaving your custom application only about 850 MB of memory–even on a beefy computer with 32 GB of physical RAM.

What Could Be Worse Than Only Having 850 MB of Memory to Work With?

Inevitably, every new version of Access will use more memory for itself. But it gets worse.

Microsoft knows this is a problem. In fact, they have addressed this issue in every other Office application except for Access by enabling the Large-Address Aware flag, which exposes the full 4 GB of virtual memory to the application. Case in point: