Strange Things happen in VBA Editor

[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

 

[CJ_London]

Probably already suggested but have you done a decompile/recompile?

 

[HappyCoding]

@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


@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

Nice testing...
The total of modules, including forms and reports which have modules, does not exceed 800.
I did the same kind of testing with forms, subs and functions in the past. I did not continue in that direction cause the numbers did not match to the actual project. Another test is still something I would like to try, though. The number of public or/and local variables in the project.
The declaration of a new variable inside a sub or function can lead to this problem in this project.... something reaches it limit and can be lowered by deleting other object in the project...

As to vbWatchdog, I don't think it is causing any issues. Like Josef P. stated, it must be initialized to become active and by just opening the project it will not be initialized.

 

[HappyCoding]

Probably already suggested but have you done a decompile/recompile?

As stated in multiple comments, Decompile & Repair does not solve the problem. Unless you mean something else with decompile/recompile.

 

[The_Doc_Man]

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.

OK, this tells me something, but I need to ask questions, perhaps pedantically, in order to see if I understand this...

You have an Ac2007 project with lots of modules. You have a way to compile them and they DO successfully compile without syntax errors. At some point, however, you make a change and can no longer compile. Importing the project to a new, blank project doesn't help. Converting the project to Ac2010 doesn't help. You can eventually re-add the deleted items, recompile, and things work again.

Question 1: Is it always stopped with a memory error? Frequently? Rarely? What class of errors usually stop you? (Not interested in exact statistics, just a general impression.)

Question 2: When you start to make your changes, do you do a Compact & Repair (C&R) first? Or perhaps a decompile & recompile?

Question 3: The error occurs. You delete one or more objects such as forms or reports, at which point the compilation can succeed. But then you say you can re-add items and it is possible to proceed without the error. Is there one particular form or report that is your "go to" target for recycling? I.e. have you found that you can get the best traction on recovery if you whack a particular form?

Question 4: How often do you do a Compact & Repair as a matter of principle?

Question 5: You say that an import to a blank DB file doesn't help. But does that mean "the problem eventually recurs" or "the problem immediately recurs?"

Question 6: When you get this compile error, how many times have you added/changed something since the last total re-import or C&R? I.e. how quickly does the problem manifest itself?

Question 7: You said the file is about 55 MB when you finish a C&R. But how big has it become when the compilation next fails?

Question 8: When the app compiles and you run it, have you looked at its size using Windows Task Manager (WTM), Process tab? If you have an app form waiting for input and you don't give it anything, app size should be stable so the memory used by the app should ALSO be stable. With the 32 GB of RAM that you mentioned earlier, you should be able to run WTM and your app simultaneously. If so, you should be able to flip to the Process tab of WTM to see the run-time memory consumed by Access AND you should be able to confirm from the Performance tab, Memory focus, that you have no data structures below 5% free-space available. (And I'm not thinking that you SHOULD be at a depletion point - but if you are going to check process memory, you have WTM open so can check memory stats too. Basically a cheap check if you go to the trouble of the Process run-time size check anyway.)

Here is my working theory: In a split database, the working space is in the FE file, embodied in Workspace(0).Database(0) and whatever is in Database(0). This includes all Tabledefs (some of which will be linked to a BE file; system tables will be local), all QueryDefs, all Forms, all Reports, all Macros, and the text of all Modules. It ALSO includes data for any local tables, but I will discount that because I'm guessing you know not to do too much of that. But there are other things in the file, too.

For instance, you have the list of references. BUT ... in the run-time image of the file, you call every referenced library to fit into the FE's virtual memory. If you don't do a decompile, then the file that is your FE contains compiled code. If you have not done a C&R, it can contain multiple copies - now "dangling" - of the compiled code because of the way Access garbage-collects. You have said that you have a large number of modules, and I noted it was enough that you became slightly defensive about it being under the Access limit. Which means it bothered you that you had so many. (And I'm not saying you don't need each and every one of them - but the title of your question betrays your own suspicions.) Then, there is the process stack and heap data areas where there is working space for nested subroutine calls, dynamic array and object creation, internal file handle allocation, and string manipulation. Those are not trivial spaces.

ALL of these factors contribute to the virtual size of the FE when it is running. When it exits, the dynamic structures (stack, heap, .DLL files) vanish, so your FE file doesn't LOOK like it reached 2 GB - but WTM would tell you how close you really are at run-time. Since you are developing, you have the ability - whether you use it or not - to switch to activating the app. That means that the dynamic structures such as stack and heap have to be there. And it doesn't matter HOW much physical memory you have. When you hit the process addressing limit, you are done. That is what I think is going on.

