Solved Database Application constantly out of memory / system resources exceeded after migration to ODBC SQL

[isladogs]

@Mike Krailo
Please see Philipp Stiefel's article: which was originally linked in post #12 but I've just seen the OP has since edited that section out
The article is at:

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.

codekabinett.com

Philipp's code MUST be run from another VBA program such as Excel with that run as an administrator as elevated privileges are required.
I just added 3 convenience functions to Philipp's code so I could run it on my dual installation of 32-bit Access 14/16:

Sub SwitchAccessLAAOff()
    Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE", TurnOffLaa
     Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", TurnOffLaa
End Sub

Sub SwitchAccessLAAOn()
    Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE", TurnOnLaa
     Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", TurnOnLaa
End Sub

Sub CheckAccessLAA()
    Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE", DisplayLaaStatusOnly
     Debug.Print "---------------------------------"
    SetLaaFlag "C:\Program Files (x86)\Microsoft Office\Office14\MSACCESS.EXE", DisplayLaaStatusOnly
End Sub

 

[isladogs]

I read that it became the default in June 2023 - whether you have that version would be dependent upon what update channel you are on -- I know we are quarterly with P1, that delcaration came from this website:

https://www.accessforever.org/post/now-you-can-open-more-tables-and-databases

Two important changes were made to the Access architecture in version 2305 which was released to the Current Channel early in June 2023.


1. The maximum number of open tables - doubled from 2048 to 4096


2. The maximum number of available connections - doubled from 256 to 512

Maybe I just assumed that was LAA and it wasn't

What other tricks do you recommend besides late binding of subforms? I'm all ears I have to get this resolved at least now I know what the issue is though!

I hadn't realised until now that you had edited post #12.

My AccessForever article was a more concise version of the article on my own website:

Now you CAN open more tables and databases

This short article outlines two recent improvements made to the Access architecture behind the scenes. Taken together, these should significantly reduce the number of 'cannot open any more tables/databases' messages and make it far less likely that Access applications will crash

www.isladogs.co.uk

There are several more suggestions in the longer article as well as other points I've covered earlier in this thread

 

[Mike Krailo]

OK, now it makes sense and I downloaded the module. I finished watching the AUG video with Carl's demo and thought you were just calling that "Large Address Aware.exe" program to do it. But instead with that imported module in the Excel application, It can then set or get the LAA status of whatever Access application you use it on.

 

[isladogs]

Both methods work but I do prefer Philipp’s approach. In either case, it’s annoying having to keep resetting it after each Office update. Hopefully that issue will be addressed in the near future

 

[SuperShin]

What is the code for SetLaaFlag in the above referenced routines?

No I was mistaken I think I just assumed LAA was on by default -- its not for sure, but on another note that seems to be the main issue with my front end i have that one 800mb heavy form which I can optimize with late binding of sub forms but as of now simply enabling the LAA flag (by whichever option you choose) seems to be solving my issue in the short term.

As for the code you can go here:

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.

codekabinett.com

HOWEVER you can't enable the LAA flag via VBA while you are inside the .accdb (kind of obviously) that you WANT to enable it in. So the technique that I used which to me seems the easiest is to google up largeadaware.exe and use it to set the flag on MSACCESS.exe (after you make a backup) and thats it -- the key caveat is that any microsoft update will overwrite the msaccess.exe and blow away your flag so you will need to do it again AND it needs admin access to do it. Honestly you don't even need this code either you can just check to see if you have 4GB of total virtual memory and if so its on and its 2GB you dont have it -- Either way.

So in my case with 25 users in one corporate office I can deal with that via some scripts to push it out via Active Directory -- your mileage may vary.

You can always use the code to see if its set and then deal with it however is best for your environment, but LAA is pure win, glad to learn about it!

 

[SuperShin]

This is solved obviously but I came back to do just a little more loop closure on how i setup a solution for our company moving forward because the LAA bit gets unset CONSTANTLY

I added to the batch file that is used to start our access application:

copy "\\dbserver\SystemShare\System\editbin.exe" c:\DBClient\System\ 
copy "\\dbserver\SystemShare\System\link.exe" c:\DBClient\System\ 
copy "\\dbserver\SystemShare\System\tbbmalloc.dll" c:\DBClient\System\ 

C:\Windows\System32\icacls.exe icacls "C:\Program Files (x86)\Microsoft Office\root\Office16" /inheritance:r /grant:r "Everyone:(OI)(CI)F"
c:\DBClient\System\editbin.exe /LARGEADDRESSAWARE "C:\Program Files (x86)\Microsoft Office\root\Office16\MSACCESS.EXE"

 start /affinity 1 msaccess "c:\DBClient\DBClient.accdb"

So basically the batch file is what people have on their desktop it copies over the client, OCX controls, and other things then starts the client.
The three files editbin.exe, link.exe, and tbbmalloc.dll come from Visual Studio 2022. That software is free to install and then i cherrypicked those three files out since editbin wont run without link and the DLL. So install visual studio and pluck these files out.

The ICACLS obviously sets the permission on the folder to everyone so that the editbin file can actually modify the LAA status. Now of course you could set this to your domain users write permission if you wanted to refine security a bit more but I actually don't care.

However this batch file has to be run as admin once to set the permissions which our guys will do once when installing office anyway for our users. From then on out the permissions are set and it will just always set the LAA bit switch before running access, so no matter how often microsoft sets it this will keep working.

I hope this helps someone as much as it has helped me.

 

[isladogs]

For info, LAA was added to the 365 Beta Channel a few weeks ago and will be included in the Current Channel in version 2311 towards the end of November

https://www.microsoft.com/en-gb/microsoft-365/roadmap?filters=Access

 

[Pat Hartman]

@SuperShin Thanks for the detailed explanation of your solution.
@isladogs Thanks for letting us know we probably won't have to use it

 

[isladogs]

Ha! I had tried to find this thread a few weeks ago but couldn't remember its subject line or the OP's name.
I'm on the Beta channel and have been using LAA successfully with absolutely no issues.

Also, the nav pane shutter bar vertical text glitch is fixed in the latest beta channel 2311 update yesterday. It will also be in the 2310 current channel release sometime next week