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

[SuperShin]

I have a front end database that I've used since about 2002 (in .adp format back in those days)

We recently migrated it from 2010 to Office 365 so of course that meant migrating from .ADP to an .ACCDB via Linked tables so we migrated to ODBC linked tables.

The SQL backend was 2008 at the time as well. It was constantly complaining about out of memory / system resources exceed / There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.

So to be clear my dev laptop has 11 terabytes of storage / 32 GB of memory / 20GB swap file on an SSD / Massive I7 processor. So clearly these messages are erroneous in some regards so I looked into all the error messages as hard as I could.

I've changed the MaxLocksPerFile from 9500 to 20000 in the registery, I've increased the swap size of my swap file, I've made sure to only have one form from the application open at a time, I've set processor affinity for opening the file, I've migrated from 2008 SQL to 2016 SQL, I've but nothing has alleviated these problems.

One form is pretty complex and does a lot of things, however many other forms are very basic. I can reproduce the "there isn't enough memory" errors by opening 10 forms at once or just using the large for for an extended amount of time, closing and reopening the database will allow me to keep going, however I do NOT think its a memory link via VBA. Clearly though the errors come from something within Access usage that never occurred while it was an ADP project (dont you miss those btw.) The users are working around it by closing out when they hit an error then logging back in, but i hate that!

Help me Obi Wan Kenobi Access Users, you're my only hope.

 

[Galaxiom]

Is it the front end or back end that is runing out of resources? What version of SQL Server are you using? Express or Standard?

 

[SuperShin]

Is it the front end or back end that is runing out of resources? What version of SQL Server are you using? Express or Standard?

I highly doubt its the backend since it happend on the origional server that ran the .adp (ran on that SQL backend server for 10 years with no issues) and then i stood up a brand new VM that is not taxed on resources at all. It has to be the front end in my opinion, its standard SQL. The new VM is purpose built for SQL 2016 standard only and has plenty of Ram and CPU dedicated.

My dev machine is made of pure win so its not the actual machine it has to be something about the Access front end in my opinon happens on every client machine across multiple forms and users and physical environments. NEVER happend as an .ADP only on ODBC connections, although i dont know that it has anything to do with ODBC its not really a time out issue.

 

[The_Doc_Man]

"System Resources Exceeded" isn't always about out of memory per se. Sometimes it is because the app consumes all the slots in a fixed-size area that holds graphic or file-related items that are temporarily assignable resources for use by programs.

Resource (Windows) - Wikipedia

en.wikipedia.org

Menus and Other Resources - Win32 apps

The resources section describes window resources such as icons, cursor, and dialog boxes.

learn.microsoft.com

Most likely, something is opening a file and not properly closing it or releasing the resource. This failure accumulates until it hits the limit. From your discussion, this started when you changed formats... and methods of connection. Do you open a LOT of recordsets in this process? By "lot" I mean more than just a couple of digits worth.

 

[SuperShin]

I mean I open 20 or less but i always close them and set them to nothing when done. I can also make the system crap out by just opening 10 or so forms in quick succession not just the beefy ones. Eventually it will complain or combo boxes will just be blank. Thats one wierd thing I've noticed combo boxes with missing recordsets but of course closing the form and reopening solves it as a work around. Very difficult to pin down,

The forms are 100% of the time bound to a table via ODBC and i allow searches via DAO to move around records. The main tables have less than 5000 records though.

I have ran into the graphics issue before so I do know what you mean there for sure, if i recall though those errrors were not enough memory to update the display or some such. There are photos of employees that get loaded on the main form for sure so you may be onto something there. There are two images boxes on the main form and the photos are stored on a network share and are .jpg .pngs etc. They get loaded on current of the form if a photo exists for the employee. The database simply stores the filename.

    If IsNothing(Me![Photo]) Then
        Image.Picture = gPhotoDir & "NoPic.jpg"
        Else: Image.Picture = gPhotoDir & Me!Photo
    End If

Do you think that could be part of the problem?

 

[SuperShin]

This seems very promising and I intend to try many of these tricks out tomorrow.

https://codekabinett.com/rdumps.php?Lang=2&targetDoc=largeaddressaware-msaccess-exe

 

[The_Doc_Man]

There are photos of employees that get loaded on the main form for sure so you may be onto something there. There are two images boxes on the main form and the photos are stored on a network share and are .jpg .pngs etc. They get loaded on current of the form if a photo exists for the employee. The database simply stores the filename.

I make no claims of expertise here, but each photo opened that way involves a file handle, which is one of the types of resource that doesn't always get cleaned up properly. The photo file link counts as a consumer of file-handles because the photo is in a separate file that has to be opened in order to be displayed. But opening that file through the hyperlink or image reference isn't normally a visible action.

 

[isladogs]

Enabling large memory awareness will certainly help if you are using 32-bit Access. For 64-bit Access its bult in.
You can increase the MaxLocksPerFile to far higher than 20000. It may help.

Suggest you also check the number of available connections using my add-in

Available Connections Add-In

This is an Access add-in used to monitor the number of open databases/tables and help prevent 'cannot open any more tables' or 'cannot open any more databases' errors.

www.isladogs.co.uk

 

[SuperShin]

I do use 32 bit access for sure due to some ActiveX and the API modules are written for 32 bit (more so the activeX), I just deployed the Large Adddress Aware modified .exe and I read that Microsoft just enabled large address aware for access 32 bit in the June update for 2023 however I'm not sure what channel we are on so I have no idea when that updates going to push down at the moment, I'm just excited to try out the LAA .exe, Ill let everyone know.

