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

ms-access memory ms-access-2003
maxhugen picture maxhugen · Oct 16, 2009 · Viewed 62.3k times · Source

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

David-W-Fenton picture David-W-Fenton · Oct 16, 2009

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.

Related questions

How to do INSERT into a table records extracted from another table

I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want …

Read More
How to SUM two fields within an SQL query

I need to get the total of two fields which are within the same row and input that number in a field at the end of that same row. This is my code. Sum(tbl1.fld1 + tbl1.fld2) AS [Total] …

Read More
Code to loop through all records in MS Access

I need a code to loop through all the records in a table so I can extract some data. In addition to this, is it also possible to loop through filtered records and, again, extract data? Thanks!

Read More