Microsoft Access Memory Issue after December 6 2022 Build15831 Office Update

Remotely hosted or on-premises?

The SQL Server Driver is in the connection string.

Correct the Sql Server is on a server that we connect to via odbc.

Having the issue with both SQL Server 2017 and 2019 at my office.

Further testing on my part (limited time so far, so not thorough) indicates this problem does seem to appear with a remotely hosted SQL Server, but not a local installation on the same computer.

Please let us know SQL Server Driver versions in use, and also are your users linking to a remotely hosted SQL Server or an on-premises SQL Server.

Hi Tom, we are in the process of adding 64bit functionality but have to deall with several external utilities (ex: pdf viewers, redemption, etc) so it's a big job to have our programs work with both 32 and 64. Hoping to have that done in the next month or two.

We have started adjusting users to semi-annual to get around it also. Better practice to be set to that but my concern is that the issue isn't addressed and it eventually hits all of my customers.

Thanks

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