Managing Memory and Error 3048 in Access 2003

Posted on 2008-06-16
Medium Priority
Last Modified: 2013-11-29

I have a very large applicaiton nearing completion.  I have come into the problem that without to much difficulty users reach the memory limit of access.  The error 3048 is sometimes thrown.  Other times the program hangs.  

How can I best manage for the limitations of access without causing the user to much greif.  I would like to capture the out of memory error, send a warning message (msgbox-too many forms open, shut some) and shut down the form that was last open.  

I have tried to do this by calling a custom error box function from the form and report error functions, but it doesn't always work properly.    For example:

    DispError "DMenuBar", "setReportMenu", Err.Number, Err.Description
    Resume Exit_Handler

It seems that the out of memory limits of access can be reached without an error being thrown.  Is this the case?  Any suggestions / discussion about the issue would be great.

Question by:Carl Sudholz
  • 2
  • 2
  • 2
  • +2
LVL 33

Expert Comment

ID: 21792314
I think that you should first try to solve the problems that are causing the out of memory. Maybe you're putting to much data into memory when you query your tables or your forms are to "heavy" with lots of controls in it.
Try figuring out what is causing the out of memory error instead of trying to capture the on error event.


Author Comment

by:Carl Sudholz
ID: 21792404
Yep I know.  I have many very large queries and large tabbed forms.  I already modify the forms that subforms are hidden when on on the selected tab page.  I also use a number of make tables on the biggest queries.  The queries do not contain any vba procedure calls to slow things up.  All of the recordsets are closed and set to nothing.  

I have pretty much done all I know to prevent the memory overload.  My only option I can think of is to  restrict the user to have only one form / report open at a time.  Doing this will keep the memory under control, but I would like to give the user more flexibiliity, if possible. However in providing the flexibility some users may tend to have too many forms / reports open at once for MS access to handle.  Thereby I would like to manage this by capturing the error.
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 21799141

1. Is the Database "Split"?
If so, then how big is the front end and how big is the back end?
If not, then split it and test for the error.

2. Are all your variables declared as the smallest type?
(Many people over-use the Long datatype when integer will suffice)

3. Avoid using Recordsets when SQL will do the same thing.

4. Avoid storing images in the db. (Even the smallest image can bloat the DB, and even "Linked" images can cause bloat)

5. How much memory is free on the end user's machines?
For example, my machine has 2gb of Ram on Win XP.
Before Access is started, I have about 1.5 gb free.

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

LVL 11

Accepted Solution

RgGray3 earned 1000 total points
ID: 21803353
One reason it is hard to recover from an out of memory error is ...   you are out of memory....
Hard to hit the breaks when you have already hit the wall!

So yes the trick is to prevent the out of memory problem.

Usually an out of memory error is caused by being out of STRING, HEAP, STACK memory (which we don't directly control in Access as you can in C)
this can usually be verified by oppening Task Mgr after the error occurs and you will see that the machine has plenty of memory

In generic terms   there is a limited amount of memory allocated in access for string literals and keeping track of open forms, reports...  yadda yadda

I forget the actual numbers but I believe it was 2, 2k pages of memory in Acc97 and has been expanded to 4 in 2k+

I could get into more detail but it's boring....  

One way to minimize your memory is to control how your objects are loaded and unloaded... let's try a simple illustration

Form A opens B.  B opens C  and D, C opens a report (E) (Now if all of your forms are open at the same time  you have created a stack of ABCDE

Lets say you close form C and open form G (notice I did not say that you closed the report(e) or Form D
You MIGHT think that you have freed up the memory from form C... but you would be wrong....  the computer can not "Fill the hole" with something new

the new form would take new memory after the end of E   (Lets call it F)
From F you call a new copy of C....   it again would use new memory on the stack and come after F...
Now close the report and Form B....  Again this memory is not recovered  

Not until the items above the vacant holes are closed is it possible to recover that memory....   if the application gets too complex....   it may never recover the memory (until after the restart or crash)

A long way to get to the solution....   application object control.

If A opens B  and B opens C...    the only way you should expose A for additional work (and the possibility of oppening additional objects) is by closing C then B

When teaching, I refer to this as Yo-Yo programming...
Go Out the way you came in.

Imagine a dog on a 10 ft leash....   Tie him up to a telephone pole next to a street sign and fire hydrant...   In 5 minutes the dog has 3 inches of available leash...  he has tied himself up in nots

Do not allow A to open B  & C and D at the same time.  

Author Comment

by:Carl Sudholz
ID: 21818846

An excellent and logical response.  I will modify my application interface to operate as you describe.

Thank you.
LVL 11

Expert Comment

ID: 21822579
Glad to help...   Good Luck


Expert Comment

ID: 22632205
I have had the same problem with a database at least with one User, (although I have never been able to replicate it myself). The design strategy put forward by RgGray3 seems sensible in any application regardless of whether errors are being recieved or not.

An issue can occur with menus that allow forms to be opened irrespective of the MODAL setting on any particular form. One way round that is to grey out certain menus to restrict certain combinations (or just lots) of forms from being open at the same time. That isnt great UI design though as just now I could select a Report of Form and now suddenly I cant. Re-designing forms to limit the number of listboxes and comboboxes can help - but if you have a SEARCH form then this type of control is usually the most appropriate.

I think a combination of workflow control, killing off objects properly (particularly recordsets and db connections) and making listboxes/combos available only at the point a user requires access to that selection option - probably summarises the design approach that will minimise Error 3048 problems.  Other than that, we can hope that MS have increase the stack etc memory in future versions of Access (which mah be the case already in 2007 I dont know).

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22634265

Great Explanation.
I bookmarked this and added it to my KB.

That is why the Access switchboard manager basically uses "One" form and just swaps out the buttons and commands.


Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

601 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question