Solved Memory issues (1 Viewer)

dussel1000

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

1700061882290.png


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

Well-known member
Local time
Today, 11:16
Joined
Apr 9, 2015
Messages
4,338
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

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

Active member
Local time
Today, 08:16
Joined
Aug 18, 2021
Messages
638
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

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

George Hepworth
Local time
Today, 08:16
Joined
Nov 25, 2004
Messages
2,093
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

Immoderate Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 28, 2001
Messages
27,529
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

Well-known member
Local time
Today, 11:16
Joined
Apr 9, 2015
Messages
4,338
When db is close to 2 gig, it will throw random fake errors.
 

dussel1000

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

Immoderate Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 28, 2001
Messages
27,529
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

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

Immoderate Moderator
Staff member
Local time
Today, 10:16
Joined
Feb 28, 2001
Messages
27,529
Makes perfect sense. Thanks for reporting back with the solution! And good luck going forward with your project.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2002
Messages
43,785
Can you tell us how many objects the database contained and how many you deleted?
 

dussel1000

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
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

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2002
Messages
43,785
Thanks. That's a pretty big app but doesn't exceed any limits.


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

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
Thanks. That's a pretty big app but doesn't exceed any limits.


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

New member
Local time
Today, 08:16
Joined
Feb 4, 2013
Messages
16
I also did some testing with the Isladogs TestCreateModules_BLANK database and I already run into errors after adding 702 modules...
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:16
Joined
Feb 19, 2002
Messages
43,785
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

MVP / VIP
Local time
Today, 16:16
Joined
Jan 14, 2017
Messages
18,301
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?
 

Users who are viewing this thread

Top Bottom