I would pay attention to file size after each compilation. I suspect you will be shocked at how big it becomes with each recompile. If I am right then your problem is that you have too much garbage accumulation and will have to be a bit more scrupulous in running the garbage compactor on your project.

 

[HappyCoding]

OK, this tells me something, but I need to ask questions, perhaps pedantically, in order to see if I understand this...

Thanks for staying with me on this problem and think things over.
I will try to answer all the questions and fill in the blanks

You have an Ac2007 project with lots of modules. You have a way to compile them and they DO successfully compile without syntax errors. At some point, however, you make a change and can no longer compile. Importing the project to a new, blank project doesn't help. Converting the project to Ac2010 doesn't help. You can eventually re-add the deleted items, recompile, and things work again.

The deleted object are not returned to the project, they are obsolete and will not be used again. So it seems that deleting some objects will provide some space for new objects. After further development, new forms or reports, the problem appears again.

Question 1: Is it always stopped with a memory error? Frequently? Rarely? What class of errors usually stop you? (Not interested in exact statistics, just a general impression.)

At runtime there are no errors. The problems occur only during development. "Not enough memory" is the error that causes the problem. At that moment, every change to the project gives error. Loading a form from text or importing directly from another accdb is not possible until other objects are deleted.

Question 2: When you start to make your changes, do you do a Compact & Repair (C&R) first? Or perhaps a decompile & recompile?

Normally I use both options if the problem arises. Because I expect the problem to arise any day, I frequently Decompile, Compact, Repair and Compile the project..

Question 3: The error occurs. You delete one or more objects such as forms or reports, at which point the compilation can succeed. But then you say you can re-add items and it is possible to proceed without the error. Is there one particular form or report that is your "go to" target for recycling? I.e. have you found that you can get the best traction on recovery if you whack a particular form?

I did not add the deleted objects back to the project. I meant that I create new object in the project and can compile without any problems.

Question 4: How often do you do a Compact & Repair as a matter of principle?

Almost every day, after changes...

Question 5: You say that an import to a blank DB file doesn't help. But does that mean "the problem eventually recurs" or "the problem immediately recurs?"

The problem immediately occurs, it seems the problem is imported as well.

Question 6: When you get this compile error, how many times have you added/changed something since the last total re-import or C&R? I.e. how quickly does the problem manifest itself?

Can't say, I do not use the newly accdb with then imported objects because it is, in my opinion, already unstable.

Question 7: You said the file is about 55 MB when you finish a C&R. But how big has it become when the compilation next fails?

I always start with a fresh copy of the project to develop, size at 55MB. During development the size goes up to 120 MB. after compression it is back to 55 MB.

Question 8: When the app compiles and you run it, have you looked at its size using Windows Task Manager (WTM), Process tab? If you have an app form waiting for input and you don't give it anything, app size should be stable so the memory used by the app should ALSO be stable. With the 32 GB of RAM that you mentioned earlier, you should be able to run WTM and your app simultaneously. If so, you should be able to flip to the Process tab of WTM to see the run-time memory consumed by Access AND you should be able to confirm from the Performance tab, Memory focus, that you have no data structures below 5% free-space available. (And I'm not thinking that you SHOULD be at a depletion point - but if you are going to check process memory, you have WTM open so can check memory stats too. Basically a cheap check if you go to the trouble of the Process run-time size check anyway.)

Memory usage is stable at 108 MB

Here is my working theory: In a split database, the working space is in the FE file, embodied in Workspace(0).Database(0) and whatever is in Database(0). This includes all Tabledefs (some of which will be linked to a BE file; system tables will be local), all QueryDefs, all Forms, all Reports, all Macros, and the text of all Modules. It ALSO includes data for any local tables, but I will discount that because I'm guessing you know not to do too much of that. But there are other things in the file, too.

The accdb has all those object, there all some local tables for default data. Most tables are linked to SQL Server. During development it makes no difference if the Linked tabledef are really linked or not. To run the project the tables should be linked, but changing a statement in a module or class it is not really necessary.

For instance, you have the list of references. BUT ... in the run-time image of the file, you call every referenced library to fit into the FE's virtual memory. If you don't do a decompile, then the file that is your FE contains compiled code. If you have not done a C&R, it can contain multiple copies - now "dangling" - of the compiled code because of the way Access garbage-collects. You have said that you have a large number of modules, and I noted it was enough that you became slightly defensive about it being under the Access limit. Which means it bothered you that you had so many. (And I'm not saying you don't need each and every one of them - but the title of your question betrays your own suspicions.) Then, there is the process stack and heap data areas where there is working space for nested subroutine calls, dynamic array and object creation, internal file handle allocation, and string manipulation. Those are not trivial spaces.

