Access 2003: Not enough Memory Errors 2

[maxhugen]

0 vote down star


I'm using Access 2003 on a duo-core machine with 4GB of RAM, running Windows XP (Service Pack 3) [5.1.2600]

Periodically, I get an error msg "There isn't enough memory to perform this operation. Close unneeded programs and try the operation again."

A check of Task Manager indicates that there is plenty of free memory. Closing other open programs makes no difference.

This happens sporadically, and under different circumstances: sometimes whilst saving Form design or VBA code changes, sometimes when several Forms are open and in use.

If attempting to save design changes, and this error occurs, the Access objects are corrupted and can't be recovered.

Any suggestions on what might be causing this would be very welcome.

MTIA

Max Hugen
Australia

 

[lameid]

I am assuming that your Access 2003 is SP3 as well and otherwise fully patched.

The only memory error I have seen has to do with Access is the maxlocksperfile registry setting. I have seen this crop up when executing large queries. My hunch is your problem is different since it is not query related and has to do with saving objects...

On the otherhand it is the only memory issue I know of so here is your shot in the dark. Below is a sample registry file that sets the maxlocksper file key. It sets it to 4 times the default.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0]
"MaxLocksPerFile"=dword:00009470

Just copy and paste the above into notepad save as a .reg file and doubleclick it to merge into the registry. You could also tinker with the hexadecimal value or navigate there with regedit.

Everything else that comes to mind is hardware/OS related. You might also see if your boot.ini file has been modified to start allowing allocation of 3GB of ram (XP 32bit max, assuming your not running 64 bit version), but alas the page file should have you covered. The other thought would be assign 1 GB of ram to your onboard video controller in the bios, assuming you have one, then XP would only see the 3GB of ram no matter what. Other posibilities include malware, memory isses and corrupted install (Windows or Office / Access).
Maybe the Hardrive itself is failing? Find a utility to read out the S.M.A.R.T. properties. Also do a check disk if the report is clean. If it is not clean, make backing up your first priority.

 

[maxhugen]

Hi lameid

Yep, A2003 is SP3, and I do apply all MS patches.

I've read posts elsewhere that advise the 'memory' error msg is used by Access when it doesn't have a clue as to what's actually wrong.

FWIW, my MaxLocksPerFile is set to (dec) 9500, and there is no RAM entry in boot.ini, it's stock standard.

Really, though, I doubt that the prob is memory related at all.

Max Hugen
Australia

 

[lameid]

The 9500 is of course hexadecimal...

I assume your've tried all the standby's (given your recent post about application.saveastext): Command-line decompile; import everything into a fresh file; compact and repair.

My next question: Is the problem database file specific or computer specific?

Anything else you've tried? Anything unusual about your application (like references)?

Hmmm.... do you have any undocumented code in the database like application.saveastext? It is a stretch but maybe the compiler does some odd things with it sometimes which is why it is undocumented?

I think I'm fresh out of ideas after that one.

 

[maxhugen]

Thanks lameid,

Yes, I've tried the decompile/recompile, something I do quite regularly. Can't tell if the prob is db or machine specific, but I do think it's Access.

I found that when I tried exporting a corrupt form as text, the entire form desciption was replaced with ?????? etc, but the code behind the form was OK.

No references apart from the ones required for Access, as I use Late Binding to avoid potential version issues.

It's true that I do have a function which exports all my objects using SaveAsText, but I've only recently added that, it compiles OK, and the problem surfaced at times long before that was introduced.

Touch wood, but since I last rebuilt the mdb, importing ALL forms and reports using LoadFromText, Access hasn't crashed, so I'm keeping my fingers crossed.

Max Hugen
Australia

 

[genomon]

If you are performing uncommitted changes to a recordset in code (.edit, .delete, etc.) especially in a loop (in a loop the transactions are not committed until the loop is complete), try adding this right before the loop initiates:

DAO.DBEngine.SetOption dbMaxLocksPerFile, 30000

You may need a different value as approprite - this is a long int & not hex. When the app loses scope, the setting reverts to default and nothing is saved in the registry.
Conversely, you could enclose each operation in an explicit transaction, and commit the trans each iteration, but I think this may have performance issues.

Cogito eggo sum – I think, therefore I am a waffle.

 

[lameid]

Thanks for the star...

I had forgotten about it but genomon's code is the other way of increasing the maxlocks per file aside from my registry hack.

The registry is a good way to fix all computers with group policy, but code is definitely the way to go for distributing the application to others... A convenient time to be reminded of it for me... have a star.

 

[genomon]

SELECT @lameid = MyHero FROM tbl_kudos!

Cogito eggo sum – I think, therefore I am a waffle.

 

[maxhugen]

Thanks Genomon. I do try to avoid the large loops that you describe, but that's a handy tip - will add it to my "bag o' tricks".

Max Hugen
Australia