• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 350
  • Last Modified:

"There isn't enough memory" error in Access 2003

I've developed a fairly large Access 2003 database (1370 queries, 184 forms, 129 linked SQL Server + local tables), and some of the forms/queries/reports, etc. are used for quoting jobs.  The people that use this part of the program (and only those people) are getting the following error: "2004 There isn't enough memory to perform this operation.  Close unneeded programs and try the operation again."  
   The parts of the program that have error handling record the error when it happens, but the users get kicked out of the program and the error doesn't get recorded when the error happens and there is no error handling.  Judging both by the errors that get recorded and the user's complaints, the error is happening more and more frequently.  Out of the errors that were recorded, the error happened as follows (1 error in October, 1 in November, 0 in December, 4 in January, 9 in February, 14 in March, 22 in April).  
   This happens to users using the latest Service Pack (SP3) and with users who aren't using the latest Service Pack.  This happens when they're using different parts of the program (i.e. it happens on different forms and when they are doing different things with those forms).  I made a change to the forms and reports that they use back in December after which the problem started to happen more and more often.  The error only occurred twice (that I'm aware of) before I made this change to the program.  I tried saving to text and loading from text all of the forms and reports they use, and I tried importing all objects into a blank database.  How can I prevent the error from continuing to occur?
0
Declan_Basile
Asked:
Declan_Basile
  • 6
  • 3
2 Solutions
 
Gustav BrockCIOCommented:
How is your coding practice? Are you keen to close and set objects to Nothing when finished using them? Missing that could be a source for your trouble.

Can or do the users have many forms open at one time? That could be another source.

/gustav
0
 
Declan_BasileITAuthor Commented:
Yes, my code always closes and set objects to nothing when finished using them.  Yes, the users can and do have multiple forms open at one time, however the users have always kept these same forms opened at the same time for years of using the program, and the problem only started happening back in October and only started happening often in January.  Also, keeping multiple forms open helps them to go between the forms faster.
0
 
Dale FyeCommented:
What version of Windows are you using?

Is this occurring on a set of computers which have limited RAM, or does it randomly occur across multiple computers?

Are you careful to programmatically close and set recordsets and database object to nothing when in your code, or do you depend on the closure of objects and the built-in mechanisms to do that for you?

Have you looked at the computers where this is occuring to determine whether the users typically have lots of applications running at one time, consuming large quantities of RAM.

Do you remember what you changed back in December?  It seems that whatever change you made back then may be at least partially responsible for the increase in this problem.  If you have old backup versions of the application, you could use a tool like FMS Total Access Detective to identify the differences between two versions of an application.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Declan_BasileITAuthor Commented:
I believe this error may be misleading.  There is always plenty of memory available (as reported in Performance Monitor) when this error occurs.  Also, I forgot to specify in my original message that none of the forms have grouped controls  (I read an article from Microsoft that reported a known bug that causes this error when a form with grouped controls is opened and closed multiple times)
0
 
Declan_BasileITAuthor Commented:
<<What version of Windows are you using?>>
The users are all on Windows 7.
0
 
Declan_BasileITAuthor Commented:
I just had something happen that could give a clue to the underlying problem.  I once again began to import the .mdb's objects into a blank database.  I did one object type at a time.  Tables, queries and forms all imported just fine.  Then I selected to display the reports objects in the new database (which was blank because no reports were imported yet) and after I clicked import and selected the old database to display a list of reports available to import I got the same error message "There isn't enough memory ...".  I tried this again and again got the same error.  I suspect that it is a report object that is corrupt because I got the error message when Microsoft Access 2003 tried to access the list of reports in the original database.  The error came up without opening any form or report or running any part of the program, so I don't think that it's caused by the way I wrote the program or by there being too many forms opened at a time.  I think the problem is a corruption problem.  
   I closed Microsoft Access, re-opened the new database and was able to get a list of available reports to import from the old database without getting the error message.  I wish I consistently got the error when retrieving a list of reports so I could be more confident that it's a report that is corrupt.
0
 
Gustav BrockCIOCommented:
Are graphics/images included in any of the reports?

/gustav
0
 
Declan_BasileITAuthor Commented:
No graphics or images are included in any of the reports.   I found a report that was corrupt.  Before I imported it into the new database, I was able to open the most complex form in the database and it worked just fine.  But after I imported this report into the new database, the form either wouldn't open or would open but wouldn't work correctly.  Just having that corrupt report in the database caused a form not to work correctly.  So I saved the form to text while it was still in the original database, loaded it back from text to the original database, then imported it into the new database after which I was able to open the form and it worked fine.
Now the users are not getting the error message as often, but they still get it.  I think there is still one or more objects that are corrupt, only this time it's not as easy to determine what else might be corrupt.  Is there a program that I could run on the database that would point out any corruption that it finds?
0
 
Gustav BrockCIOCommented:
I don't believe so.

If the saveastext file doesn't contain a clue, I would recreate the report totally from scratch. Then you have a firm starting point.

One old source of error was lines. The trick was to replace them with rectangles of height zero. I'm not aware if this bug still exists, but the additional work is so little that - in this case - it would be worth considering.

/gustav
0
 
Declan_BasileITAuthor Commented:
The problem was a corruption problem.  I imported all of the reports from a version of the program before people started getting this error and re-wrote all the changes since then.  It seems to have fixed the problem.  I didn't need to use "Total Access Detective" because I was able to remember the changes I made, but thanks for telling me this program exists.  I may use it sometime in the future.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now