Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access Database Out of Memory Error

Posted on 2010-01-02
23
Medium Priority
?
735 Views
Last Modified: 2013-11-29
I am unsure of what has created a barrier to adding additional vba code/ imporitng objects. If I remove a number objects (forms/reports) it is possible to continue adding code and other objects.  (It does not seem to matter which objects are removed which appears to mean the problem is not with corrupted objects.)  It is not posible to import forms/reports back into the mdb with out the Out of Memory error when attempting to compile. While testing I noticed that I can import simplier objects one at a time without the error upto a point.

Are there other mdb limits than those listed in the specifications.  The applciation is below those limits.

The MSysObject table has 5198 objects if this matters.

Any suggestions would be helpfull
0
Comment
Question by:JBB9906
  • 11
  • 7
  • 2
  • +2
23 Comments
 
LVL 14

Expert Comment

by:Bill Ross
ID: 26163347
Hi,

What version of MS Access are you using?

Bill
0
 

Author Comment

by:JBB9906
ID: 26163389
2002
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 664 total points
ID: 26164406
Could you check what is the output of the following query?
Also, how many code modules do you have? It seems there is 1000 limit for the modules.
SELECT type,flags,count(*)
FROM msysobjects group by type,flags;

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 85
ID: 26165292
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.
0
 

Author Comment

by:JBB9906
ID: 26165574
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
0
 
LVL 85
ID: 26165591
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?
0
 

Author Comment

by:JBB9906
ID: 26165611
LSMConsulting:
I am in the process of doing so now.  I will let you know the results in a few minutes.
Thanks Jeff
0
 
LVL 85
ID: 26165632
Also make sure that you've got all updates for Windows and Office/Access.
0
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 668 total points
ID: 26166371
I would import into a new MDB.  Sounds like the VBA project file is corrupt.  That may be due to one or more objects.
Import everything but forms and report, then do a half dozen/dozen forms at a time and compile after each.  You'll either make it through all, or pin it down to a couple of forms that are causing problems.
The only way to get those forms into a new MDB will to be do a save as text and then import text.   By doing that, you won't be reading the VBA source code in the original DB.
JimD.
0
 

Author Comment

by:JBB9906
ID: 26166685
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
0
 

Author Comment

by:JBB9906
ID: 26166687
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 668 total points
ID: 26166754
Both errors are signs of corrupt objects. One or more of your forms or reports could be corrupt, or as JimD said the VBA container is corrupt. If this seems to always occur on one form/report, that would be the one that is corrupt.

You can force Access to rebuild the form, which may resolve your issue. To do that, use the undocumented SaveAsText and LoadFromText commands. From the immediate window:

SaveAsText(acForm, "YourFormName", Currentproject.Path & "\YourFormName.txt")

Now delete the form in your database, and Compact and Repair. Now do this in the Immediate window:

LoadFromText(acForm, "YourFormName", Currentproject.Path & "\YourFormName.txt")

This will rebuild the form from scratch. Note this doesn't always work, but can recover corrupt forms/reports. If this does NOT work, then your recourse is to either rebuild the form, or recover from a known good source.

<I believe I have the latest window and office updates installed (Winodws & Office XP)>

I'd make a concerted effort to insure this.


I'm not sure why you have issues adding controls.

If you can import the form without the code module, this would indicate a VBA Container corruption. However, moving your objects to a new, blank database will generally resolve that.
0
 

Author Comment

by:JBB9906
ID: 26166756
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
0
 
LVL 85
ID: 26166774
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.
0
 

Author Comment

by:JBB9906
ID: 26167907
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
0
 

Author Comment

by:JBB9906
ID: 26167962
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
0
 
LVL 85
ID: 26169489
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.
0
 

Author Comment

by:JBB9906
ID: 26170996
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

0
 
LVL 59
ID: 26171067
<<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.
0
 
LVL 13

Expert Comment

by:sameer2010
ID: 26171186
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.
0
 

Author Comment

by:JBB9906
ID: 26176802
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
0
 
LVL 85
ID: 26179336
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.
0
 

Author Comment

by:JBB9906
ID: 26180432
Thanks again,
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question