Re: [MSAccessProfessionals] Error Out of Memory - How big is too BIG for MS Access?

@ozair,

You've gotten some good advice so far from Crystal and others, and it seems you're already doing most of those things.
Unfortunately, it may not be enough. I have seen both ~2GB databases that still worked (up to finally hitting the limit), and much smaller ones that hit those "out of memory" errors long before 2GB. The OOM errors are not about database size, but about the heap memory limit, and that is HALF the 2GB database limit. Access(/VBA) can only manage 1GB of heap space in use, and there are problems with this.

This is what you're running into most likely.

In these cases where the OOM errors recur frequently - some application re-engineering may be needed, or you simply need to be absolutely certain you're not leaking any COM Object references. So, step 1 is to review your code to be certain that you "clean up your toys" the moment you're done using them. If you are assigning COM object reference in any PUBLIC/Global variables, focus your re-engineering on getting rid of those cases. For example, of you are using Excel via automation, it is tempting to put the Excel.Application reference into a public/global and keep it open for re-use. Don't. Bad Things(tm) (like OOM errors) tend to happen when you do this kind of thing.

On 09/05/2023 9:45 AM EDT ozair <ozairkhalid@hotmail.com> wrote:

 

 

On Tue, Sep 5, 2023 at 06:15 AM, Richard Rost wrote:

I agree with Crystal on checking your object variables, but also, don't try to do too much with a single database. I've had students before who tried to run their entire business off of one single database, when in reality, it could have been split into multiple databases. Remember, the accounting people don't necessarily need the same information as the warehouse and shipping people, or the CEO. You can create multiple front-ends that access the same back-end data, and each one can be much more lightweight and compact. So keep that in mind; one single database doesn't have to run the whole operation.

And remember, you can make buttons on your main menu that can open the different databases. So, in case the shipping people do need to access the customer records, they can. But if it's not something they use on a daily basis, then don't try to pack all those features into a database that's not going to use them.

LLAP
RR

@Richard, 
I agree with you, the file should be split into smaller files.
We are using MS Access Workgroup Security MDW file - maybe I am unaware or its not possible to avoid the logon dialog.
That would have user intervention - that's undesirable.

@Jane,
After compact, the file size is around 200mb~250mb. As per MSFT's documentation - its still far below the limit. 
Yes, its already split into FE (MS Access 2016 - 64-bit) with BE (MS SQL 2012).

Best,
Ozair