Solved

Managing Memory and Error 3048 in Access 2003

Posted on 2008-06-16
8
918 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
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.

jppinto
0
 

Author Comment

by:Carl Sudholz
ID: 21792404
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
ID: 21799141
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
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.  
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Carl Sudholz
ID: 21818846
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
ID: 21822579
Glad to help...   Good Luck

Rich
0
 

Expert Comment

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

Tony
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22634265
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of utilizing SQL Server views 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 Access…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

895 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

15 Experts available now in Live!

Get 1:1 Help Now