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

Answer 1

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.

Answer 2

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.

Answer 3

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.