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

Question

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. Close unneeded programs and try the operation again."

A check of Task Manager indicates that there is plenty of free memory. Closing other open programs makes no difference.

This happens sporadically, and under different circumstances: sometimes whilst saving Form design or VBA code changes, sometimes when several Forms are open and in use.

If attempting to save design changes, and this error occurs, the Access objects are corrupted and can't be recovered.

Any suggestions on what might be causing this would be very welcome.

MTIA

Foiled

Answer 1

As I know that it's either forms or reports that most likely get corrupted, I created a new mdb, and only imported tables (attached), queries, scripts (one only), modules and menus. Then I used LoadFromText to import Forms and Reports via a function, and then did the usual decompile/compile and compact/repair etc.

So far, touch wood, I haven't had another crash in some days, so I'll probably stick with this recovery method.

Many thanks to all for your suggestions.

Foiled

FWIW, I haven't had a serious crash for ~4wks since using LoadFromText, so the above-described backup and recovery methods using SaveAsText and LoadFromText appear to be most worthwhile. – Foiled

Answer 2

The VBA project in your front end is likely corrupted. You need to rebuild it from scratch and then use proper Access coding practices:

1. in VBE options, turn off COMPILE ON DEMAND (see Michael Kaplan's article on DECOMPILE for details of why).

2. in VBE options, turn on REQUIRE VARIABLE DECLARATION.

3. in the VBE, customize your toolbar so that the COMPILE button is easily accessible (it's on the Debug menu). I also recommend adding the CALL STACK button (from the VIEW menu), as it's handy for debugging errors in break mode. The point here is to make debugging and compiling as easy as possible.

4. having set up your environment, go through all the modules in your newly recovered project and add OPTION EXPLICIT to the top of every module that lacks it. Then compile. You'll quickly find out where you have invalid code and you'll need to fix it.

5. from now on, when programming, compile frequently, after every two or three lines of code. I probably compile my project 100 or more times a day when coding.

6. periodically decompile your project and compact and recompile it. This will clean out any crud that accumulates during regular development.

These practices insure that the code in a non-corrupt project stays in as clean a condition as possible. It will do nothing to recover an already corrupted project.

In regard to how to rebuild the project, I think I'd go the drastic route of exporting all the objects with Application.SaveAsText and importing them into a new blank database with Application.LoadFromText. This is superior to simply importing from your existing corrupted front end because the import can import corrupt structures that won't survive a SaveAsText/LoadFromText cycle.

I program daily in Access, working with non-trivial apps that use lots of code, including plenty of standalone class modules. I have not lost an object to code corruption in over 5 years, and that was back in the day when I was still using A97.

Lefty

Thanks David. Re 1, what does that do? (not covered in Michael Kaplan's article) 2: always on. 3: I always compile after every code change. 4: always use this. 5: I also compile a lot. 6: I also regularly decompile/recompile. I too work daily with Access, for ~10 years. Haven't lost any code - I too have a lot of VBA modules/classes - but occasionally get a corrupted form or report. But recently I've been getting this 'memory' error. Today I lost most forms, and all reports! Fortunately, I had text backups of all objects from yesterday, and used LoadFromText to recover forms and reports. – Foiled

The article explains why you don't want incremental compiles, because they increase the number of compilation levels that VBA has to keep track of. Explicit compiling is a complete compile. You might want to check the Help file article on COMPILE ON DEMAND (hit F1 while viewing the OPTION | GENERAL tab). – Lefty

Answer 3

Having tripped across this old post of mine, and seeing it's had a fair bit of interest, I thought maybe an update would be in order?

So 2 years down the track, doing a lot of 2007 app work as well as older 2003 (and even '97) apps, I'm finding that 2007 is less prone to really nasty crashes than 2003 - where Access object definitions (forms and reports esp.) would be easily corrupted.

I still follow the suggestions 1-6 (above) by David-W-Fenton religiously though, plus the use of Application.SaveAsText (see Tony Toews' suggestion and link above).

These days, whether it's 97, 2003 or 2007 I'm working on, if Access gives any hint of "being weird | crashing | throwing inexplicable errors" etc, I do the following:

1. Immediately close the Access app
2. Backup the mdb/accdb file
3. Re-open the app whilst holding down [Shift] so nothing runs
4. Export all objects as text using Application.SaveAsText (as another backup)
5. Close and re-open the app using the /decompile switch
6. Recompile the VBA code
7. Do a Compact/Repair.

This doesn't solve everything, but it does significantly reduce the number of corruptions of Access objects from what I'm able to observe.

Foiled

Answer 4

Oh my.

I worked in a shop for many years that used Access as their platform of choice. The application eventually got so large that it began hitting an internal memory limitation of Access 2003. They began experiencing the exact same problem that you are having. As you have noticed, there is no external indication of memory problems when this happens.

The company talked at length with Microsoft about the problem, and I believe Microsoft eventually supplied them with a patch. So you might want to talk to Microsoft about this, if it sounds like a similar situation to what you are experiencing, as they may be able to supply you with the same patch.

Ultimately the long-term solution is to break the application into smaller pieces. Moving to Access 2007 didn't help; in fact, it made things worse because Access 2007 has more moving parts.

Ferri

Thanks Robert. This application is quite small really, ~40 forms & subforms, same for reports, ~11,000 lines of VBA code. As for MS support, I gave up on that years ago: paying them when I found bugs in their apps wasn't my cup of tea :) – Foiled

If you have an MSDN subscription, you have free incidents. In this particular case Microsoft should provide the patch for free. – Ferri

Answer 5

Quick solution; guaranteed to work:

Open VBA (Alt-F11) In the immediate window enter the following:

Application.SaveAsText acForm, "corrupt form name here", CurrentProject.Path & "\zzTempRevive"

then

Application.LoadFromText acForm, "corrupt form name here", CurrentProject.Path & "\zzTempRevive"

That's it :) Hope this helps others!

Subrogation

After executing Application.LoadFromText acForm, "corrupt form name here", CurrentProject.Path & "\zzTempRevive" statement some errors may be found in errors.txt. Fix errors then run statement again. – Swill

Answer 6

This is also the default error message when Access has no idea what the problem actually is. Now if your MDB is particularly large, say more than 800 forms and reports with modules then, yes the MDB could be too large although that gave you a message when you went to create MDEs. ACC2000: "Microsoft Access Was Unable to Create an MDE Database" Error Message

I have had this happen occasionally myself. And my current MDBs aren't quite that large. Note that compact and repair doesn't detect errors in objects other than tables, indexes or relationships. So importing into another MDB is the only way to correct these errors.

Are you working on this MDB over the network? That's about the only thing I can think of that might cause this problem.

Rompers

Hi Tony: the app is small. True, compact/repair is of no use, so I import all objects to a new mdb. The app is a FE/BE sitting on my local drive whilst developing. I've taken to backing up objects via the undocumented function Application.SaveAsText, and then Application.LoadFromText to recover the corrupted objects that could not be imported. It's laborious though, and Access's instability is worrying me, as it's happened to a user occasionally as well. – Foiled

Max, there is a tool at one of the links at Corrupt Objects within a Corrupt Microsoft Access MDB granite.ab.ca/access/corruption/corruptobjects.htm which will do all the objects. I'd certainly review David's suggestions as well although I'm not sure if it's the VBA which is corrupt. – Rompers

Thanks Tony. I've a function that much the same as Arvin Meyer's. It dumps all objects into a time-stamped folder, as part of my backup strategies, and I use many times each day. I have exported a couple of corrupt forms, and note that the form decription is replaced by ???????? etc, but the VBA appears to be OK. – Foiled

Answer 7

I have encountered this problem many times and finally found a solution that worked. I don't know what causes the problem, but I do know how to solve it.

Usually the error occurs when you open a form. What you need to do is completely re-create that form. The easiest way to do so is to first export the form to a text file with the undocumented function Application.SaveAsText. Then you delete the form from your database and re-load it with Application.LoadFromText.

Hereat