Microsoft Access Memory Issue after December 6 2022 Build15831 Office Update

Hi Tom, unfortunately some of my customers are set this way. We make recommendations but their IT dictate the update policy. I'll have changing to semi-annual in my back pocket as an option as it comes up.

Thanks

As a follow up, I just ran a query against a SQL Azure backend. I am also on MS 365, Build 16.0 .15831.20098 32-bit.

I used a Cartesian query that generated over 8.5 million records. Memory usage did climb while the query ran, but not enough to cause an out of memory condition, not out of the ordinary in my experience, really. Memory Usage didn't drop, according to Task Manager, when I closed the query, but when I closed the accdb, it did.

It might be helpful to describe a testing scenario in which you see this problem resulting in an Out of Memory situation.

Hello, if the user closes the program it does clear the memory out. The issue is if the continue to use it over the day or days. The memory keeps climbing until there is none available.

In your scenario what happens if you run the same query multiple times without closing?

I have been otherwise occupied all day without time to test further. The two times I did try, memory usage stayed at the same level after closing the query, and only dropped when the accdb was closed. I should be able to return to testing later today.

It did occur to me to ask what version of SQL Server you and your users have and which SQL Server driver version you use.

Hi Shirk,

Is there is a reason why you and/or your clients really need to remain using 32-bit Office? Perhaps there is a valid reason, such as a required ActiveX control for which a suitable 64-bit replacement is not (or no longer) available. Also, if any of these same clients tend to use gargantuan Excel spreadsheets, they will benefit immensely using 64-bit Excel versus 32-bit Excel.

I’ve been helping a Chiropractor for the past 3-4 years, who has an awful DB design (very poor normalization, relationships w/o enforced RI, lots of calls using SendKeys, you-name-it). His FE application still pulls every patient record from a main table (over 1200 records now, whether they are current patients or not), and displays data from many other tables on bound subform’s found in a tab control with a dozen pages. Let’s call this his “kitchen sink form”—it is overflowing with dirty dishes!

He was on 32-bit Access (Microsoft 365 / Current Channel) and was not surprisingly suffering frequent (daily -- 2 to 3 times per day) Out of Memory errors that required him to restart Access each time. He was also periodically getting bit-in-the-rear-end by code changes pushed by Microsoft, on the Current Channel, that weren’t ready for prime time.

As you may know, 32-bit applications are normally limited to 2 GB of RAM, although the undocumented Large Address Aware (LAA) trick can be used to get up to 3 GB available. (Outlook & Excel have LAA “baked-in” in 32-bit; I’m not sure about 32-bit Word). Still pretty skinny when you consider that each release of Office, including Access, tends to want more RAM, leaving less available for working data. He was too nervous to try implementing LAA in Access, even after I gave him a link to watch Access MVP Karl Donaubauer’s excellent presentation on this topic (https://www.youtube.com/watch?v=JTTsyNzY\_i8).

To his detriment, the Chiropractor has resisted many suggestions I’ve made, such as returning only the records you need to look at (the “golden rule” of databases), and using the Tab on Demand technique, so that each subform’s recordsource is only queried when a user clicks on the tab. There were also tons of DAO recordset code where no attempt was made by the developer to clean up at the end of each procedure—a known memory leak. That part, along with the extensive use of SendKeys, has since been fixed and some relationships have had referential integrity enforced. Not surprisingly, other tables are full of records with unmatched foreign keys, but so far, the Chiropractor doesn't want to delete any past data.

I finally got him to agree to test 64-bit Office on one PC, after a hard drive failed and he had to reload all software programs. He was very happy when he reported back that this PC had not presented any Out of Memory errors in two weeks after he started using it. I did have to first convert several API calls to use conditional compilation, to be both 32 & 64-bit compatible. I also had to remove an ActiveX control he uses for a signature pad, but that’s not been a problem because his patients use just one dedicated computer with a signature pad attached. That one computer of six total is the only one that continues running 32-bit Access. And that computer isn’t used to open his massively busy “kitchen sink form”.

Finally, I got him to accept and implement my recommendation to convert all six computers from Current Channel to Semi-Annual. That wasn’t a hard sell on my part; Microsoft sold him on that idea after he paid the price for a second time.  Me: “This wouldn’t have happened if you had taken my earlier suggestion…”.

Tom Wickerath

MS Access MVP Alumnus, 2006 -- 2012