Carl Sudholz
asked on
Managing Memory and Error 3048 in Access 2003
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
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
ASKER
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.
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
RgGray3
An excellent and logical response. I will modify my application interface to operate as you describe.
Thank you.
An excellent and logical response. I will modify my application interface to operate as you describe.
Thank you.
Glad to help... Good Luck
Rich
Rich
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
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
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
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
Try figuring out what is causing the out of memory error instead of trying to capture the on error event.
jppinto