Solved Opening an Access database to a form, then get and error.

[JMan_1018]

Hello All,

This is my first post, so please be kind. I have created an Access database and been using it for probably 10+ years. It opens to a form called frmMain. Now all of a sudden when I open the database it looks like it is working, but I click on a tab (I created) in the form and get the error "There isn't enough free memory to update the display. Close unneeded programs and try again.". I have been trouble shooting it for days. Strangely, I have found that if I don't load the frmMain on opening and open the frmMain manually from the Navigation Pane, it works like normal and don't get the error. Any ideas?

Jody

 

[Pat Hartman]

Welcome aboard.

Strange error. I wonder if the error message isn't what we think it is. Has something changed in your environment? Are other programs having trouble with displaying data? Have you rebooted recently?

Let's start with the first steps. Have you tried compacting the database? Does it compile without error? Is the form initially opened by a startup form reference or by an AutoExec macro? If by a macro, what else does the macro do?

 

[gemma-the-husky]

I don't know if you have back up databases. Maybe delete the form causing the issue, and reimport from a back up.

 

[JMan_1018]

To answer Pat, I have many Access database and there is no issue with them. Reboot every day in the morning. I have run the Compact and Repair Database, with no errors found. I set the initial startup under "File/Options/current Database/Display Form:".

A little history. This database was linked to our ERP database via ODBC so I could use the part numbers from the ERP database. Then about 3 years ago we changed ERP software, but still used the old ERP as reference. I hadn't changed it over. Then a couple of weeks ago, I started having this issue with the error message and was thinking the linked database was causing the issue. Sure enough Access ODBC connection was not working. So, I deleted the old ODBC connection and linked via ODBC to the new database. Changed all my queries and forms to work. Still getting the error message. Then I accidentally discovered the database worked if I didn't use the frmMain to startup, but even then, the frmMain worked on startup, I would just start getting the error message when I picked the tab.

 

[JMan_1018]

Pat asked about an Autoexec macro and got me thinking maybe if I used an Autoexec macro to open the frmMain, that it would bypass something causing the issue. That didn't work.

 

[The_Doc_Man]

The next thing to check - should be quick to do this: Windows Start >> Settings >> Update and then look on that page for the link to Update History. See if the last update corresponds to the time you started to see this problem. Since you mentioned an ODBC driver, it triggered all sorts of alarm bells in me because of the depredation so frequently imposed by Windows driver updates.

 

[cheekybuddha]

Salesforce Connector is broken - accessforever.org

 

[isladogs]

Pat asked about an Autoexec macro and got me thinking maybe if I used an Autoexec macro to open the frmMain, that it would bypass something causing the issue. That didn't work.

Are you aware that the startup form loads BEFORE the autoexec macro runs. Try removing the startup form from Access options and instead running it as the final item in autoexec

 

[ebs17]

I would suspect less of a data problem. If the amount of data to be loaded is larger than the currently available memory, the data is automatically switched to the swap file on the hard disk. The result is that the processing speed drops significantly. Or should the swap file be too small?

I would suspect that you are exceeding the limits with the number of controls loaded and their actions. Conditional formatting is also labor-intensive.

 

[Josef P.]

I seem to remember that I had a similar error message in context with conditional formatting.
In my case, however, this was independent of whether the form was set as the start form or was called up manually.

 

[JMan_1018]

Are you aware that the startup form loads BEFORE the autoexec macro runs. Try removing the startup form from Access options and instead running it as the final item in autoexec

When I created the autoexec macro to open form frmMain, I removed it from the startup in the Access options. Still received the error message. Is there a way to open my frmMain after startup, other than autoexec or Access options?

 

[JMan_1018]

I'm still trying to understand why I can run this database perfectly if I manually open the form frmMain, but as soon as I open the form using the Access Options or in an Autoexec macro then I get the error message.

 

[Gasman]

Can you try on another computer?

 

[JMan_1018]

Can you try on another computer?

Yes, I've tried it on 4 different computers and with the same results. Two were running 32bit and two were running 64bit versions of Access.

 

[Gasman]

Have you tried a decompile and recompile?

 

[JMan_1018]

Have you tried a decompile and recompile?

I was just reading about how to properly do that. I have never done it befor.

 

