Solved

Access Error - Visual Basic for Applications Project in the database is corrupt - when app opens

Posted on 2007-11-29
11
428 Views
Last Modified: 2011-08-18
Using Access 2003. I have a rather large Access app, which is a "front end" for a SQL Server 2000 database. I develop in the MDB then, when I reach a release point, I compile to an MDE. The users don't see nor have access to the MDB - they can only access the MDE. When I tried to open the database this morning, it threw this error:

Visual Basic for Applications Project in the database is corrupt

Googled the issue - the only solutions I found had to do with (1) compiling a 2003 mdb under prior versions (that's not the case here), and (2) running under an incompatible access runtime (which also does not apply).
I can load the database, but not run it. If I attempt to copy, export, or view any objects, the same error appears.
0
Comment
Question by:rlwhitney
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 20373494
Does a new copy of the mde do the same?

Have you just installed Office2003 SP3 (and thereby wrecked everything(:-))?
0
 
LVL 84
ID: 20373557
<When I tried to open the database this morning,>

Was this on the development machine, or an enduser machine?

Also: Are you sure that your target machine has the correct References installed? For example, if you've used a 3rd party ActiveX control, your target machine will have to have this control installed. Also, if you have references to Office applications (like Word 2003 or something), then your target machine must also have those references.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20373581
The first thing to do right now is to backup your 2003 .mdb.  You may have encountered corruption to the project file in which case you are in for an unpleasant but not necessarily unsolvable situation.

Project File corruption was a new bug introduced in 2000 an has been a problem ever since.  Though Microsoft does not talk about it much I have talked to Microsoft including everyone from their Project Managers, Testers and even demonstrated it in person for their Developers (they were working on Office 2007 at the time).  Below is a few KB's that do no justice to the problem but do offer a few tips.

http://support.microsoft.com/kb/225946/en-us
http://support.microsoft.com/kb/294755/en-us

After you backup your .mdb we need to find out if you .mdb has a corrupt project file and if so how bad it is.  Make sure you have that backup, you may find you will go back to it A LOT while trying to recreate a solid .mdb again.  Also keep close at hand any recent backups you have made to your .mdb (hopefully you have plenty to pick from.

After that try to decompile your Access .mdb.  For example, click Start, then Run and then enter (substitute "C:\Dev\YourApp.mdb" with the actual path and file name of your .mdb) ...

"C:\program files\Microsoft Office\OFFICE11\MSACCESS.EXE" "C:\Dev\YourApp.mdb" /Decompile

If that doesn not crash on you you are in luck, if not let me know and I will let you know where to go from there.
Assuming no crash, open the app and then open any module.  Select 'Debug' from the Pull down Menu then select 'Compile All Modules'.
If there are issues with the code will usually show up now, if not move on to a compact, repair and then compile a new .mde.  With any luck the new .mde will not have the same problems.

If in fact you have a corrupt project file this is going to be more complicated and there is no set answer to what you need to do to recover so if this turns out to be the case just be patient, I will ask a few questions to figure out how bad it is and what recovery options you still have.

Rick
0
 

Author Comment

by:rlwhitney
ID: 20373645
peter57r - sorry for the confusion. The error was in the MDB file. The existing MDE (made several days ago) runs fine. Corruption is in my development app (the MDB file). SP3 is on this machine, but it wasn't just installed; it's been there for awhile.

LSMConsulting - This was the development machine. The enduser machines only have the runtime. RE: references - the development PC had the references as of yesterday (I was working in the MDB most of the day). Can't get into VB Editor now to check reference listings, but no change that I know of would have caused file lose. And, just to be sure, I tried the MDB on the other machine in my office, which also has Access 2003; same error.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20373690
An inability to access the VB Editor is a tell tale sign of project file corruption.  If you haven't already backed that .mdb up do it now.  Once that is done a damage assessment and recovery plan can be figured out.  Hopefully you haven't password protected the VBA code less your options will be limited indeed.

Rick
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:rlwhitney
ID: 20373797
Rick Rickards:

Reviewed the KB articles. They both address earlier versions of Access, and as you commented, don't really do justice to the issue in Access 2003.

I've backed up the broken database, and am playing with a copy. With Access 2003, I've learned to backup often. If I can't recover this mdb, I've lost about 8 hours work - painful (who likes to code the same thing twice?) but not fatal.

When I try to decompile the database, I get the same error.

As a side comment, I worked with Access 2000 for years, and never had a corrupt database. Since going to Access 2003, I have it happen quite a bit (it has caused me to hone my backup skills, however). Just seems to me that Access 2003 is especially "fragile".

No password protection on the VBA.
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20374016
Project file was not solved in Access 2003, in fact I've found more bugs in 2003 than in 2000 so much so that I'm inclined to believe they created more bugs in 2003 than they fixed from their earlier versions 2000 and 2003.  As for Project File Corruption I can assure you they didn't fix that.   When I met with them on site back in 2005 they still had yet to see any one demonstrate how to reproduce Project File Corruption.  I solved that issue for them although I've yet to see the evidence that they ever fixed it, I can only say that Microsoft received step-by-step instructions on how to corrupt a project file 100% of the time.

Anyway, back to your problem, I hate to break it to you but everything I'm seeing screems Project File Corruption.  I can help but I'm going to have to ask a lot of questions so we can asertain what options are available to us and which ones or not.

1) Have you identified the most recent backup that does not have corruption in the project file.  An easy way to know for sure is to decompile the .mdb then compile the code then compact and repair.  If all steps succeed we have a good foundation to start with.  What we want to do right now is find the most recent copy you have in backup that is good.

2) Are you able to get to any VBA window?  For example, can you get to the code behind the form?  

