"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?
LVL 1
Declan_BasileITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 FyeOwner, Developing Solutions LLCCommented:
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.