[isladogs]

I'm still trying to understand why I can run this database perfectly if I manually open the form frmMain, but as soon as I open the form using the Access Options or in an Autoexec macro then I get the error message.

I doubt decompiling will help in this case as the form loads without error from the nav pane

What you described can sometimes happen if there is a lot of processing to do in the Form_Load event.
You could try building in several short delays by using a DoEvents line after each line if Form_Load

Or perhaps a better solution is to move most of the heavy processing out of the Form_Load event.
Try the following:
1. Set the timer interval to 1000 (1 second)
2. Move some/all of the Form_Load code to a Form_Timer event
IMPORTANT: Add the line Me.TimerInterval = 0 to the timer event so it only runs once

 

[The_Doc_Man]

When I see an error message relating to "free memory" I have to ask how much virtual memory you have set aside for your system. What I am about to describe is an experiment to determine whether your problem really IS what I think it is.

Start Windows Task Manager before you launch your Access app. Switch to the performance tab (along the top). Down the left side, click on Memory. Look at the bottom. There are several statistics to consider. There is "Available" which is normally measured in Gb. If it is less than 1Gb you may already have a memory overload from other "stuff" to be displayed. There is also "Committed" which is expressed as a fraction, shown as xxx GB/yyy GB. If the xxx number (numerator) of the fraction is close to the yyy (denominator) i.e. the fraction would be close to 1, then you probably have insufficient virtual memory.

Now launch your app. If you do, when that error pops up, immediately check the memory stats again to see if anything has gone to zero or to a very low number. This would work best if your app DIDN'T launch maximized because you could continuously watch the performance of memory.

The message's reference to Free Memory suggests to me that your system memory is clogged up and there is neither enough available free physical memory, nor enough virtual memory to make room by image swapping, for something that happens during Form_Open or Form_Load. Offhand I would GUESS (and this is emphasized as such) that both the form visualizer code AND the form's container are open at the same time, and that other things are going on during startup that involve having other components and their sources open at the same time. (E.g. a general module gets loaded to memory - but for a brief time both the allocated memory area AND the compiled code co-exist in memory as the code is getting loaded.) Your ability to manually open the form later makes me think that it is just the general load on memory brought about by startup, where the memory object and its load source will be in Access's memory space at the same time.

You can also check your virtual memory file. One of these articles will be the right one for you. (First is Win10, second is Win11.)

Tips to improve PC performance in Windows - Microsoft Support

Learn how to improve Windows PC performance if your device is running slowly.

support.microsoft.com

Tips to improve PC performance in Windows - Microsoft Support

Learn how to improve Windows PC performance if your device is running slowly.

support.microsoft.com

#4 in the list is "managing the page file size." Be sure you have the swap/page file set up and non-zero in size. The general rule of thumb is to allocate between 25% and 50% of your physical memory size (if possible) On my new system with 32 GB of RAM, I'll probably settle for 8 GB because I don't usually run games that hog memory THAT much. In any case, this setting would drastically affect "free memory" (among many other memory categories in the Windows Memory Dynamics routines) and might help you get past the speed bump during your app's startup.

 

[JMan_1018]

I saw little change in performance before and after opening. I have tried opening on 4 different computers and some of them have 32 GB of ram. All do the same thing.

 

[JMan_1018]

I doubt decompiling will help in this case as the form loads without error from the nav pane

What you described can sometimes happen if there is a lot of processing to do in the Form_Load event.
You could try building in several short delays by using a DoEvents line after each line if Form_Load

Or perhaps a better solution is to move most of the heavy processing out of the Form_Load event.
Try the following:
1. Set the timer interval to 1000 (1 second)
2. Move some/all of the Form_Load code to a Form_Timer event
IMPORTANT: Add the line Me.TimerInterval = 0 to the timer event so it only runs once

Is this where I set the timer interval (see attachment)? I apologize that I'm not sure where I add the Me.Timerinterval = 0?

I also discovered when I tried opening on another PC that the first time I open it, it worked flawlessly. The second time, and thereafter, I start getting the error message. I also thought it may be happening because I am opening it from a shared folder on the server, so I copied it to my desktop and opened it. Again, the first time it worked the second time I opened it, I stated getting the error message.