What I meant to express, was that I was wondering about the number of modules I have and the limits of Access, being far off. As stated by other members, the number of modules are not reaching the limits by far... so it is bothering me that there are messages thrown for "not enough memory".
The messages of Access are not always very descriptive, or even not to the point. So I am really in the dark as to search for an explanation of the problem.
You are talking about stack, heap data areas and other kind of spaces; is there an option or action to show these spaces? Check if these spaces are exhausted?

ALL of these factors contribute to the virtual size of the FE when it is running. When it exits, the dynamic structures (stack, heap, .DLL files) vanish, so your FE file doesn't LOOK like it reached 2 GB - but WTM would tell you how close you really are at run-time. Since you are developing, you have the ability - whether you use it or not - to switch to activating the app. That means that the dynamic structures such as stack and heap have to be there. And it doesn't matter HOW much physical memory you have. When you hit the process addressing limit, you are done. That is what I think is going on.

Where can I find the size of these dynamic structures during runtime. In the WTM and the resource manager, they are not shown.

I would pay attention to file size after each compilation. I suspect you will be shocked at how big it becomes with each recompile. If I am right then your problem is that you have too much garbage accumulation and will have to be a bit more scrupulous in running the garbage compactor on your project.

You mean by this, that before compiling a project, it should be decompiled and compressed?

 

[isladogs]

Suggest you check for possible limits in Access specifications but remember not all are accurate
Specifically:
1. I don't believe there is any limit on the number of variables
2. Databases can contain up to 32768 objects. I believe that is a hard limit as its the integer limit....but I've never had a need to test that!

In terms of running out of resources, you can try increasing the MaxLocksPerFile & MaxBufferSize settings
You can also try using my available connections 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.

isladogs.co.uk

 

[The_Doc_Man]

Most tables are linked to SQL Server.

I don't recall seeing that fact before in the thread, but it is significant because one of the typical sources of "bloat" just vanished if you are using pass-thru queries a lot. If your queries are NOT pass-thru then my opinion didn't change because then the table and query manipulation detritus is in the FE workspace if the work is done locally.

"Not enough memory" is the error that causes the problem. At that moment, every change to the project gives error. Loading a form from text or importing directly from another accdb is not possible until other objects are deleted.

Yes, once you reach a "not enough memory" you have the space problem until you make more space. So that makes sense. And it points to the problem being a memory usage overload for the distinct internal structures. But surely that statement is incomplete. Does the problem occur after the objects are deleted, or does it occur after the objects are deleted AND a C&R and/or Decompile occurs?

Where can I find the size of these dynamic structures during runtime. In the WTM and the resource manager, they are not shown.

Unfortunately, I don't know how to examine the run-time memory allocation of inner structures like stack, heap, and the allocation of address space to .DLL files because that would require (a) that Microsoft publish the Access code and (b) that we could find a highly privileged program that can dynamically remap itself to look at the memory fragments of another program. Theoretically possible and I could do it in OpenVMS but not in Windows. I'm not saying such tools don't exist but I don't know of any.

It is clear that you are blowing out a memory-related resource but it is hard to know which one. Here's a last-gasp try with the tools that you would easily be able to use. When this happens, IMMEDIATELY note the time/date that is usually in the task bar, lower right. Then open the Event Viewer (Start >> Windows Administrative Tools >> Event Viewer) and look in the System or Application logs to see if it left a track with a specific error message or error code. The events are time-stamped and ordered chronologically so it should be easy to scroll to the right time and search for an Access error. The clock in the task bar and the clock used by Event Logging are the same clock so they WILL be synchronized. If there is no error from Access at that time, then I don't know any easy way to get inside its little head.

 

[The_Doc_Man]

There is a list of special utilities from this link:

Process Explorer - Sysinternals

Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more.

learn.microsoft.com

Since it is a Microsoft link, it is legit to use and might be helpful. Got NO idea as to costs. You can use the left-hand side of the linked page to explore other tools that might or might not help. I would have to download some stuff and play with it before I could advise you more deeply, but perhaps someone else on the forum has experience with these tools and could advise. I can't commit to that download because I've got some other things going on at the home front and my time is limited. I think for this there would be a learning curve.

You mean by this, that before compiling a project, it should be decompiled and compressed?

I would suggest it.

 

[isladogs]

SysInternals is both free and very useful. I've only used a small proportion of the tools but all are highly regarded

 

[Josef P.]

I can still imagine a defect in the VBA project, but I find the behavior of example 1 the strangest.

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

Access VBE with GitHub CoPilot?

 

[sonic8]

But compiling throws Compile Errors in some random other Modules or Forms, that without this change would compile without problems.

What kind of compile errors are in the other forms?

