Database cannot be opened because the VBA project contained in it cannot be read.

[Katie Howard]

I have a split database on our network and lately (the past several weeks), it's been seemingly-randomly giving this error to users:
The database cannot be opened because the VBA project contained in it cannot be read. The database can be opened only if the VBA project is first deleted. Deleting the VBA project removes all code from the modules, forms and reports. and then it talks about making a backup.

The only way to get past the error is to click OK (cancel doesn't do anything), and then it opens the database without any code (so it's useless).

I've Googled this problem many times and came across this: http://support.microso ft.com/kb/2533794
I've decompiled, as it recommends, and then it works for another day or two before it breaks again and gives the same error. I've also decompiled, compact & repair, recompiled... tried various forms of that - but nothing works for very long.

Does anyone know of a long-term solution to this problem?

Thanks!!

[Seth Schrock]

I have had similar weird problems with Access 2010 (I'm not sure which version you are using). Sometimes I have been successful with just copying and pasting all the queries, forms, reports, etc. into another brand new database file.

If that doesn't work, then try copying one object at a time over till you find which object is causing the problem and then redo it from scratch.

Also, where is your front end stored?

[NeoPa]

Have you upgraded all your Access installs on all the PCs to the latest Service Pack. That's my reading of the linked article. Until this is finished these errors are likely to reccur.

[Katie Howard]

I'm running Access 2010, SP1. Our entire company was upgraded to this a couple months ago - which, ironically, is about the same time we started getting these errors (I was previously on Access 2007).

Both the back end and the front end are on our network. We have over 5,000 employees who need access to this database, so it just didn't make sense to try to get everyone to put the front end on their local computers.

I guess I'll have to try copying everything over into a new database, if you really think that will help. I hesitate because I have about 100 queries and another 30 or so forms and reports, and then some tables that are linked to the back end and other tables that are linked to a SQL database.

Thanks for your help.

[NeoPa]

What I understand from the article is that if the database is ever opened on any of the PCs where Access has not been upgraded to 2010 SP1 then this issue is likely to occur. I don't believe that the database itself needs changing necessarily (I see nothing to indicate that at least), and I can clearly see why upgrading 5,000 PCs at a time could be complicated, but I would at least consider blocking those PCs which are not yet upgraded from accessing that version of the database. Another version for those PCs may be in order until the full migration has been completed, but it seems mixing the old with the new on the same database is not a good mix.

[brianwatson]

Only compile the application on the server and not a stand alone PC. Different PC's can contain different attributes, due to this central compiling is essential.

[zmbd]

Do I understand this correctly:
You have 5000 users opening the same front-end file?
Or
Are each of the users downloading a copy of the front-end file to their local PC and running it from there?
-Z

[twinnyfo]

Katie,

Also, there is a way to install the FE to everyone's PC, and whenever there are changes, those updates are copied down to the local machine. If you get this error corrected, you may want to explore that option, as well, so that you don't have to worry about kicking 5,000 people out of the DB every time there is an update......

[zmbd]

One method would be to use a batch file that copies from a central store to the local pc: i.e. I have a "production " copy of the front-end located on the server. In that same folder is the batch file that deletes the current copy in the users profile and then copies the current version to the local drive. Once done, the batch file then launches the Access application. I then send a short-cut to each user, the short-cut points to the batch file.

I have also taken a look a some things that launch a Script shell and a batch file based in local version versis networked version that I may start using when I complete a current project; however, I havn't vetted them yet.

-z

[Katie Howard]

5,000 users (actually, now we're at about 5,800) have access to the same front end - but the vast majority don't use it; they just have to be able to open the file should there ever be a need for it.
I know that ideally we'd have the front end on each of their local machines, but I don't see that as realistic in this case, unfortunately.

I did create a 2nd front-end that our 3 heaviest users access, and that's significantly cut down on the frequency of these errors. It used to happen about 1-3 times a week, and since I created that 2nd front end, it only happens about 1 or 2 times a month.

[zmbd]

Do you want our help?

I did create a 2nd front-end that our 3 heaviest users access, and that's significantly cut down on the frequency of these errors. It used to happen about 1-3 times a week, and since I created that 2nd front end, it only happens about 1 or 2 times a month.

Katie, you prove our point!

You need to start deploying your front-end to the local user’s PCs as suggested by not only myself but also by Twinnyfo or use the advice that NeoPa has given.

As for the suggestion to only compile on the server… unless the installation you are using is actually running from the server (i.e. in the cloud)... you are compling at the local PC level! I often compile my front-ends on either the PC at my desk or on my PC at home (both stand-alone installations of Office). The most important thing is that the software the front-ends are compiled on are at the same revision level, so long as they are, I’ve never had any issues with where the front-end is located during compile. Nor have I had an issue with the files opening on the user’s local PC… so long as they are using the same revision level of the software.

Ok... now... I'm going to give you a VERY simple batch-file that will make distribution of your front end VERY doable... There is no error checking; however, I've used this template dozens of times without issues.

Now, normally, I don’t offer full code unless I've seen some work; however, in this case with so many users needing your DB and the fact that batch-files are nolonger used alot by the typical user, I will give you my version of the SIMPLE batch file that I use for small front ends:

Open your Notepad and copy the following:

@ECHO OFF
 rem Check for local file - create directory and copy file if not present. 
if not exist %userprofile%\z_db_applicationname\z_productiondatabase.mde mkdir %userprofile%\z_db_applicationname 

Rem updates local copy with server file with overwrite and no prompt
xcopy "\\UNCSERVER\uncfolder\uncsubfolder\z_ productiondatabase .mde" "%userprofile%\z_db_applicationname\ z_ productiondatabase .mde " /y /q 

rem start the application… give a user prompt if the shell sticks…
echo "CLICK ANY KEY TO START THE ACCESS PROGRAM"
START /I "MSAccess.exe" "%userprofile%\z_db_applicationname\ z_ productiondatabase .mde "

Now this is a template file so save this file with the name “Z_FrontEnd_Upd ate.TXT”

Now… make a copy of this text file!

Opps... one more step:
Create a new subfolder in the folder where your production database is kept. MOVE your production database to this folder…

Now back to the text file we just made:
Now in the copy of the text file you just created:
“\\UNCSERVER\un cfolder\uncsubf older” = You need to change this to the path to the location of the subfolder you just copied the production database to… I use UNC paths instead of drive letters… that way if the network drive is mapped to “H” on one PC and “X” on a different PC then it doesn’t matter as the batch file is looking for the “UNCSEVER” name… much like a website. If you can not determine the UNC path yourself... get your I.T. Magician to help.

“z_db_applicati onname” = This is a subfolder. I use separate subfolders to group my database work and I use the same name on the local PC so that it is easier to find in the code... I HIGHLY ADVISE that you do not use spaces, dashes, etc in your path and file names… USE only alpha-numerics and the the underscore! If you fail to do so, then you WILL make things like this batch file difficult to use.

“z_productionda tabase.mde” = Change this to your database name… I HIGHLY ADVISE that you do not use spaces, dashes, etc in your path and file names… USE only alpha-numerics and the the underscore! If you fail to do so, then you WILL make things like this batch file difficult to use.

Now save your changes… but use something like “Z_Launch_Appli cationName.CMD” and place this file in the folder where your production database WAS located.... that’s correct… was…. You do not want your users opening the front-end on the network anymore… so move it! Let the batch file do the opening from now on. ALSO Note… the extension must now be “CMD” not “TXT”

Now… send a short cut to the CMD file to your users. Personally, I send an Email in Outlook with a link to the batch file in the message and instructions as to how they can create their own shortcut for the batch file on their desktop.

From now on… every time they click on the shortcut/batch-file, it will run, copy the current front-end to the local PC and start Access using the local copy. It doesn’t matter at this point if you have 1 user or 10000 users; your users will never ever use the network front-end again unless they open it directly. AND BETTER YET… you can make a change, upload the changed front-end, and the next time the user clicks on the Z_Launch_Applic ationName.CMD file to open the database… they get a copy of the update.

You can also do something like this from VBA code in your front-end that checks for versions and so forth. I offer this solution as it doesn’t require VBA… just windows DOS.

There is just one issue... linked tables... make sure they are also using the UNC convention for file path. With the UNC, so long as they don't move or the server name doesn't change... you should be fine; however, if the server name is changed etc... you will have to relink. I have code in my front-ends that does this check and trys to relink... but that is a different thread! :)

-z

[NeoPa]

In view of what's transpired since my last comment, I would add my weight to the suggestion proposed (and a basic template provided for) by ZMBD here.

If you have more complicated needs for such a system you can post a new thread asking for assistance. Many of us have developed subsystems to handle such needs and I imagine are happy to share such with you. Obviously this goes for other members too. If anyone needs code/objects to help with automatic upgrading of FE databases in a network environment then I know I, for one, have a working set. I imagine others have too.