Solved

Managing Memory and Error 3048 in Access 2003

Posted on 2008-06-16
8
910 Views
Last Modified: 2013-11-29
Experts,

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:

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

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.

Thanks
0
Comment
Question by:Carl Sudholz
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 33

Expert Comment

by:jppinto
Comment Utility
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.

jppinto
0
 

Author Comment

by:Carl Sudholz
Comment Utility
jppinto.
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
csudholz,

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.

JeffCoachman
0
 
LVL 11

Accepted Solution

by:
RgGray3 earned 250 total points
Comment Utility
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.  
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Carl Sudholz
Comment Utility
RgGray3

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

Thank you.
0
 
LVL 11

Expert Comment

by:RgGray3
Comment Utility
Glad to help...   Good Luck

Rich
0
 

Expert Comment

by:fester62
Comment Utility
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).

Tony
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
RgGray3,

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.
;-)

JeffCoachman
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Send email from MS Access macro 7 31
Access 2010 3 15
Dealing with Null in VBA sql statement 9 24
Resize text 4 14
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now