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

 

[JHB]

Have you tried the database on another computer?

 

[HappyCoding]

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.

Little steps did not solve the problem. At first it looked like everything was ok, but other unpredictable messages occur, until I was back at the known Problem....
I will probably have another go at single Import through LoadFromText....

 

[HappyCoding]

Have you tried the database on another computer?

The Problem occurs on two different Systems, different OS, and different Access Version. I even tried an Import on Access 2019. No Luck.

 

[JHB]

With two different systems, do you mean two different computers?

 

[HappyCoding]

With two different systems, do you mean two different computers?

Two different computers, and a virtual machine, all give the same problems with this project.

 

[ebs17]

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.

Have you looked at what takes place in the code, beyond numbers of modules?

- Long codes (more than 40 lines) in single procedures?
- Repetitions of code sequences instead of refactoring?
- Lifetime of variables used?
- Number of objects open at the same time? Closing objects as fast as possible.
- Use of arrays: These reserve memory immediately. With a single array of suitably large dimensions, you can completely occupy the main memory and crash the application.
- Can standard solutions be outsourced to external libraries?

 

[HappyCoding]

Have you looked at what takes place in the code, beyond numbers of modules?

- Long codes (more than 40 lines) in single procedures?

There are lots of subs and functions. I do not consider subs with more than 40 lines as "long code". There are longer subs, with 150 or more lines. I don't think this will influence the compiler. Or would it?

- Repetitions of code sequences instead of refactoring?

The project is very old. Subs and functions are refactored/reorganized when they are changed in some way.

- Lifetime of variables used?

There are lots of global variables, that's why I wondered if there is a limit. Local variables are defined and will be cleared if they get out of focus.

- Number of objects open at the same time? Closing objects as fast as possible.

There are no problems at runtime, when the project is compiled, so I don't think the number of objects open at the same time, does really matter at compile time.

- Use of arrays: These reserve memory immediately. With a single array of suitably large dimensions, you can completely occupy the main memory and crash the application.

Most arrays are dynamic and will be assigned by used by split and join statements. There are no large arrays at compile time.

- Can standard solutions be outsourced to external libraries?

The project is a front-end and lots of modules and classes could be in an external linked accdb. Normally that would not be a necessity for a project of this size... An indication why the project reaches Access limits would be nice....

 

[JHB]

Sure it is a pain and time consuming, but …!

When I have had a problem, that I not could solve with Compact & Repair or import into a new database, or try the database on another computer.

I’ve created a brand new database, opened all forms and reports one at a time in the old database, marked all controls in the form/ report and copied it into the form/ report in the new database and of cause all the code in the form’s/ report’s module.

Yes I agree, it is very time consuming, does it help?
Yes for me it did, actually several times!

After creating two – three forms/ report, do a compile; every thing okay, create two – three more forms/ report, do a compile and so one.
And as other mention, save different version as the work progresses.

 

[dussel1000]

Did you already solved this issue in one way or the other? I have exact the same issue going on in my database.