System Resource Exceeded

[Guest]

I get this message everytime i open up VBA Editor and try to edit my program.
Each time I try to save my editing or compile my VBA, it gives me "System
Resource Exceeded".
The saving will fail and if I exit MsAccess and return back, it will give me
"The Vsual Basic for Applications project in the database is corrupt", and I
cannot open the MsAccess MDB anymore.

I am using MsAccess XP with Office XP SP3 and WinXP SP2


Any clue? Please help.

 

[Jeff Conrad]

in message:

I get this message everytime i open up VBA Editor and try to edit my program.
Each time I try to save my editing or compile my VBA, it gives me "System
Resource Exceeded".
The saving will fail and if I exit MsAccess and return back, it will give me
"The Vsual Basic for Applications project in the database is corrupt", and I
cannot open the MsAccess MDB anymore.

I am using MsAccess XP with Office XP SP3 and WinXP SP2

Sounds like definitely some corruption.
At this point I think a decompile is in order.

Here is an explanation of what it does by MVP Allen Browne:The Decompile switch was undocumented in earlier versions of Access, but has
been documented for 3 versions now.

Decompiling may not give the same results as importing into a new database.

In an mdb file, there are 2 copies of the code:
- a text version (what you view and edit);
- a compiled version (what actually runs).

If the database corrupts, these 2 may differ.
Decompiling discards the compiled version.
It then recreates the compiled code from the text version.
The inconsistency is then gone.

If you import from one mdb into a new file, using the same version, Access
may import both the text version and the compiled version exactly as they
were. If the two copies were inconsistent, the new version can therefore
still have the inconsistency. This is not common, but it does happen.

So, the safest procedure would be:
1. Decompile the old mdb.
2. Compact it to get rid of the space used by temp objects and the compiled
code.
3. Import into a new (blank) database. At this point you are importing only
the text version as the compiled version has been discarded.
4. Set your minimal references, and compile.

So to elaborate a little further here is what I would do.

1. Make a backup of your database.

2. Repeat step 1

3. Compact the database:
Tools | Database Utilities | Compact

4. Close Access. Open a command prompt, and enter something like this. It is one line, and include
the quotes:
"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\My
Documents\MyDatabase.mdb"

Adjust the syntax for your Access.exe file and file location.

5. Compact the database again to clear it out:
Tools | Database Utilities | Compact

6. Create a new blank database and import everything (including data, menubars, toolbars,
relationships, etc.)

7. Set minimal References needed

8. Turn off Name AutoCorrect

9. Compile your database

10. Compact the new database
Tools | Database Utilities | Compact

11. Set any Startup properties
Tools | Startup

12. Test things out in the new database.
Problem solved?

I would also review the following information on corruption as well:

http://allenbrowne.com.au/ser-47.html

http://www.granite.ab.ca/access/corruptmdbs.htm

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Whittle,Jerry
(See Fix Corrupt Access Database v3.9 Document)

http://www.e-tech.ca/001-AccessFAQ.asp

http://www.everythingaccess.com/tutorials.asp?ID=0

ACC2000: How to Recover Data from a Corrupted Table by Using the DAO Method
http://support.microsoft.com/?id=815280

ACC2000: How to Troubleshoot Corruption in a Microsoft Access Database
http://support.microsoft.com/?id=306204

ACC2000: How to Troubleshoot and Repair a Damaged Jet 4.0 Database
http://support.microsoft.com/?id=209137

ACC2000: How to Recover Data from a Damaged (Corrupted) Table
http://support.microsoft.com/?id=247771

ACC2000: How to Use MS Query to Recover Data from a Damaged Jet 4.0 Database
http://support.microsoft.com/?id=304561

Jetcomp utility available at:
http://support.microsoft.com/?id=273956

Good luck,

 

[Guest]

Dear Jeff,

I think I've failed to mention that, my Access.MDB is linked to Source Safe
Database.
Even I've try to delete the local Access.MDB and recreate the whole
Access.MDB from Source Safe the problem still persist.

Thank You,
Woo

 

[Jeff Conrad]

in message:

Dear Jeff,

I think I've failed to mention that, my Access.MDB is linked to Source Safe
Database.
Even I've try to delete the local Access.MDB and recreate the whole
Access.MDB from Source Safe the problem still persist.

Hi Woo,

Humm...yeah that probably makes a difference.
Unfortunately I do not have any experience with Source Safe, so I probably
cannot advise on the best way to troubleshoot the issue.

If you do not receive any other responses in the next day or two you
may want to try re-posting, but make sure you put Source Safe as part
of your Subject line to attract attention.

Good luck,

 

[Guest]

The process Jeff describes should cure the problems with the mdb itself.
There is one extra step that is recommended. After Step 4, copy all your
modules to a .txt file. I usually use notepad. go into each form and report
that has VBA code in it and do the same thing then set the Has Module
property to no. Delete all the code in the mdb as you go.

Then after you have copied all the objects to the new mdb, paste your
modules back in from the .txt files, open each form and report that had code,
set the Has Module property to Yes and paste it's code back in.

As to the Sourcesafe issue. I have not used it in a little over 3 years,
and I don't remember Sourcesafe being the root of a problem like this, but I
guess it could happen. I would suggest you delete your old project in
Sourcesafe, and update Sourcesafe with the new mdb or perhaps create a new
project in Sourcesafe with a different name.

 

[Guest]

Thank a lot guys. I think I'll will try both ways and keep you update with my
progress.
It may take a few days though.

Thank You
woo

 

[Jeff Conrad]

in message:

Thank a lot guys. I think I'll will try both ways and keep you update with my
progress.
It may take a few days though.

Please do let us know how things work out.
This will benefit not only us, but anyone else that may also
be presented with this problem in the future.

Good luck,