We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Access 2007 .File Lost All VBA Code

Medium Priority
1,721 Views
Last Modified: 2012-08-13
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.
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

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

Author

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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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

Author

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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
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

Author

Commented:
Very well explained, thanks for your time.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.