I plan to add the virtual memory form to my database too as a real time monitor form maybe check memory every 15 seconds on the form and as a user is using it throughout the day log the results. As I was watching Karl's presentation it all made sense. My old .accdb and .adp projects were written in 2010 access -- that .exe takes up about 1.2 gb of memory leaving 850 for the actual application. When i switched over to Office 365 the newer version I am sure it uses MORE than 1.2gb leaving even less virtual memory for the actual application and thats why my users are seeing this happen. I could definately make use of the late binding techniques for subforms on some of my really heavy forms but first lets see if LAA solves my problem.

I will post back my findings

 

[isladogs]

I don't know where you read that LAA was enabled in version 2306. I have v2307 and it isn't enabled by default.

You should also be aware that each version update will currently disable LAA if you have enabled it. So I currently switch it back on about twice each month ... and have just done so again now! As Access 14 (2010 doesn't get updates, LAA remains enabled.

However, by itself, LAA won't solve all your issues. That's why I recommended several other things as well to help Access cope better in your situation.

 

[SuperShin]

LOL well then....one time to start optimizing this main form.

I couldn't find the sample database Karl used for the logging but i was able to find the stack overflow article and recreate my own and what can I say the form does a lot -- late binding of subforms is probably going to help this one out a lot.

 

[SuperShin]

I don't know where you read that LAA was enabled in version 2306. I have v2307 and it isn't enabled by default.

View attachment 109241
You should also be aware that each version update will currently disable LAA if you have enabled it. So I currently switch it back on about twice each month ... and have just done so again now! As Access 14 (2010 doesn't get updates, LAA remains enabled.

However, by itself, LAA won't solve all your issues. That's why I recommended several other things as well to help Access cope better in your situation.

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!

 

[isladogs]

I know Philipp's article well and I'm using a slightly modified version of his code to set LAA as I find it easier to use than the tool Karl publicised.
However, the article was last updated in Nov 2020 & there is no mention of June 2023 anywhere in the article.

FWIW I'm on the current channel for v2307 and the Beta channel on another machine
I'm not aware of any public or private announcement that LAA was being enabled yet despite repeated requests from several current and former MVPs. However, there are indications that it may well be enabled at some point.

To repeat, don't rely on LAA alone.
Review how the available connections change whilst your database is in use. Further increase the MaxLocksPerFile and see if it helps.

 

[SuperShin]

At its core I'm sure its a multi varied problem but LAA seems to really be making a difference but when I have one form that chews up 800mb that is to be expected, what are the key differences between your add in version and the version 3 of your table monitor, I will use those tool also thanks. After opening my 800 mb form this is the results of your add in

David

 

[isladogs]

... what are the key differences between your add in version and the version 3 of your table monitor,...

David

Maybe I'm being dense here but what exactly are you referring to here when you wrote 'version 3 of your table monitor'

 

[SuperShin]

I mean the access add in version vs the version 3 which is a sample .ACCDB with 1 table 1 form 1 report

 

[isladogs]

Both the add-in and standalone apps are v3.16. They reference CurrentDb & CodeDb respectively
The add-in is loaded from any Access app and shows the changes when loading/unloading objects in the host app.
The standalone version is provided if you want to import its components into any app of your choice.
The two articles do explain the purpose of each.

 

[spaLOGICng]

I have a front end database that I've used since about 2002 (in .adp format back in those days)

We recently migrated it from 2010 to Office 365 so of course that meant migrating from .ADP to an .ACCDB via Linked tables so we migrated to ODBC linked tables.

The SQL backend was 2008 at the time as well. It was constantly complaining about out of memory / system resources exceed / There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.

So to be clear my dev laptop has 11 terabytes of storage / 32 GB of memory / 20GB swap file on an SSD / Massive I7 processor. So clearly these messages are erroneous in some regards so I looked into all the error messages as hard as I could.

I've changed the MaxLocksPerFile from 9500 to 20000 in the registery, I've increased the swap size of my swap file, I've made sure to only have one form from the application open at a time, I've set processor affinity for opening the file, I've migrated from 2008 SQL to 2016 SQL, I've but nothing has alleviated these problems.

One form is pretty complex and does a lot of things, however many other forms are very basic. I can reproduce the "there isn't enough memory" errors by opening 10 forms at once or just using the large for for an extended amount of time, closing and reopening the database will allow me to keep going, however I do NOT think its a memory link via VBA. Clearly though the errors come from something within Access usage that never occurred while it was an ADP project (dont you miss those btw.) The users are working around it by closing out when they hit an error then logging back in, but i hate that!

Help me Obi Wan Kenobi Access Users, you're my only hope.

Sounds to me like there are memory leaks. Recordsets not be properly closed.

Also, forms and reports open in the background, especially that are not filtered record sets, will unnecessarily use precious resources. Forms and reports need be closed when not in use.

 

[isladogs]

@spaLOGICng
The available connections utility helps to check such memory 'leaks'

 

[Mike Krailo]

I don't know where you read that LAA was enabled in version 2306. I have v2307 and it isn't enabled by default.

View attachment 109241
You should also be aware that each version update will currently disable LAA if you have enabled it. So I currently switch it back on about twice each month ... and have just done so again now! As Access 14 (2010 doesn't get updates, LAA remains enabled.

However, by itself, LAA won't solve all your issues. That's why I recommended several other things as well to help Access cope better in your situation.

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