[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1461
  • Last Modified:

Access 2007 .File Lost All VBA Code

Hello,

We have a basic Access 2007 application which polls data from a table and creates an Excel spreadsheet.  There are 4 tables, 1 form in the .accdb.

I tested the code on Tuesday and everything worked fine.  Today (Thursday) we had a user go log in and open the .accdb.  When he logged in, the buttons on the form did not work.  He alerted us and when we went in we found all of the VBA code was gone.  If I right-click on the button, and view properties, I can see that "OnClick" there is an evert procedure, but if I open the VBA editor, it opens a blank page and automatically inserts the Function header as if I were writing for the first time.

I have not been able to replicate this issue, I've tried varying levels of security, but the code remains intact in every case.

Is this a common issue with Access 2007?  We developed this app from scratch within 07, so it is not a migration issue from an earlier version.

My concern is that i do not want it to happen again, we had a backup but still there was a significant amount of time the user could not get in and do his work.

Thanks for any info.
0
compsol1993
Asked:
compsol1993
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Also, this bug list is continually updated ... and also leads to other ongoing lists:

http://allenbrowne.com/Access2007.html#Bugs   'A2007 bug list

mx
0
 
compsol1993Author Commented:
Thank you both for your feedback.  GRayL, we did not try creating a MDE.  DatabaseMX, thanks for the site, i looked through the list and did not find anything that seemed to explain my issue.

Does anyone else have any experience or suggestions as to what actually caused this?

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Jeffrey CoachmanCommented:
Then give us more info on the DB.

Was *All* the code gone (Code in Modules, Forms, Reports), or just certain code in certain objects?

All updates an applicable hotfixes installed?
Is the DB "Split".
Is the database set to "Compact on close"?

I'm just trying to think of anything that might cause something line this.

Let's see what the other experts might have to add...

JeffCoachman
0
 
compsol1993Author Commented:
boag2000, thanks for the reply.

Yes "All" code was gone.

The DB is not completely split, but does have linked tables to another DB.  There were no changes to the linked DB in this time.

The DB is not set to compact on close.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
oook ... lets try this. Be sure to make a backup first.  And also to set the correct  paths for you A2007 install (for the decompile command):

A **DeCompile** may help here ...

But first, if you have not already:
Open the VBA Editor and from the menu ...Tools>>References ....
If you see any listed as **Missing: <reference name>, including the asterisks and the word Missing, the
you need to fix that first.

Then, follow this procedure:

****
0) **Backup your MDB BEFORE running this procedure**
****
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
Close the mdb after the Compact & Repair.
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Close the mdb.
6) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

7) Close the mdb
8) Compact and Repair one more time.

*** Executing the DeCompile **EXAMPLE**:
Here is an **example** of the command line syntax  (be SURE to adjust your path and file name accordingly) before executing the decompile:

Run this from Start>>Run, enter the following command line - **all on one line** - it may appear like two lines here in the post:
Also, the double quotes are required.

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile "C:\Access2003Clients\YourMdbNameHERE.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx
0
 
Jeffrey CoachmanCommented:
compsol1993,

    "The DB is not completely split, "
Can you clarify?

In addition to MX post, just bear in mind that there is a school of thought that advises against Compacting the DB on close.

The reason is this.
Compacting the DB re-arranges DB file internally.
So if the file gets corrupted during the course of normal operations (Power Surge/Lag, system crash, ...ect), when it closes, it will be compacted, thus corrupting it further.

While this is rare, the possibility still exists.
See these links for more opinions on the subject:
http://bytes.com/groups/ms-access/191727-compact-compact-close
http://bytes.com/groups/ms-access/189035-compact-close-bad-idea


My thoughts?
On a big DB this (compacting) may take a while, if you try to reopen the db immediately after closing it, sometimes the DB is in a "Unstable state" where the ldb file might not be deleted, and you may get one of the many errors resulting from two instances of the database being opened at the same time. (Possibly causing *more* issues)
Is the DB growing excessively while you use it?
If the DB is split, then the front end really should not bloat all that much anyway.
...If so, then there are lots of "design" issues that can causes the bloating, (like recordsets being left open) Fixing these issues may minimize the bloating.
...If the DB is not bloating excessively, then why bother?
It's like changing the oil in your car after every trip.

If you want to go even further, you should Backup the DB, then Compact.
At least you will have a copy of the DB before the compact.

This all falls under the category of "Prevention"

As far as getting you code back, I believe MX has that covered.

There are also companies that claim that they *may* be able to recover lost code.
http://www.everythingaccess.com/
http://www.cimaware.com/main/products/accessfix.php

Good luck!

;-)

JeffCoachman
0
 
compsol1993Author Commented:
Very well explained, thanks for your time.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now