Windows 7 Pro ACCESS 2010 VBA - Out Of Memory (Error 7)

Bob Collison
Bob Collison used Ask the Experts™
on
Hi Experts,
I have just run into this error while trying to add another small amount of code to a quite large Procedure.

If I remove the last code it is ok.

I have saved it, done a Compact / Repair and I still get the error.

Is there a way to overcome this error?

Thanks.
Bob C.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
can you share the code?
Bob CollisonSystem Architect

Author

Commented:
Hi Professor,
Unfortunately this application is large and very complex.  The only way I have been able to share the code in the past was to create a separate DB with just the code I had a problem with.  In this case I don't believe that would provide an accurate representation of the issue.
Thanks,
Bob C.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
Hi Bob,

i would not understand unless i see that small amount of code.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Bob CollisonSystem Architect

Author

Commented:
Hi Professor,
The application is for converting the data from one version of the application to another.  The .accdb is just over 100 Meg making it difficult to provide the whole application.  In addition the data is all in Back End Tables.

I am attaching a file containing all of the code for this procedure.

It works correctly if the Code for the following steps is removed.
STEP_4590: to the end of STEP_4599:

As you can see in STEP_4593: the following line of code needs to be uncommented however as soon as I do this I get the Error 7 Message.

Thanks.
Bob C.
Period-Event-Module-Conversion-Code-.txt
Microsoft Excel Expert
Top Expert 2014
Commented:
Hi Bill,

at the first look at this long code inside one procedure. i understood that you pushed this too far.  to fix this you need to break this large procedure into smaller part, try make it two and call one from another.

here is also confirmation from Microsoft on this limitation
Bob CollisonSystem Architect

Author

Commented:
Hi Professor,

Thanks for the confirmation that this code it too large.  I had assumed as much.

I'll leave this open for a couple of days to see if anyone else has comments.

Thanks again,
Bob C.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome Bob, i am glad i was able to help.
Bob CollisonSystem Architect

Author

Commented:
Hi Professor,
I would like some additional guidance with regard to doing this.  My plan is as follows.

This application is strictly for data conversion.  There are currently four Modules in this application with all of the conversion code for all Modules within the one application.

My approach will be to split each Module into a separate .accdb containing only the conversion code for that Module.  Each Conversion Module .accdb will then be opened, run and closed in sequence from a Master Conversion Application .accdb.

Each Conversion Module when opened by the Master will execute without User intervention.  This will make the individual Modules transparent for the User while I can mange / run them individually for development.

Does this sound like a good approach?

Thanks,
Bob C.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial