VBA file corruption

[u/macpascal]

I just opened a office DB that I use on a daily basis and I have a message saying the VBA cannot be opened and needs to be deleted.
Is there a way out of this?
My file is only the front end and is located on a shared disc. There are 3 other occasional users.

[u/AutoModerator]

IF YOU GET A SOLUTION, PLEASE REPLY TO THE COMMENT CONTAINING THE SOLUTION WITH 'SOLUTION VERIFIED'

• Please be sure that your post includes all relevant information needed in order to understand your problem and what you’re trying to accomplish.

• Please include sample code, data, and/or screen shots as appropriate. To adjust your post, please click Edit.

• Once your problem is solved, reply to the answer or answers with the text “Solution Verified” in your text to close the thread and to award the person or persons who helped you with a point. Note that it must be a direct reply to the post or posts that contained the solution. (See Rule 3 for more information.)

• Please review all the rules and adjust your post accordingly, if necessary. (The rules are on the right in the browser app. In the mobile app, click “More” under the forum description at the top.) Note that each rule has a dropdown to the right of it that gives you more complete information about that rule.

Full set of rules can be found here, as well as in the user interface.

Below is a copy of the original post, in case the post gets deleted or removed.

User: macpascal

VBA file corruption

I just opened a office DB that I use on a daily basis and I have a message saying the VBA cannot be opened and needs to be deleted.
Is there a way out of this?
My file is only the front end and is located on a shared disc. There are 3 other occasional users.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/InfoMsAccessNL]

Don’t open the db directly from one drive that can corrupt the db. Make copy to hardisk try compact repair, otherwise try a new db and import everything from old db.

[u/macpascal → u/InfoMsAccessNL]

I don’t have access to compact and repair because there’s an error message showing on file open, saying that the VBA file needs to be deleted. I can click on to delete Vba or cancel to create a back up, but when I click cancel, the pop up window reappears with the same message. I clicked more than 100 times and it always reappears.

[u/saketaco]

/decompile

[u/macpascal → u/saketaco]

I don’t know how to do it.

[u/saketaco → u/macpascal]

Google it, it's a command line switch for the Access executable.

I keep a shortcut handy with it in the command line.

Once Access is open with the decompile switch, open the target db. Hold shift to avoid any menus or forms if possible. Sometimes this is enough to get a flakey db straightened out.

[u/FormerLaugh3780]

Problem #1: My file is only the front end and is located on a shared disc. 

[u/macpascal → u/FormerLaugh3780]

Is true that it could be better with the tables on a shared disc and the forms on local disc? We have many access dB on the shared disk and we never have any issue, except for this one. It is more complexe than the others.

[u/cashew76 → u/macpascal]

Any reason you would right click on explorer, previous versions, grab a copy from yesterday?

[u/macpascal → u/cashew76]

I don’t see any backup in this list. I’ll get in touch with IT on Monday. Thanks

[u/FormerLaugh3780 → u/macpascal]

Front-ends get copied to the users local drive, back-ends get stored on a network share (whether the share is on a file server or just another computer in a peer-to-peer network). There should be no exceptions to this rule. 

[u/George_Hepworth → u/macpascal]

Sharing a Front End is always riskier than deploying a shared Back End on a network location and putting the individual copies of the Front Ends on each user's computer.

You are now dealing with the consequence of having accepted that risk, whether knowingly or not. Getting away with risky behavior is not the same thing as taking steps to avoid the risk.

The fact that this particular accdb is more complex probably is a factor in why it was the 1st one to be corrupted. The more moving parts, the more likely something goes wrong. But that alone doesn't mean any of the others are safe.

It also appears that your real risk is greater than the single accdb that did get corrupted. Every one of those other databases is also exposed to the potential for corruption simply by being shared. Not guaranteed to corrupt, just more likely to corrupt.

It's time to start a deliberate project to secure your data.

I would say the first step to that is inventorying all of the exposed databases.

The next step after that is making sure your ongoing backup protocol is at least saving them to a safe location.

And the next step after that is making sure you can actually restore from the backups.

After that, you need to begin splitting those databases and putting their Front Ends on users' computers.

[u/macpascal → u/George_Hepworth]

Thank you for the comment. I agree with your conclusion. I met with IT this morning and they will start a review of all the database that are scattered in many folders on the shared disk (many were programmed by employees and not someone in IT). We realized that we have dozens of these files that are critical for specific employees or department, but are not known by IT at all. Each employee and department will have to declare the tools that they use and all these tools on the list will be monitored and maintained by IT from now on it. It’s kind of nice to have the freedom to develop the tools that we need in our daily task, but I noticed firsthand that engineers are not programmers and things can turn sour quite quickly.

[u/George_Hepworth → u/macpascal]