3) Do you have a password protecting your VBA code?  If so any chance we had to get your code is probably lost

4) Please tell me you've made a backup of your .mdb as originally suggested.  The longer you wait the worse it gets.  .mdb's with corrupt project files self destruct the more you fuss with them so it won't be uncommon to save one piece and then have to start again from a fresh backup before we can save another.

Rick
0
 

Author Comment

by:rlwhitney
ID: 20374111
Rick,

The most recent working database backup is 11/28/07 @ 8:31 AM. I've confirmed that it's OK (can compact/decompile/compile, and it runs). Was able to create an MDE from that version. So my loss is only about 8 hours of coding. Thankfully, Microsoft's gift for making buggy development tools has made me an advocate of frequent backups. In this case, I should have backed up a few times during the day, but got sloppy. So I may be "Microscrewed" again. Anyway ...

I have renamed the corrupt database BROKEN.MDB, and have backed it up, also.

When I open BROKEN.MDB, I've been unable to open the VBA editor, or view any "code behind" form. I'm unable to compact or decompile it.

I haven't password-protected the VBA code.

And yes, I've got backups o'plenty (except for the most recent 8-hour effort).

Thanks!
0
 
LVL 16

Accepted Solution

by:
Rick_Rickards earned 500 total points
ID: 20374392
OK, that helps a lot to at least know where we stand.  So the worst-case scenario is that you've lost 8 hours worth of work.  Now we start the process of recovering as much of those 8 hours as possible without investing undue amounts of time trying.

1) What you have not lost: Even a corrupt project file will allow you to import tables, queries and macros.  Any work done here is not lost, you can import them into your good .mdb and be good to go.

2) What you might be able to save.  Any work done in laying out forms and reports can usually be copied and pasted from your corrupt .mdb to the good one.  Copy and Paste is your greatest friend and ally right now.  In most cases you can either get to all controls and properties within your forms and reports or at least most of them.  It's common to be unable to access one or two but usually not several unless you've been fussing with the corrupt file for a while (which is why we have the backup - you can keep going back to it as you discover the file falling apart).

3) Try and find any way you can into a VBA window.  If you can get in, regardless of how, you'll most likely be able to save most if not all of your code via cut and paste.  Try adding a new module, insert new class object if you can, try opening a form and trying to drop in via any of it's event properties (preferably one already in use).  If possible take an inventory of where code was written vs. where it was not so you'll know what work must be redone and what does not.

4) Things you need not waste your time with.  Any effort to export and/or import a form, report, module or class object will most certainly fail although it has a good chance of fouling up your already corrupt .mdb, it'll just be worse off than it was before you tried.  Again, Cut and paste controls, properties, values and take care to notice any event procedures that were in use and insure that they are flagged accordingly in your good .mdb

5) If you can get to a code window you're in luck.  Using the VBA explorer window you will most likely be able to get into most if not all your code and carry it from the corrupt .mdb to the new one using that good old cut and paste technique again.

For future reference:  Project File Corruption is one of the most serious problems I've seen Microsoft introduce into VBA since 2K.  There are things you can do to make yourself less venerable.  That neat new feature of being able to save some but not everything modified queries, forms, modules etc. is a perfect recipe for getting bit by project file corruption.  Unfortunately, if it asks you if you what you want to save, if you pick and choose you may find yourself here again.  The only way to be safe is to save everything or nothing.  Also, it is best not to open up multiple modules and class objects and edit them at the same time.  By opening each one up one at a time and saving them one at a time you'll be a lot safer.  Now for the bad part.  Even if you do everything right to avoid getting bit there is no guarantee that you won't end up here again so my best recommendation is to backup often while working on the development of your .mdb.  For myself I usually won't invest more than an hour into an .mdb without backing it up.  Backing it up can be made into a quick a simple task that takes little time and you'll be thanking yourself the next time your VBA project file corrupts, (and it almost certainly will - not so much a question of if, just when).

Rick
0
 

Author Comment

by:rlwhitney
ID: 20374527
Rick,

I'm able to grab the SQL code from a query, which saved me some time. And since my tables live in SQL Server 2000, I'm OK as far as my data. I can also open a report, and cut & paste. But I haven't been able to view code any way I've tried.

Long ago, I wrote a script which first backups the database with a timedate stamp in the name, then opens the file. So it's as easy to backup and open, as it is to open; I just got lazy and/or stupid, and am paying the price.

Thanks for all your help on this one ... I've already started re-doing the lost work.

Rob Whitney
0
 
LVL 16

Expert Comment

by:Rick_Rickards
ID: 20374690
You're most welcome.  Sorry for your loss but hope the above helped to mitigate the damage.

Rick
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now