Access 2007 .File Lost All VBA Code

Posted on 2009-02-12
Last Modified: 2012-08-13

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.
Question by:compsol1993
    LVL 44

    Expert Comment

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    Also, this bug list is continually updated ... and also leads to other ongoing lists:   'A2007 bug list


    Author Comment

    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?

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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...


    Author Comment

    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.
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    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:
            Note ... after the word Compile 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:

    LVL 74

    Accepted Solution


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

    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.

    Good luck!



    Author Closing Comment

    Very well explained, thanks for your time.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    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…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    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

    13 Experts available now in Live!

    Get 1:1 Help Now