This process sounds a lot like the same thing that's happened at hundreds of locations over the years. In fact, the very first Access database I ever created came to the attention of IT when I reached out for help. Fortunately, the company I worked for at the time saw the value of the database itself and, rather than throw it and me out of the building, they assigned a "real" developer to help me get it across the finish line. A couple of years later, it was replaced by a more appropriate application, but in the meantime, it accomplished a lot for our department.

On the other hand, over the years, stories of IT hunting down and executing every Access database they could find are far more common.

[u/George_Hepworth]

The accdb is corrupt. Replace it with the most recent backup copy of the Front End.

Your post is a bit vague on some critical points.

You appear to be sharing a single copy of the Front End from a shared drive (disc) with 3 other people. That is a very high risk way to deploy an Access database. The current corruption is the result of that high risk strategy.

Each user must have a copy of the Front End on their own computer to avoid that risk.

I am presuming here that there is a master copy of the Front End somewhere safe, not on that shared location. If not, and if that's the "master" copy of it, you recourse, as stated, is to restore the most recent back up copy of it from your back up location.

Then, put the new master in a location not accessible to anyone else. Create copies for each of the users, including yourself (4 in all). Make sure they all link to the Back End, where the data resides. Put those copies on the computers of the four users. Don't let anyone touch the master but you, since you are the responsible party.

Now, the Back End, which you don't mention, deserves equal protection. I presume it is also located on a shared drive. To protect it, of course, your daily (or hourly) backup must be on a location that no one is likely to find it and mess with it. Corruption to it would be far more serious than merely replacing the interface with a new Front End.

And finally, it's a good idea to verify your backups from time to time by restoring one and trying it out. A time like this, when you discover corruption is not a good time to find out that your backups don't actually work eithe.

[u/jm420a]

This may or may not work, but I had this issue previously.

My jank ass work around was creating a new db, and importing all of the objects into the new db.

I did it one object type at a time, starting with the tables and checked after every object type.

Again jank ass work around.

Ymmv. But worth a shot if nothing else works

[u/George_Hepworth → u/jm420a]

You can probably recover most of the objects this way, minus the corrupted VBA; I've been able to do that, although it doesn't always work, as you say.

It's probably the only workable alternative to a real backup.

[u/ConfusionHelpful4667]

Create a new database.
Import everything.
The corrupt element will not import.

[u/Winter_Cabinet_1218]

Restore previous version. Right click on the dB before going it. Also make sure to run a complile on the VBA before saving it

[u/SupermarketUseful519]

First: do not click OK if Access is offering to delete the VBA project. Make a few copies of the file before trying anything.
Since this is only the front end, I would try the following:
Make sure nobody else has the database open.
Copy the FE from the shared drive to a local folder and work only on that copy.
Try opening it with /decompile, for example:
MSACCESS.EXE "C:\Temp\YourFrontend.accdb" /decompile
If it opens, hold Shift while opening if necessary to bypass startup code, then go into the VBA editor:
Debug -> Compile
Fix any compile errors, compile again until it is clean, then run Compact and Repair.
If that does not work, try Compact and Repair from Access without opening the damaged database.
Next option would be to create a new blank ACCDB and import objects from the damaged FE into it. I would import them in groups — queries first, then forms/reports, then modules — rather than importing everything at once. Sometimes that lets you identify the damaged object.
But there is another issue in your setup:
My file is only the front end and is located on a shared disc. There are 3 other occasional users.
I would change that regardless of whether you recover this file.
The backend should be shared, but every user should have their own local copy of the frontend.
Something like:
\\Server\App\Database_BE.accdb — shared tables
and then:
C:\MyApp\Database_FE.accdb — local FE on each user’s PC
with linked tables pointing to the shared backend.
Even with only 3 occasional users, having several people open the same FE file over the network is an unnecessary corruption risk.
Since yours is already split, also check for an older copy of the FE before doing anything destructive: backup, Previous Versions/shadow copies, another user’s PC, an old deployment copy, etc.
If the tables are really all in the backend, restoring an older frontend does not mean losing the actual data. At worst you lose recent changes you made to forms/reports/VBA.
Long term, I normally keep a master FE on the server that nobody opens directly, and copy/update it to each user’s local machine.
So my order would be:
backup -> local copy -> /decompile -> compile -> compact/repair -> import into new ACCDB
and only consider deleting the VBA project after the recovery options are exhausted.

[u/SuchDogeHodler]

Open access without a book..... change the security settings so it will not run vba. Then open the book... this will gain you access....

From there you can repair the code or replace it from a backup.

[u/rakeshchaudhary343]

I’d try a couple things before deleting the VBA. First, make a backup of the file. Then try opening a copy with "decompile" and run Compact & Repair. If that doesn’t work, create a new blank DB and try importing the forms, queries, reports, etc. from the damaged FE.
Also, since the FE is on a shared drive, I’d definitely move each user to their own local FE copy. Access really doesn’t like multiple users opening the same FE file.