Strange Things happen in VBA Editor (1 Viewer)

[HappyCoding]

As the Title suggests, I am experiencing strange things in the VBA Editor of Access 2007.
I am looking for an explanation and of course a solution to the problems.

Example 1
If I type something like this, in code that would normally compile just fine
Dim aString As String

After enter, it will be changed to this
Dim me() as String

Which of course does not compile anymore.
Sometimes "decompile and Compress" will solve the problem and new declarations are possible.

Example 2
Typing multiple Lines in a Sub or Function of code gives no problems
But compiling throws Compile Errors in some random other Modules or Forms, that without this change would compile without problems.
Sometimes the message is just: - not enough memory

Decompiling and compressing will not solve the Problem

Solution to both examples
Deleting a different Form, sometimes one, sometimes more, solves the Problem.
The code that previously could not be compiled or would be changed by Access, will, without any changes, compile without problems

Presuming that if compiling completes normal, there would not be any Syntax Errors.

It is a large Project with many Forms, Reports, Classes and Modules, but the known limits to Access are not reached yet; Object count, module sizes etc.
Are there any other Limits in Access I can check?
It seems like there is a limit on the number of total Variables in a Project.
Or maybe a limit on String Space.

I tried importing the project in Access 2010, same Problems.
Importing all objects in a new Project does not solve the problems.
The Project should stay at Access 2007, so upgrading to Access 2016 or 2019 is not an Option

Any suggestions are welcome.
Thanks

 

[The_Doc_Man]

When you get a "Not enough memory" error, there are a few things to check.

How much RAM do you have on your physical machine? If 4 Gb or less, Windows is hoggish and might not leave a lot for apps.

For Win10, use Start >> Settings >> System >> About >> Advanced and browse around for your virtual memory setting. Memory errors can be caused by not having enough virtual memory enabled.

Limits on project space CAN exist, but perform a manual Compact & Repair on the front-end and any related back-ends. Be sure to make backup copies of each file beforehand. Then check the sizes of each involved file. If any are over about 1.5 GB, then the amount of free space used during normal operations might approach the hardware memory limit.

Another memory related situation can occur if you reach a "heap" limit, though I might expect the "Stack overlaps Heap" message (or "Heap overlaps Stack"... which one depends on the operation that failed.) Do you have any known cases of program recursion in your modules?

 

[MajP]

That sounds pretty bizarre and sound more like an issue with the Access application not the database. Do other databases work? Do you have the same issue with this project on a different installation? Have you tried reinstalling Access?

 

[HappyCoding]

When you get a "Not enough memory" error, there are a few things to check.

How much RAM do you have on your physical machine? If 4 Gb or less, Windows is hoggish and might not leave a lot for apps.

For Win10, use Start >> Settings >> System >> About >> Advanced and browse around for your virtual memory setting. Memory errors can be caused by not having enough virtual memory enabled.

Limits on project space CAN exist, but perform a manual Compact & Repair on the front-end and any related back-ends. Be sure to make backup copies of each file beforehand. Then check the sizes of each involved file. If any are over about 1.5 GB, then the amount of free space used during normal operations might approach the hardware memory limit.

Another memory related situation can occur if you reach a "heap" limit, though I might expect the "Stack overlaps Heap" message (or "Heap overlaps Stack"... which one depends on the operation that failed.) Do you have any known cases of program recursion in your modules?

Thanks for the quick response.
The Message "not enough memory" does not apply to the computer itself.
The problem exists on win7, win10 and win11. Today, with 32 GB memory and enough free space on disk.
Compacted, the size of the accdb file is 55 MB.

Recursion is not a problem, the code runs fine once it is compiled, no runtime errors occur.

 

[HappyCoding]

That sounds pretty bizarre and sound more like an issue with the Access application not the database. Do other databases work? Do you have the same issue with this project on a different installation? Have you tried reinstalling Access?

Bizarre it is...
I use two different development Systems, one still at Win 7 the other at Win 11, both have the save problem with this accdb project.
Other projects run fine.

New development in this accdb does not give any issues when several Forms have been deleted. Eventually the problem arises again when too many changes are made. If I delete other, obsolete forms, the problem is gone, till some other day...

 

[cheekybuddha]

Do you have a timer running on any open form when you are working in this module?

 

[The_Doc_Man]

Thanks for the quick response.
The Message "not enough memory" does not apply to the computer itself.
The problem exists on win7, win10 and win11. Today, with 32 GB memory and enough free space on disk.
Compacted, the size of the accdb file is 55 MB.

Recursion is not a problem, the code runs fine once it is compiled, no runtime errors occur.

OK... 3 versions of Windows, plenty of physical memory (enough that you are unlikely to do significant virtual paging), and thus disk-based virtual workspace is unlikely to be an issue, either. Small DB (relatively). You said Access 2007, but is it the same version for all three Windows versions?

This sounds like you are running into a dynamic memory limit. Are you opening A LOT of files - like in the thousands? Or perhaps are you allocating and resizing large arrays? How many libraries do you have checked in the VBA >> Tools >> References list? Are any of the libraries unusually large? I'm trying (and so far, failing) to figure out the color of memory that is being overloaded.

It seems like there is a limit on the number of total Variables in a Project.

Yes and no. A single module has a size limit. Here are two articles that discuss those limits.

Redirecting

Too many Modules Spoil the VBA Project? – Excel Do, Dynamic Does

Modules, because they occupy the FE space and share it with all referenced libraries and Access itself, can drain the space that can remain for use in the FE memory area, which cannot exceed 2 GB. That space ALSO has to accommodate the program stack, which gets hinky when you have too many levels of subroutine calls, and the heap, which is a work area where string operations and dynamic array resizing occur. For Access, the BE is limited to 2 GB and the FE gets the other 2 GB of the maximum address range of 4 GB. Switching to 64-bit Access doesn't change the internal limits because apparently, MS didn't change internal pointers within Access, so those huge address ranges that work for Excel don't work for Access.

 

[Josef P.]

Example 1
If I type something like this, in code that would normally compile just fine
Dim aString As String

After enter, it will be changed to this
Dim me() as String

That is very strange. Are VBE add-ins running in the background?

Example 2 could be a broken VBA project. But this does not explain the behavior of example 1.

Importing all objects in a new Project does not solve the problems.

How did you export/import the objects? Did you use SaveAsText and LoadFromText?

 

[HappyCoding]

Do you have a timer running on any open form when you are working in this module?

No timers active, no open forms.
When this problem occurs, I close the project and restart Access in decompile mode, Then Compact & Repair the accdb file.
Then I restart Access, open the database and try to compile after I delete one or more older forms, until the compilation is successful.
After deleting any forms, I start the process of compacting and repairing again....Eventually it all works again. No changes to Syntax, just deleting other forms or/and reports.

 

[HappyCoding]

That is very strange. Are VBE add-ins running in the background?

Example 2 could be a broken VBA project. But this does not explain the behavior of example 1.

How did you export/import the objects? Did you use SaveAsText and LoadFromText?

Add-in vbWatchdog is installed.
Import tried through saveastext/loadastext and direct Access import....

 

[HappyCoding]

Yes and no. A single module has a size limit. Here are two articles that discuss those limits.

Modules, because they occupy the FE space and share it with all referenced libraries and Access itself, can drain the space that can remain for use in the FE memory area, which cannot exceed 2 GB. That space ALSO has to accommodate the program stack, which gets hinky when you have too many levels of subroutine calls, and the heap, which is a work area where string operations and dynamic array resizing occur. For Access, the BE is limited to 2 GB and the FE gets the other 2 GB of the maximum address range of 4 GB. Switching to 64-bit Access doesn't change the internal limits because apparently, MS didn't change internal pointers within Access, so those huge address ranges that work for Excel don't work for Access.

No module exceeds the size limit. If this problem occurs, even the adding of a new form with only an onLoad method will fail the compilation.

 

[Pat Hartman]

