Access Database Out of Memory Error

Hi,

What version of MS Access are you using?

Bill

2002

I would STRONGLY suggest you stop what you're doing, make a backup IMMEDIATELY, and then do the following:

1) Compact the database - Tools - Database Utilities - Compact and Repair
2) Compile the database - from the VBA Editor, click Debug - Compile. Fix any errors. Continue to Compile until the menuitem is disabled
3) Compact again
4) Now build a new, blank database. Import everything into that new database
5) Compact the new database
6) Compile the new database
7) Compact the new database again.

Note that you may need to transfer over references from the old to the new. To do that, just review Tools - References (in the VBA Editor) to make sure you move the refs from the old to the new.

Sameer2010,
Below are the results from the query:  If I am reading it correctly -32764 are modules and there are 186.  Are there any irregularities in the results?  Thanks,  Jeff
Type      Flags      Cnt
-32768      0      393
-32766      0      1
-32764      0      215
-32761      0      186
-32758      0      1
-32757      0      3
1      -2147483648      4
1      0      406
1      2      1
1      10      1
2      -2147483648      1
3      -2147483648      3
3      0      6
5      0      1230
5      3      1706
5      16      28
5      32      192
5      48      84
5      64      318
5      80      4
5      128      132
6      2097152      208
8      0      70

The total count is around 5000 ... the limit is around 32,000 ... so that doesn't seem to be a factor.

Have you tried the methods I suggest?

LSMConsulting:
I am in the process of doing so now.  I will let you know the results in a few minutes.
Thanks Jeff

Also make sure that you've got all updates for Windows and Office/Access.

LSMConsulting:

I believe I have the latest window and office updates installed (Winodws & Office XP)
I followed all 7 steps and no problems.  Still can import less complex forms but will not accept a complex form even though I can import it into another db with no problem. I will attache the import error messages. (I tried renaming the form) I removed the form src code and can import but can not access the vba module (errs out).

Assuming there was still some error with the form I attempted to see if it had to do with number of controls.  I created a new blank form and began to add ctls.  Added combo boxes with lables - 10 at a time.  Will accept 50 if I save and then compile. If I added the next 10 and do not save first get the Out Of Mem err.  If instead I save first can added the 60 and will compile.  This seems to be well under the limit of ctls per form.  Is there a limit on combo boxes per form different than the 754 lifetime limit for ctls on a form?
It also appears that once a form is involved in an Out of Mem  err even when the overlimit ctls are deleted & mdb compacted the form will not let ctls be added even thought it will comple with what is present.  

On the other hand I was able to import a unique form with over 700 text boxes with no problem. Even imported multiple copies with no issues during import or compiling afterwards.

Thanks,
Jeff





-CORE-ImpErr-01.bmp

2 err msg
-CORE-ImpErr-02.bmp

JDettman:

How do you save a form as text?

Also see my previous comment about creating a new mdb and importing.  In continueing that comment I just created a new db and imported the test frm I referred to with the multiple combos that caused err.  In the new mdb I have no problem adding and compilng (stoped at 140 cbos )

It seems that even after creating the new db and importing objects even though it compiles there is still somehting that is creating a limit.  Is it possible to have a corrupt object that will let mdb compile but create a limit in adding cbo.  Did similar test adding tab ctls- reahced simialr limt imported into another blank mdb and no problem compiling. ?????

Thanks,

jeff

If you import your trouble form to your newly created database, do you then have the same problem on that form?

See my earlier post for info on SaveAsText and LoadFromText.

Experts:

Here is the update:  Going back to a July verion I can export and then re-import into the July V(using text form or access import export feature) one of the complex forms with no problems.  However in the newly created mdb it will not.  I have even created another new mdb and methodically imported objects.  I have avoided the one form even thought it appears the July version is fine.  I have been using a clean copy of the new mdb for all the tests just in case once it encoutners the Out Of Memory error it corrupts something.  I cannot get all forms in and compile.  I can get most of the forms in (90-95%) and it compiles fine and can even create new forms using the cbo ctl test mentioned earlier.  However when I import the last 10-5% it will not compile.  It does not appear to matter which forms are the last ones to come-in.  I have alternated importing from different ends of the form list (alphabetically) - even tried the middle first.  All to no avail.
The difference between the July and the current version is a moderate number of forms/reports/queries and code.  Am I missing something about size??

Tired and frustrated in Siloam Springs,
Thanks,
Jeff

Another Note:

It appears that when I am getting close to the end of the list if I import the forms one at a time and compile and save before the next import I can get farther through the list.
Not sure if this helps.

Thanks
Jeff

If that's the case, you may simply have a database this is too complex. Given your counts:

Forms: 393
Reports: 215
Modules: 186

Why do you have that number of forms and reports? Even in an exceptionally complex databse, 100 or so forms would be pushing it. You're nealy 4 times that amount. It may be time to simplify your objects, and combins/reuse them as needed.

Do your forms contain a large number of subforms? Reports contain a large number of subreports?

Do all your forms/reports contain code modules? There is a limit of 100 Modules, which includes forms/reports with code.

Experts:

Most forms and reports are already combined and reused where possible. There is a limited number of special forms/reports that have multiple subforms/sub reports, most do not.  Most forms have code modules.  Many reports have a small code module that could be moved to a standard module.

Even though the application is below published individual specifications is there another specification for compiling limts.  If so what is the criteria?   Is there a way around it.?

Thanks,
Jeff

<<The difference between the July and the current version is a moderate number of forms/reports/queries and code.  Am I missing something about size??>>

  Sounds from everything you said that you are bumping into some type of limit.  As LSM pointed out in his last comment, your limited to 100 modules, which includes forms and reports.
 FWIW, your the first that I've run across that has ever described a problem along these lines.  I didn't remember anyone ever having a DB with that many objects.
Keep in mind that you can have a single DB to hold the data, then different "Front ends" with some of the forms and reports all linked to the same "backend" data DB.  So it's possibly to split your app up.
From a centeralized menu front end, you could then open another front end (say for sales reporting), do processing, then open the menu app backup and let the user choose another section of the application to use (i.e. A/R).
 In this way you could split up the app into as many pieces as needed, yet to the user it would be fairly transparent (there would be nothing special they would need to do).  There would be a bit of a delay in opening new DB's and some gyrations on the screen, but they would not be significant.
JimD.

As mentioned above, 1000 is the limit of modules that could be compiled.
I think, there might be some objects that have HasModule property set to TRUE. If so, this would add to the list. Can you try setting this property to FALSE for the object, where there is actually no associated class and then try adding one more object? If it works, it can confirm our test.

Experts:

I followed the suggestion and exported and re-imported all forms and reports into the new mdb as text files.  I performed a compile after the forms import and before the report import and after the report import.  During the form compile it identified several dots that should have been bangs (from 2004 code).  Other than that all is well, have the full list objects in and no problems with compiling.  Not sure what all was corected other than the few bangs.  Although the code worked fine before and compiled it must have had something in it that created too much overhead for additional objects????????  It also apears to run faster.  One strange side effect is that the mdb is significantly larger (25%) even after repeated decompile and compact and repair runs.  Any thoughts on that?  Any other thoughts?
I have already added code to make this process a part of my mdb maintenance tools.
Again thank you very much.  
Jeff

Try the new db - import routine again, since your current db is now working. This might reduce the size of your database somewhat. That said, if it's not giving you any issues, I might not mess with it right now.

Thanks again,