As stated in multiple comments, Decompile & Repair does not solve the problem. Unless you mean something else with decompile/recompile.

I described the decompile operation here: The /Decompile Switch for Microsoft Access

 

[sonic8]

There is a list of special utilities from this link:

Process Explorer - Sysinternals

Find out what files, registry keys and other objects processes have open, which DLLs they have loaded, and more.

learn.microsoft.com

For diagnosing memory issues VMMap (also by Sysinternals) might be the better suited tool.

 

[HappyCoding]

I can still imagine a defect in the VBA project, but I find the behavior of example 1 the strangest.

Access VBE with GitHub CoPilot?

Is that a suggestion to use CoPilot in VBA?
The sample is just a sample to illustrate the behavior.

 

[HappyCoding]

What kind of compile errors are in the other forms?

Before the problems starts, there are no errors whatsoever.
When the compilation fails, the message is "not enough memory" or Access shows a message that a normal Access statement like "Left$(...) or Trim$(...)" is not valid..

I described the decompile operation here: The /Decompile Switch for Microsoft Access

That is the decompile I am using.

 

[Josef P.]

Is that a suggestion to use CoPilot in VBA?

Nope. CoPilot does not run in the VBE. Was just a description of the behavior in the editor.

The sample is just a sample to illustrate the behavior.

I understand it like this: after entering the code line (content doesn't matter) this code line changes.
And that is very strange.

@sonic8:
If I understood the OP correctly, he also already performed SaveAsText and LoadFromText to a new database file. (This is even more effective than /decompile in my opinion).

How 'to bother' Access/VBA?
* Changes in interfaces (used in Implements ...)
* Interface changes at referenced libraries without changing the COM-GUID or changes in an accde reference
... /decompile or reset reference usually helps here.

Access shows a message that a normal Access statement like "Left$(...) or Trim$(...)" is not valid..

I am familiar with this behavior. I think a broken reference can also trigger such a message.
But again, if I have something like this, I've always been able to fix it with /decompile or by updating the reference.

 

[The_Doc_Man]

When the compilation fails, the message is "not enough memory" or Access shows a message that a normal Access statement like "Left$(...) or Trim$(...)" is not valid..

The second type of error (non-memory) occurs because the referenced libraries are processed in order and one of them fails. Depending on which one fails, any reference that would be satisfied by a library appearing later in order than the one that failed would trigger the "not valid" message. I.e. the error occurs for the first function that WOULD have been referenced if another reference earlier in the list had not failed. The function that fails depends heavily on the order of references and there IS a way to change the order in which they appear.

 

[The_Doc_Man]

Still researching... Here is an article that seems relevant.

MS Access: There isn't enough memory to perform this operation

I'm using Access 2003 on a duo-core machine with 4GB of RAM, running Windows XP (Service Pack 3) [5.1.2600] Periodically, I get an error msg "There isn't enough memory to perform this operation. ...

stackoverflow.com

I can say without equivocation that while the specific symptoms (including spontaneous code change) are not that common, the "not enough memory" error has been around since Ac2000 and is often acknowledged to be related to corruption.

 

[HappyCoding]

The second type of error (non-memory) occurs because the referenced libraries are processed in order and one of them fails. Depending on which one fails, any reference that would be satisfied by a library appearing later in order than the one that failed would trigger the "not valid" message. I.e. the error occurs for the first function that WOULD have been referenced if another reference earlier in the list had not failed. The function that fails depends heavily on the order of references and there IS a way to change the order in which they appear.

Will changing the order of references shed any light on the problem? I think that loading of referenced libraries are failing is a result of the first problem, where Access has a problem with its memory.

I played around with process explorer, vmmap, procdump and winDbg.... None of them give any information about reaching any kind of limits.
Creating the right dump file and then interpreting the data is not that easy. So I will give that some more thoughts. There is no crash dump because Access is not crashing at that moment...

 

[The_Doc_Man]

Will changing the order of references shed any light on the problem? I think that loading of referenced libraries are failing is a result of the first problem, where Access has a problem with its memory.

I played around with process explorer, vmmap, procdump and winDbg.... None of them give any information about reaching any kind of limits.
Creating the right dump file and then interpreting the data is not that easy. So I will give that some more thoughts. There is no crash dump because Access is not crashing at that moment...

The error log files have multiple topics. Did you check the application log to see if anything is there? I agree that in the absence of a process crash, the system log probably won't say much.

 

[gemma-the-husky]

occasionally I have had large products start behaving strangely, and I have had to resolve it by importing all the objects into a new database.
I leave forms till last, and import them a few at a time, to avoid errors of the type you mention. I save versions as I go in case a certain form corrupts the whole project again.

It's a pain, as it can take a while, but it has worked for me.