none
MS Access 2007 - There isn't enough memory to perform this operation. Close unneeded programs and try the operation again.

    Question

  • Few weeks ago my Access database started giving me this error when opening certain forms 'There isn't enough memory to perform this operation. Close unneeded programs and try the operation again'.  No changes have been made to those forms for months.

    The error is happening only on Windows 7 machines but NOT on any XP machines.  I know it's not a code/database issue because the same problem is happening in a backup db (taken from months ago at which point the error was not happening).

    To recreate the problem I simply have to open a form (controls are linked directly to table fields), close it and re-open it with a different product id (maps to a table key).

    I tried exporting the forms as text and reimporting, tried compact & repair, tried increasing MaxLocksPerFile registry key, tried running access in compatibility mode, tried recompiling the database even tried reinstalling office. Nothing seems to fix the problem.

    THe problem seems to have happend as if overnight, with no changes in the code/forms/etc...  One day it worked, next day it didn't.

    Could this be an update issue? I'm out of ideas of what to try.


    • Edited by srenert Tuesday, March 27, 2012 7:16 PM
    Tuesday, March 27, 2012 7:14 PM

Answers

  • I've had this issue too but mainly after opening many forms with many subforms or somehow getting access to use around 100 MB of memory.

    Could you check in the Task Manager the memory usage of your MSAccess process (under the Processes tab). See how that evolves in time and if indeed it reaches a high memory usage. In my tests "high usage" is actually ridiculously low, around 110 MB used is enough to crash Access with this message.

    Let us know what you find.

    • Marked as answer by srenert Friday, March 30, 2012 2:05 PM
    Wednesday, March 28, 2012 10:20 AM

All replies

  • After doing a comapct & repair, save the database and exit Access.  Then try opening the database again.

    jdweng

    Tuesday, March 27, 2012 7:28 PM
  • "I tried exporting the forms as text and reimporting, tried compact & repair, tried increasingMaxLocksPerFile registry key, tried running access in compatibility mode, tried recompiling the database even tried reinstalling office. Nothing seems to fix the problem."
    Tuesday, March 27, 2012 7:59 PM
  • I've been reading that there's a hotfix for Access 2003 SP1/SP3 which supposedly fixes this problem. Is there a hotfix for Access 2007 SP1 which does the same?
    Tuesday, March 27, 2012 8:08 PM
  • Your DB/ or more specificly your Form might have gone corrupt.

    You may try a Decompile of your database: http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

    If that doesnt work try to rebuild the specific Form (or recover from the most recent back-up available).

    Its certainly recommend to upgrade to Office 2007 SP3:

    http://support.microsoft.com/kb/2526086/en-us

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Wednesday, March 28, 2012 3:19 AM
  • I've had this issue too but mainly after opening many forms with many subforms or somehow getting access to use around 100 MB of memory.

    Could you check in the Task Manager the memory usage of your MSAccess process (under the Processes tab). See how that evolves in time and if indeed it reaches a high memory usage. In my tests "high usage" is actually ridiculously low, around 110 MB used is enough to crash Access with this message.

    Let us know what you find.

    • Marked as answer by srenert Friday, March 30, 2012 2:05 PM
    Wednesday, March 28, 2012 10:20 AM
  • Your DB/ or more specificly your Form might have gone corrupt.

    You may try a Decompile of your database: http://www.fmsinc.com/microsoftaccess/performance/decompile.asp

    If that doesnt work try to rebuild the specific Form (or recover from the most recent back-up available).

    Its certainly recommend to upgrade to Office 2007 SP3:

    http://support.microsoft.com/kb/2526086/en-us

    Hope this helps,


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.

    Daniel,

    I can try to decompile (if it's different than just compiling), but I have doubts that it will be the solution.  The problem didn't start happening after a form change or code change or anything of that sort.  The database has been working perfectly on Windows 7, Access 2007 for months without problems.  All of a sudden (as if overnight), every workstation with Windows 7, Access 2007 began exhibiting the same exact symptom.

    I've tried recovering a backup db from a time which I know the problem was not happening, but still the same problem occurs in the backup db.  Which leads me to believe that this isn't a corruption/database issue.

    You mentioned an Office 2007 SP3 upgrade.  We're currently on Windows 7, MS Access 2007 SP1.  I couldn't find in the link you provided if the upgrade you're referring to is for Windows 7 or Win XP?

    Thanks


    • Proposed as answer by Hashim OS Monday, February 04, 2013 10:33 AM
    Wednesday, March 28, 2012 3:09 PM
  • I've had this issue too but mainly after opening many forms with many subforms or somehow getting access to use around 100 MB of memory.

    Could you check in the Task Manager the memory usage of your MSAccess process (under the Processes tab). See how that evolves in time and if indeed it reaches a high memory usage. In my tests "high usage" is actually ridiculously low, around 110 MB used is enough to crash Access with this message.

    Let us know what you find.

    The problem happens almost immediately after opening the database form.  For example open form once with product id 'A' and everything is fine. Close and reopen with any other product id and it gives that error. The form is directly linked to the table via the controls.  Tried recovering the from a backup (from a time which I know everything was working) and still the same problem
    Wednesday, March 28, 2012 3:10 PM
  • I had a similar problem last summer with a very large database 15MB.  I ran the compact and repair and it did not fix the problem.  I then ran the compact and repair and saved the database.   Then shutdown acces and found the file size got reduced to 3MB.  When I opened the database the problem went away.

    I'm wondering if there are any queries that are automatcially running when the workbook is opened.  If not, then I suspect when the form is opened a large amount of data is being generated by some query in the form.  I would disable any queries in the form.  Run a compact and repair on the database.  Save the database and quit Access.  Then reopen the databaes and try the form again.  If you don't get the error then the problem is with the query and there is nothing wrong with the database.


    jdweng

    Wednesday, March 28, 2012 3:17 PM
  • Daniel is most likely correct. If there is any corruption in the Form it should be resolved by decompiling then compile from VBA editor.

    Decompile - Right Click the desktop, Create new shortcut, Paste the following including the quotes

    "C:\Program Files\Microsoft Office\Office14\MSACCESS.EXE" /decompile

    save and close.

    left click drag and drop your icon for the database onto the icon for the decompile.

    If you need to bypass the startup macro then hold down the shift key (after password if you have one)

    after db opens the goto your visual basic and debug/compile/save

    then manually run the compact & repair.

    you can add to your quick access toolbar the visual basic and compact & repair icons for ease.

    alternative way is Start/Run/          msaccess /decompile

    Make sure you get the sp ace character in there as well as ""


    Chris Ward

    Wednesday, March 28, 2012 3:25 PM
  • I had a similar problem last summer with a very large database 15MB.  I ran the compact and repair and it did not fix the problem.  I then ran the compact and repair and saved the database.   Then shutdown acces and found the file size got reduced to 3MB.  When I opened the database the problem went away.

    I'm wondering if there are any queries that are automatcially running when the workbook is opened.  If not, then I suspect when the form is opened a large amount of data is being generated by some query in the form.  I would disable any queries in the form.  Run a compact and repair on the database.  Save the database and quit Access.  Then reopen the databaes and try the form again.  If you don't get the error then the problem is with the query and there is nothing wrong with the database.


    jdweng

    Joel, I've gone through all those steps before up, even re-installed access.  Our database is around 140mb. When i ran the compact and repair earlier, it definitely did what it was supposed to, because the file size was reduced immediately.  So that's not the issue.  As far as the form, there are no queries that run automatically - it's a form linked directly to the table via controls.
    Wednesday, March 28, 2012 3:29 PM
  • After compacting and repair how much did the file size get reduced?  Then after yo opened the form how much did the files size grow?

    You said the you had problems on more than one PC.  Are all the databases around the same size?  Are the database connected to the same BE?


    jdweng

    Wednesday, March 28, 2012 3:41 PM
  • After compacting and repair how much did the file size get reduced?  Then after yo opened the form how much did the files size grow?

    You said the you had problems on more than one PC.  Are all the databases around the same size?  Are the database connected to the same BE?


    jdweng

    It's only 1 database. It's accessed from several PCs.  The database has been around for years and has worked without problems on Win XP computers.  Recently it's been opened from Windows 7 PCs, and has been working perfectly for months.  It stopped working only on Windows 7 PCs overnight - no one had many any changes to the DB, forms, code, table structure, queries, etc...    I also recovered a backup version of that same database from months ago (this problem was DEFINITELY not happening then), but it happens on that backup version now.  
    Wednesday, March 28, 2012 3:45 PM
  • The problem may not be with access or the database.  I assume the databae is on a network drive so multiple PC cna connect to the database.  I would likeyou to try to make a copy of the database to the C:\ drive on you computer.  then see if it happens on the PC.  if it still happens then run the compact and repair on the database, save databae, exit, and open access again.

    The Disk Drive Server or the network routers may be causing the problem.  Lets see what happens.


    jdweng

    Wednesday, March 28, 2012 3:56 PM
  • The problem may not be with access or the database.  I assume the databae is on a network drive so multiple PC cna connect to the database.  I would likeyou to try to make a copy of the database to the C:\ drive on you computer.  then see if it happens on the PC.  if it still happens then run the compact and repair on the database, save databae, exit, and open access again.

    The Disk Drive Server or the network routers may be causing the problem.  Lets see what happens.


    jdweng

    Tried that before also.  Didn't help.
    Wednesday, March 28, 2012 4:01 PM
  • Does every user have their own copy of the front end or does every user share one Front end?

    If it is shared the first thing you need to do is give everyone their own copy.

    If they already have their own copy then the problem was in the latest distribution of the UI. If this is the case you need to go to a version earlier than the last distribution and copy it and test it. If theis version works you have verified the issue is in the last version regardless of when the problem started. If this is true then a Decompile/Compile should fix or at least identify any issues.

    Also if this is a shared Front End and someone happens to have opened and used it in A2010, this could cause some issues with A2007 users due to backwards compatibility.

    Also Daniel mentioned in an earlier thread "Service Pack 3". you asked if that applied to the operating system or the Office program. This is specific to the Office 2007 program. It is available at

    www.microsoft.com under downloads

    or

    http://www.microsoft.com/download/en/office.aspx?q=office&WT.mc_id=MSCOM_EN_US_DLC_ICONNAV_121LSUS007798

    or

    http://www.microsoft.com/download/en/details.aspx?id=8


    Chris Ward

    Wednesday, March 28, 2012 4:08 PM
  • Does every user have their own copy of the front end or does every user share one Front end?

    If it is shared the first thing you need to do is give everyone their own copy.

    If they already have their own copy then the problem was in the latest distribution of the UI. If this is the case you need to go to a version earlier than the last distribution and copy it and test it. If theis version works you have verified the issue is in the last version regardless of when the problem started. If this is true then a Decompile/Compile should fix or at least identify any issues.

    Also if this is a shared Front End and someone happens to have opened and used it in A2010, this could cause some issues with A2007 users due to backwards compatibility.

    Also Daniel mentioned in an earlier thread "Service Pack 3". you asked if that applied to the operating system or the Office program. This is specific to the Office 2007 program. It is available at

    www.microsoft.com under downloads

    or

    http://www.microsoft.com/download/en/office.aspx?q=office&WT.mc_id=MSCOM_EN_US_DLC_ICONNAV_121LSUS007798

    or

    http://www.microsoft.com/download/en/details.aspx?id=8


    Chris Ward

    Chris, 

    I tried the decompile steps, then recompiled.  Everything compiled fine. Did compact & repair.  Problem still persists.  I even created a brand new 2007 database, imported the tables/queries/forms into it and the problem still persists.  The problem seems to be isolated to 1 form (with several sub-forms).  One of the sub-forms calls for another form to open - that's when the error happens (but never on the 1st try).  All of the forms have been exported as text and re-imported prior which didn't fix the problem. Short of recreating that form from scratch (which I really really would prefer not to do, since I don't even know if that will fix the problem), I'm running out of options...

    Wednesday, March 28, 2012 6:12 PM
  • To make sure I understand,

    1) You have a single database unsplit that multiple users in multiple versions of Access use.

    2) You have tried Decompile/Compile, Compact Repair.

    3) You can recreate the issue using a copy of an older backup.

    4) The error only happens on Windows 7 machines & not XP machines.

    5) You have installed Access 2007 Service Pack 3

    6) Only 1 Form is affected but only when opened for a second time (need clarification, are you opening a second instance in the same database or closing Access and reopening the Form 1 time)

    7) You've tried importing everything into a new shell in Access 2007 and compiling.

    8) The format you are saving under is .accde.

    9) You have uninstalled / reinstalled Office 2007 including all the latest Service Packs and KB's

    10) You only have one version of Access running on each machine.

    11) No Users have any copies of Access, Outlook or Office 2010 installed on their machines.

    12) You've checked each computers update history and compared these to when the problem first occurred. (I think Every machine had to be updated on the date for this to have caused the issue globally)

    13) You verified the new PK you entered is a value in the tables.

    14) You followed the advice of making a copy of the db on a local drive of a computer with Windows 7, decompiling/compiling & compact / repair to verify the problem still exists on a local machine and ruled out any network issues.

    15) You are willing to share the code that makes the connection between the Form and Subform and Table and any formulas involved so we may best assist you.

    Please answer each and every question for the best advice.

    Thank you.


    Chris Ward

    Wednesday, March 28, 2012 8:22 PM
  • To make sure I understand,

    1) You have a single database unsplit that multiple users in multiple versions of Access use.

    2) You have tried Decompile/Compile, Compact Repair.

    3) You can recreate the issue using a copy of an older backup.

    4) The error only happens on Windows 7 machines & not XP machines.

    5) You have installed Access 2007 Service Pack 3

    6) Only 1 Form is affected but only when opened for a second time (need clarification, are you opening a second instance in the same database or closing Access and reopening the Form 1 time)

    7) You've tried importing everything into a new shell in Access 2007 and compiling.

    8) The format you are saving under is .accde.

    9) You have uninstalled / reinstalled Office 2007 including all the latest Service Packs and KB's

    10) You only have one version of Access running on each machine.

    11) No Users have any copies of Access, Outlook or Office 2010 installed on their machines.

    12) You've checked each computers update history and compared these to when the problem first occurred. (I think Every machine had to be updated on the date for this to have caused the issue globally)

    13) You verified the new PK you entered is a value in the tables.

    14) You followed the advice of making a copy of the db on a local drive of a computer with Windows 7, decompiling/compiling & compact / repair to verify the problem still exists on a local machine and ruled out any network issues.

    15) You are willing to share the code that makes the connection between the Form and Subform and Table and any formulas involved so we may best assist you.

    Please answer each and every question for the best advice.

    Thank you.


    Chris Ward

    Chris, 

    So far everything above is valid.  I just spoke with an engineer from Microsoft.  Seems like he has narrowed it down to a combination of poor form design and a windows update which has exposed the problem.  The main form has over 50 sub-forms that load when the form is opened (not the best design, but inherited the problem and too big a project to change now).  So he thinks that the windows update had some sort a memory management change that flushed out this problem in Windows 7, A2007 environment.  This would explain why the problem persisted even on an backed up version of the db.  

    I am going to run some more tests to definitively prove or disprove this theory.  I will update this post when I have more information.

    Thanks for the replies thus far.

    Wednesday, March 28, 2012 10:03 PM
  • Well, it seems like reducing the amount sub-forms that are loaded on the form has fixed the problem (at least for now).  Just really annoying that the database has been working like this for years, and because of a windows update I had to spend 3 days on this problem and pay $260 to Microsoft Tech Support since no documentation about this problem was to be found online.
    Friday, March 30, 2012 2:03 PM
  • Fully agree, a very annoying undocumented limitation. I've asked several times a couple of years ago on the Access forum but with no adequate response or workaround. For me that was a sign that I've reached the limits of Access and that I should look for another platform.

    A few months ago though I've decided to give it another try and did some serious refactoring on a form with a large number of subforms, some of them with their own subforms (a dashboard). I basically replaced the subforms with gridview objects. I got a huge performance boost with that at the expense of a lot of code and work.

    Sometimes, when trying to use Access as a Rapid Development Tool, we make more work for ourselves. It would be nice if the memory limitations would be solved or at least documented to avoid this kind of situations for others.

    Friday, March 30, 2012 2:36 PM

  • The very problem is most likely to occur due to corrupt database or the form corruption, however any sort of corruption with the database objects may lead to generation of this or other fatal error messages by MS access. In situation as such, decompiling or rebuilding may help. Yet other option is to compact & repair or restore from backup, but the solution that would certainly works varies with every case and also whether or not you have backup to restore from. So, in case all of these fail to do any good, try for the advanced Access 2007 Recovery software now to have the damages repaired and the database objects restored back.


    • Edited by bipsa Tuesday, April 17, 2012 8:33 AM
    • Proposed as answer by bipsa Tuesday, April 17, 2012 8:34 AM
    Tuesday, April 17, 2012 8:32 AM