"Out of memory" error when compiling

[jimatqsi]

I've been fighting this error for 2 days now. I've decompiled and compressed the DB. It's normally about 100MB after compression and grows sometimes to about 150MB.

I've googled this and found a number of suggestions but so far nothing has helped. I've gotten it in both Access 2007 and 2003; I keep the DB in Access 2003 format so I can use either of those versions.

I've deleted forms and modules no longer used; I've changed many Public functions to Private functions, as many as I could.

I read somewhere that a reference to a missing table can do this. Does anybody know about that? I may have deleted some tables or links to tables that I thought were no longer used. (And they're not but maybe some unused code references one).

Any ideas?

Thanks,
Jim

[TheSmileyCoder]

I had a similar issue once, and whether or not the cause of it is the same as yours only Access knows.

What I would suggest is to search all your code for SET statements, and make sure that for each SET statement there is a matching SET=nothing statement at the end of the procedure. I believe that access in theory should collect the garbage itself when you exit the function/sub, but I have heard that its still a good idea to do such a cleanup.

At least it worked for me.


EDIT: Sorry, I didn't read your title properly. I didn't notice that you said it was specifically when compiling, so I dont think my posted advice really applies after all.

[beacon]

Hi Jim,

I've had the same issue before and it was resolved by checking and/or re-adding the references.

When you're in VBA, go to Tools -> References. Make sure that all of the libraries you need for your database are included. Also, look at each of the library references you've already got...it may say (MISSING) next to it if something happened to your database and caused the reference to be removed/deleted.

This is the common list of library references I select in Access 2003. I think it would be identical in Access 2007 except for the first one, which would be 12.0 instead of 11.0:

• Microsoft Access 11.0 Object Library
• Visual Basic for Applications
• OLE Automation
• Microsoft DAO 3.6 Object Library
• Microsoft ActiveX Data Objects 2.5 Library

You can always click on Debug and choose Compile...if you're missing a reference you'll likely get an error message that says, "Compile Error: User-defined type not defined".

I should point out that it's always a good idea to explicitly declare all variables if you aren't already doing so. I would add "Option Explicit" to the top of each module underneath "Option Compare Database". This will force you to use a "Dim" statement each time you create a variable and prevent any unnecessary duplication of variables, which could cause conflict problems.

Here's a helpful link from Allen Browne's website: http://allenbrowne.com/ser-38.html

Hope this helps,
beacon

[Stewart Ross]

In addition to the comments already made, on occasion I've had this error if I've been adapting functions which involve recursive calls and I've accidentally changed the conditions for the end of recursion cases. I've experienced Out of Memory errors at this point as the stack space runs out when the recursion does not unwind. I can't remember this happening on compilation itself, though.

100-150MB is not unusual in Access, and should not be any problem in itself. I have a statistical reporting system which is just under 200MB in size when compacted. It routinely grows to 475MB as new queries etc are added until next compaction, but this in itself does not generate out-of-memory messages. I'd worry if the DB size was around 1.5GB, as it is likely to grow in use to reach the 2GB threshold that Access cannot exceed, but 150MB is well below that level.

-Stewart

[Stewart Ross]

Following on from my last reply, I wonder if you have a missing END IF at the end of a sub or function? If I remember correctly, this does not generate the error that you would expect - a missing END for a block IF - but can cause stack space issues which, like endless recursion, result in Out of Memory errors.

-Stewart

[jimatqsi]

Stewart,
Thanks for your reply. It's interesting you should mention recursive routines because I am, in fact, using one of those in this case. But the problem is during compilation, not at runtime, so I think that is not the problem.

The missing END instruction is an interesting idea ... I have seen odd results when I've misplaced or left out one of those.

For now, I worked around my problem by going to a backup a few days old and reinserting nearly all the code I worked on during those few days. Last modified dates on forms and queries were very helpful for that. I sure wish module last modified dates could be used the same way, but they always seem to be the same date for every module.

There is one new form I did not yet insert, maybe that's where the problem is. I'll proceed with caution and look for that missing END.

Thanks again,
Jim