Access running out of memory RRS feed

  • Question

  • I am working on a large large project that runs on Access 2010 32 bit. It has hundreds of forms, modules and classes. Many of the forms have multiple subforms. 

    My problem is that just starting the Access project, Access uses about 65 mb of memory. After opening up a few of the forms, the memory usage goes up over 100 mb. When it gets to about 140 mb, I start getting system resources exceeded and then lots of other error messages after that. The system has many gigabytes left of free memory. Decompiling and compacting makes no difference.

    Are there any workarounds to increase the amount of memory that is available to Access?

    Thursday, November 1, 2012 2:08 PM

All replies

  • Windows should handle the memory allocation automatically.  You can reallocate more memory, but I don't think you want to do that.

    Go to Start on type in resmon and then go to memory and it will show you how your ram is being used. Windows 7 handle ram in a new better fashion. You can also see what programs are using your ram and decide if you want to turn some things off.

    Maybe a simple Compact and repair will get you back in business.

    Ryan Shuell

    • Marked as answer by scott340 Thursday, November 1, 2012 5:06 PM
    • Unmarked as answer by scott340 Thursday, November 1, 2012 5:06 PM
    Thursday, November 1, 2012 4:50 PM
  • There are many ways to architect an application to minimize memory usage. You can try closing forms when they are no longer needed and reopening them when they are needed. How big are your modules? Can you split the functionality  into smaller modules which are only called as needed (and not at all if not needed).
    Thursday, November 1, 2012 7:36 PM
  • There is no question the app could have been designed better but things like breaking modules up into smaller pieces doesn't seem to make any difference. The users do close unused forms but there are quite a few they need open for their normal workflow. 

    The main problem seems to be that the memory allocated to the VBA code is pretty small and we are hitting the ceiling. I was looking for a way to raise the ceiling.

    Thursday, November 1, 2012 8:37 PM
  • Maybe you can split this application up. Are there parts of the appplication that some users use that others do not? Maybe you can redesign it so that you have different front-ends each with the capabilities that a subset of users needs. Just an idea.

    "The users do close unused forms but there are quite a few they need open for their normal workflow."

    - since a user can only interact with one form at a time, I question a design that has multiple forms open at the same time. Also I would not rely on a user to close unused forms, I would make the application close them.

    If this application has truly become too big for Access to handle (it has never happened to me in 15 years of working with Access) maybe you should consider converting the application to .Net with SQL Server database.

    Thursday, November 1, 2012 11:33 PM
  • You might need to make sure you have all the latest fixes applied ....

    I just discovered this:

    which contains:

    Consider the following scenario:
    • You create a form that contains a sub-form in an .adp file in Access 2010.
    • The Server Filter By Form feature is enabled in the form.
    • You open the form many times.

    In this scenario, lots of memory is consumed and the memory cannot be freed after the form is closed. Additionally, you may receive the following error message:
    There isn’t enough memory to perform this operation. Close unneeded programs and try the operation again.
    Thursday, November 1, 2012 11:38 PM
  • Hi scott340,

    Welcome to the MSDN forum.

    For the amount of memory available to Access, you can check the following thread and read Dennis's first reply:

    system resource exceeded

    "If these installations of Access are 32bit installations, you are limited to 2GB of user virtual memory. Unless you are using many applications at the same time, a typical machine should have a sufficient paging file to create the full 2GB of virtual memory available for the process but you could check the paging file size using this link." 

    Have a nice day.

    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us

    Monday, November 5, 2012 6:58 AM
  • Could you post the exact error messages you are getting, with some indication of which occur most frequently?  Are they messages from Access, or messages from the operating system?  I have never had an Access application run out of memory, but there are other resources that are more limited; especially the number of TableIDs, an internal table representing (roughly) each table and recordset that is open.  If you run out of TableIDs, you get error 3048 "Can't open any more databases".  This is not uncommon in applications that have lots of complex forms and subforms open all at once.

    Dirk Goldgar, MS Access MVP
    Access tips:

    Monday, November 5, 2012 3:04 PM