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

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.
Bob CollisonSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
can you share the code?
Bob CollisonSystem ArchitectAuthor 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 ExpertCommented:
Hi Bob,

i would not understand unless i see that small amount of code.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Bob CollisonSystem ArchitectAuthor 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
Professor JMicrosoft Excel ExpertCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob CollisonSystem ArchitectAuthor 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 ExpertCommented:
you are welcome Bob, i am glad i was able to help.
Bob CollisonSystem ArchitectAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.