Solved Memory issues (1 Viewer)

[dussel1000]

Since some time I'm facing memory issues.

For example when compiling the code I get the following error message (without) code, saying Not enough memory

But also when trying to open certain reports it also sometimes shows simuliar memory errors.

I have tried several things:
- Enable LAA
- Starting Access decompiled and running repair and compress
- Starting with a blank Database and importing all objects
- Disabeling several References
- Running on different machines

I'm using MS 365 version 2310 build 16924.20150 on a Windows 11 PC.

Does anyone recognize this behavour or better has a solution for this?

 

[Ranman256]

1 think that takes all the memory is multiple Tabs with multiple datasheets in them.
all sheets will fill with data, sucking all memory.
Instead, use 1 subform with 1 datasheet.
then when tab changes, swap out the subform source. It frees up a LOT of memory.

 

[dussel1000]

Ranman thanks for your reply. I know that issue and I already implemented that in my application.

The compiling error already occurs directly after opening the database. No form or report is opened at that moment.

 

[LarryE]

Since some time I'm facing memory issues.

For example when compiling the code I get the following error message (without) code, saying Not enough memory

View attachment 110970

But also when trying to open certain reports it also sometimes shows simuliar memory errors.

I have tried several things:
- Enable LAA
- Starting Access decompiled and running repair and compress
- Starting with a blank Database and importing all objects
- Disabeling several References
- Running on different machines

I'm using MS 365 version 2310 build 16924.20150 on a Windows 11 PC.

Does anyone recognize this behavour or better has a solution for this?

Why are you attempting to compile this code? It is useless and does nothing. Delete Sub A and see if that helps.

 

[dussel1000]

I'm trying to check my complete VBA code on errors not this specific code. But I get the error instantly. Also when I change Sub A into Sub B I get the same error message. I can't change any code anymore.

So in one way or the other my database is corrupt.

I can step back to a version from a few days ago but every now and then (mostly after pasting a large text, about 50 rows of text, into a VB module) this comes up again.

 

[GPGeorge]

Since some time I'm facing memory issues.

For example when compiling the code I get the following error message (without) code, saying Not enough memory

View attachment 110970

But also when trying to open certain reports it also sometimes shows simuliar memory errors.

I have tried several things:
- Enable LAA
- Starting Access decompiled and running repair and compress
- Starting with a blank Database and importing all objects
- Disabeling several References
- Running on different machines

I'm using MS 365 version 2310 build 16924.20150 on a Windows 11 PC.

Does anyone recognize this behavour or better has a solution for this?

Your screen shot shows only Option Compare Database. Most experienced developers have learned that including Option Explicit is highly helpful in surfacing syntax errors. Add it, try to compile the VBA and report whether that compile reveals additional errors.

 

[The_Doc_Man]

As a simple test, do whatever it is that you do to generate this error. DO NOT close the error message box. Instead, open Windows Task Manager. Look at the performance page, memory tab, and verify that none of the memory statistics say "0 remaining." (This is actually VERY unlikely to happen, but it's an easy test.) Then switch to the processes page. Find your Access process and see what it says about memory size. If it is over 1 Gb, we might need to discuss your DB architecture in more detail. If it is no more than a few hundred MB, then it isn't the space limits imposed by the system virtual page/swap file.

Also, take a look at some of the entries in the "Similar Threads" header below the threads. Some of them might be relevant.

 

[Ranman256]

When db is close to 2 gig, it will throw random fake errors.

 

[dussel1000]

I checked task manager during the error and there is over 6 Gb memory remaining. Also the process doesn't take more than 160 Mb.

 

[The_Doc_Man]

OK, those facts indicate it is not a physical error or a Windows-level virtual error. Could still be a corrupted DB or a problem with a referenced library. Open the VBA screen, check references, and be sure that none of them show "missing" or "broken" when you highlight a particular reference library.

 

[dussel1000]

This issue is solved by reducing the amount of objects in the database. Removed all unused Query's, Forms and Reports and no problems since....

 

[The_Doc_Man]

Makes perfect sense. Thanks for reporting back with the solution! And good luck going forward with your project.

 

[Pat Hartman]

Can you tell us how many objects the database contained and how many you deleted?

 

[dussel1000]

Can you tell us how many objects the database contained and how many you deleted?

Forms: 437, about 35 deleted
Report: 247, about 40 deleted
Queries: 680, about 130 deleted
Modules: 250, none deleted
Tables: 101 local, 269 connected, none deleted

The numbers are after cleaning up my application

Total size of the application: ±100 Mb

 

[Pat Hartman]

Thanks. That's a pretty big app but doesn't exceed any limits.

Microsoft Access 2016 Maximum Limits and Specifications

Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.

www.avantixlearning.ca

Access Specification issues

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

www.isladogs.co.uk

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

Rather than just abandoning objects, I rename them with a "zzz" prefix so they drop to the bottom of the list and I know I am not planning on using the object again. For this method to work, you MUST have Change Auto correct set to NO. Otherwise Access will "help" you and rename references to the "zzz" name which is exactly what you don't want to happen. By renaming objects, my intention is to break any references to them that still exist. Every few weeks, I delete the debris. Change auto correct can be useful but only when you intend to use it. I keep it off and only turn it on when I actually want it to "help" me. Experts sometimes refer to this "feature" as Name Auto Corrupt because of the mess it can make when you don't actually understand how it propagates changes and most importantly WHEN.

For code, I comment it out with a date. Again, after a few weeks or so, I delete the commented out code. If I actually want to save it because I think it will be useful later, I have a separate database where I keep a table of code procedures. Only novices think that a line of code written is a line of code that must be preserved. Far better to clean up as you go and avoid confusing yourself or others later.

A reflex I have developed is to save and compile BEFORE I test new code. This prevents loss should I have made a mistake that sends Access into a tizzy and forces me to shut it down.

Another is to close the database at least once an hour during heavy development periods and zip the file before returning. It is also good to do this at logical breakpoints were you added something new, tested it and it works so you want to move on.

 

[dussel1000]

Thanks. That's a pretty big app but doesn't exceed any limits.

Microsoft Access 2016 Maximum Limits and Specifications

Microsoft Access 2016: maximum limits and specifications ... Microsoft Access training in Toronto, Ontario, Canada. Our instructor-led courses are offered in downtown Toronto.

www.avantixlearning.ca

Access Specification issues

Microsoft publishes a list of Access specifications and limits. Most of the information is correct. This article discusses some specification items that are misleading, out-of-date or inaccurate

www.isladogs.co.uk

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

Rather than just abandoning objects, I rename them with a "zzz" prefix so they drop to the bottom of the list and I know I am not planning on using the object again. For this method to work, you MUST have Change Auto correct set to NO. Otherwise Access will "help" you and rename references to the "zzz" name which is exactly what you don't want to happen. By renaming objects, my intention is to break any references to them that still exist. Every few weeks, I delete the debris. Change auto correct can be useful but only when you intend to use it. I keep it off and only turn it on when I actually want it to "help" me. Experts sometimes refer to this "feature" as Name Auto Corrupt because of the mess it can make when you don't actually understand how it propagates changes and most importantly WHEN.

For code, I comment it out with a date. Again, after a few weeks or so, I delete the commented out code. If I actually want to save it because I think it will be useful later, I have a separate database where I keep a table of code procedures. Only novices think that a line of code written is a line of code that must be preserved. Far better to clean up as you go and avoid confusing yourself or others later.

A reflex I have developed is to save and compile BEFORE I test new code. This prevents loss should I have made a mistake that sends Access into a tizzy and forces me to shut it down.

Another is to close the database at least once an hour during heavy development periods and zip the file before returning. It is also good to do this at logical breakpoints were you added something new, tested it and it works so you want to move on.

Looking at the link Microsoft Access Maximum limits.... I see one limit I did cross.

Number of modules (including forms and reports that have the HasModule property set to True): 1,000 modules

I just re-calculated the number of objects in a database before my clean up and I come to 516 forms, 350 reports and 250 modules. All my Forms and reports have a module so that's were the problem probably is.

That might explain why the problems started after adding code to a module and also that the problems are gone for over two weeks since my clean up.

Thanks for the feedback.

 

[dussel1000]

I also did some testing with the Isladogs TestCreateModules_BLANK database and I already run into errors after adding 702 modules...

 

[Pat Hartman]

I included the links to Colin's website because he has done extensive testing and seems to have found different limits.

It is also possible that one or more of the forms had corruption and deleting them resolved the issues.

Again, make sure to keep the app "clean" and compiled and you are less likely to run into corruption issues.

 

[isladogs]

As my article Access Specification issues (isladogs.co.uk) makes clear, the specified limit for code modules is far higher than 1000 in practice.
The limit may depend on both Access version and bitness as well as other factors.
Having said that my largest production database was developed in A2003 and A2010 32-bit.

However performance declines significantly before the actual limit is reached.
What errors did you observe?