If you have Name AutoCorrect enabled, uncheck all three boxes. Go through your C&R and decompile steps one more time. It feels like corruption and Name AutoCorrect is one cause of corruption that you can control.

Always leave it off unless you need to turn it on to use the cross reference function. Then remember to turn it off again.

 

[The_Doc_Man]

No module exceeds the size limit. If this problem occurs, even the adding of a new form with only an onLoad method will fail the compilation.

It is not that A module exceeds the size limit, but that some number of modules combined can exceed the limit.

 

[The_Doc_Man]

Having examined a web description of vbWatchdog, I see that it involves adding a class module that has the ability to examine the call stack. I think that means it is adding to the load on the FE. I am not claiming any fault or bug or error with vbWatchdog, but it seems to me that it has to be in the FE in order to be able to see the stack contents (as it claims). The fact that you have to remove forms that have modules (OnLoad...) to get a compilation makes me wonder what is going on.

If you can do without vbWatchdog (perhaps merely as an experiment), remove it and try to compile your project without it. If you are reluctant, then at least keep the idea on the back burner as a "desperation" try. Again, I don't think vbWatchdog has a bug - but it DOES represent a potential memory load, and you DID mention a memory-related error.

 

[HappyCoding]

If you have Name AutoCorrect enabled, uncheck all three boxes. Go through your C&R and decompile steps one more time. It feels like corruption and Name AutoCorrect is one cause of corruption that you can control.

Always leave it off unless you need to turn it on to use the cross reference function. Then remember to turn it off again.

Name Autocorrect was never enabled in this project

 

[HappyCoding]

It is not that A module exceeds the size limit, but that some number of modules combined can exceed the limit.

What does that mean, "some number of modules"? There are lots of modules and classes in this project, together they definitely have more than 64K characters in then. Are there limits to some types of modules?

 

[HappyCoding]

Having examined a web description of vbWatchdog, I see that it involves adding a class module that has the ability to examine the call stack. I think that means it is adding to the load on the FE. I am not claiming any fault or bug or error with vbWatchdog, but it seems to me that it has to be in the FE in order to be able to see the stack contents (as it claims). The fact that you have to remove forms that have modules (OnLoad...) to get a compilation makes me wonder what is going on.

If you can do without vbWatchdog (perhaps merely as an experiment), remove it and try to compile your project without it. If you are reluctant, then at least keep the idea on the back burner as a "desperation" try. Again, I don't think vbWatchdog has a bug - but it DOES represent a potential memory load, and you DID mention a memory-related error.

Thanks, I will keep that in mind and as a last resort try to eliminate vbWatchdog. I searched the internet for this problem and never found any relation to vbWartchdog. I would presume that more people would have complained about this add-in.

 

[Josef P.]

Are there limits to some types of modules?

Only the number of max. 1000 code modules (incl. form classes).

Access specifications - Microsoft Support

Find limits and specifications for Access 2016 database files and objects, such as the maximum file size or the maximum number of fields in a table.

support.microsoft.com

I also use vbWatchdog. That has to be activated to hang itself in the error handling. So I don't think it will be a problem at design time.
You also have the problems when you open the application and edit a code without having started something in the application first, right?

 

[HappyCoding]

Only the number of max. 1000 code modules (incl. form classes).

I also use vbWatchdog. That has to be activated to hang itself in the error handling. So I don't think it will be a problem at design time.
You also have the problems when you open the application and edit a code without having started something in the application first, right?

Right, when the problem occurs, simply opening the project, compiling is not possible until some other object (form or reports) is deleted.
The Syntax of the deleted forms and the new code is/was correct. The project compiled without the new code and compiles without the old form.

The number of Modules is just over 800.

 

[isladogs]

@Josef P
I recently did some tests on various limits given in Access specifications including the maximum number of code modules.
In 32-bit Access 365, I was able to add a total of 5450 modules before hitting the limit. YMMV.
So I think it should be treated as a sensible guideline rather than a hard limit

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

@HappyCoding
Does your total include all code modules including those in forms and reports? All of those need to be included
I would be very surprised if a correctly installed copy of vbWatchdog was causing any of your issues