The amazing disappearing VBA code

[Chuck]

I have an MDB that was developed in Access 2003 and now running in Access
2007. It has not been converted to an ACCDB file format yet. The file is
around 30MB with over 40,000 records that are used daily by approximately
200 users across a network. It is not split into a back-end or front-end.
It is on a share network drive that everyone accesses. I cannot, for
several reasons, split it and put a front-end on all the users desktop
across the business.

The problem I am having since upgrading to Office 2007 is that for no
apparent reason, all the visual basic code vanishes. All the forms,
reports, queries and tables seem to be fine, but the code is missing. We
are running Office 2007 SP1 on all the computers. The database starts fine,
the main menu appears, but nothing can be done afterwards.

I first thought that nightly backups were corrupting it, but the start time
of those were moved to a later time just to see if it made a difference. I
put in a time-out procedure that if there is no activity at the user level
after 30 minutes, the database closes for that user. I was thinking that
maybe someone was rebooting the computer while a database was open.

Another thought was that there were network issues across the share drives,
but the network groups says that there is no indication of any problems.

I did not have this issue on Office 2003 and never once had to do a restore.
Now, on Office 2007, I am restoring the entire database and copying tables
at least once a week. We are planing on converting the database to the new
Access 2007 on May 11th to see if that helps.

Any ideas or suggestions would be very helpful. I am at a complete loss on
this one. Thanks.

 

[KC-Mass]

It is all attributable to Access 2007's enhanced(?) security features.
It''s default behavior is to shut off all VBA code and Macros.
Google these groups for "Trusted Locations", "Access 2007 security
features", etc.
There are solutions. Changing to ACCDB will not fix it.

Regards

Kevin

 

[Chuck]

I know about the security features. It does prompt the user to enable the
code and macros. It works when the user enables the code, but would it
actually delete or disable it for all users if the person selects the wrong
choice?

 

[KC-Mass]

It does not remove it but will disable it. The choice is offered to each
user.

 

[Tony Toews [MVP]]

It is not split into a back-end or front-end.
It is on a share network drive that everyone accesses. I cannot, for
several reasons, split it and put a front-end on all the users desktop
across the business.

Why not? You're in significant danger of corruption and wierdnesses
of the kind you describe.

Is it possible that some users are still using it on an older version
of Access?

Tony

 

[Chuck]

I cannot split it because we are a government agency that is told what to
put onto each desktop and we cannot change it. They consider placing a
front-end on each computer an "installed" program and it is not allowed.
Trust me... this has been an ongoing battle. They also will not allow a
trusted folder where I can put the database so each user does not have to
enable the code/macros each time.

I just put in the code to capture the Access version and build numbers as
part of the audit trail that is built into the database. We were thinking
along the same lines that maybe there is a rogue copy of an older version.
We recently installed SP1 so I don't know if there were any fixes for this
type of behavior. I don't know when SP2 will be pushed down to the
desktops.

I have searched the internet and stumbled across this same issue, but there
resolution didn't seem to fix the problem (decompile and recompile).

 

[Tony Toews [MVP]]

I cannot split it because we are a government agency that is told what to
put onto each desktop and we cannot change it. They consider placing a
front-end on each computer an "installed" program and it is not allowed.
Trust me... this has been an ongoing battle. They also will not allow a
trusted folder where I can put the database so each user does not have to
enable the code/macros each time.

Then I would strongly suggest changing employers. Those people are
utter idiots.

Hows about an alternative being that you give each user a copy of the
FE but it resides on a user named folder on the file server?
\\server\appname\frontends\%username%. The Auto FE updater handles
that situation very well. This solution does not require any
installing of anything on the client PC.

If they don't want to do the trusted location thing then tell them to
supply you with a certificate so you can sign the MDE.

I just put in the code to capture the Access version and build numbers as
part of the audit trail that is built into the database.

Yup, I've done that in the past. Also double check the ACE version.
Standard blurb follows.

What I've done is use the various API calls available and am checking
the version number and date/time of a crucial dll, msjetxx.dll, to
ensure it matches what I have on my system. See the Verify
Appropriate Jet Service Pack is installed page at my website for more
details including sample code:
www.granite.ab.ca\access\verifyjetsp.htm

Tony

 

[Chuck]

Thanks, I will look into the user level file server. I wouldn't say the
people that I work with are utter idiots, but it is one of those thoughts!
I really wonder who makes these decisions way up the chain of command, and
if they even know anything about computers and networking.

But the bottom line still is the same... what makes the VBA code just seem
to disappear?

 

[Tony Toews [MVP]]

Thanks, I will look into the user level file server. I wouldn't say the
people that I work with are utter idiots, but it is one of those thoughts!
I really wonder who makes these decisions way up the chain of command, and
if they even know anything about computers and networking.

But the bottom line still is the same... what makes the VBA code just seem
to disappear?

Likely because you're sharing the MDB amongst multiple users. Weird
things happen although yours isn't one of the usual weird things.

Tony

 

[David W. Fenton]

I wouldn't say the
people that I work with are utter idiots, but it is one of those
thoughts! I really wonder who makes these decisions way up the
chain of command, and if they even know anything about computers
and networking.

They either allow you to use Access apps, and then support them
being deployed properly, or they don't support the use of Access
apps at all. Right now, they seem to be supporting it, but won't let
you deploy them properly. That's completely idiocy, though it's
likely due to their obliviousness to how Access works.

 

[David H]

An Access .mdb qualifies as a file in the same way that an Excel Workbook is
a file, a Word document is a file, etc. There is NO installation neccessary
nor does running a mdb file on a desktop alter the machine's image. Running
it from the desktop is, for all purposes, exactly the same as running it from
the server. Access is NOT a client-server application.

Even if you can't add anything to the desktop, you can still split an Access
DB into a front-end and back-end and have both on the server.

Also, you can use VBScript to automatically copy the front end from the
server to the person's local machine in a temporary location to run it
locally. I did an implementation where my users used a VBScript sitting on
the server to automatically copy the front end to their machine and then
start it.

 

[Chuck]

I may end up splitting the file into a front end and back end on the same
share. Bottom line still remains the same.. what makes the VBA code
disappear? I know it sounds like a security mis-feature, but it doesn't
happen to other databases we are currently using. It is more of a curiosity
that anything else at this point.

 

[Tony Toews [MVP]]

I may end up splitting the file into a front end and back end on the same
share. Bottom line still remains the same.. what makes the VBA code
disappear? I know it sounds like a security mis-feature, but it doesn't
happen to other databases we are currently using. It is more of a curiosity
that anything else at this point.

We, or certainly I, don't have a good answer for you. This is quite
rare as I don't recall any other postings where the code completely
disappeared.

Tony

 

[Keith]

I'm getting the exact same strangeness.

This is a native Access 2007 file - it was developed in Access 2007, used by
15 or so folks across a network. File is only about 3.5MB when closed.

Every couple of days, all of the VBA code disappears.

This is NOT the trusted locations thing disabling macros - it's truly a
problem where all of the VBA code is removed from the project, and just the
tables/forms/queries remain.

Based on this thread, I'm going to divide into a front end/back end, but I'd
love to hear if anyone else is having this same issue and any possible root
causes.

-Keith

 

[Chuck]

I did discover today a new possibility on what is causing it.

Our ACCDB file is on a shared network drive. The drives are mapped for each
person as they logon to the computer.

The shortcut to the database is on each desktop, yet a lot of people attempt
to open the database prior to the mapping of the drives is complete.

Now you would think that Access would come back and indicate that the file
is not found, but it doesn't. It actually starts, gets to the user logon
screen for the database and then errors out. We then noticed that once the
drives are mapped completely, the database does not work. Upon looking at
the VBA of any form, all the code is missing. There is no way to recover it
except for the most recent backup.

If a person lets the drives get mapped after logon to the computer, the
database opens without a problem.

Of course I could put a check into the code to see if the drives are mapped,
but this doesn't prevent a person from still attempting to open before the
drive mapping is complete.

Very odd behavior. I never had this problem prior to Access 2007.

 

[Avril H]

We, or certainly I, don't have a good answer for you. This is quite
rare as I don't recall any other postings where the code completely
disappeared.

Tony
--

Its not as rare as you think. I run a development team writing custom apps
for external clients. We experience the problem described above with quite a
number of our Access 2007 apps (actually most of them), with varying
frequency. Some lose their code on a daily basis, others just occasionally.
We recover in the same way as Chuck - keep a few backup copies of the front
end app on the clients' servers to enable quick restore. Never had this issue
in version 2000 or 2003.
Chuck's comments on drive mapping - will check this out on our problem apps.

 

[Tony Toews [MVP]]

Its not as rare as you think. I run a development team writing custom apps
for external clients. We experience the problem described above with quite a
number of our Access 2007 apps (actually most of them), with varying
frequency. Some lose their code on a daily basis, others just occasionally.
We recover in the same way as Chuck - keep a few backup copies of the front
end app on the clients' servers to enable quick restore. Never had this issue
in version 2000 or 2003.

Chuck's comments on drive mapping - will check this out on our problem apps.

Please let us know.

Tony

 

[Chuck]

As far as the drive mapping, we tested it over and over and the database
does indeed get corrupted if a user attempts to open it prior to the drives
being mapped. Everything is fine, but the VBA code is missing so nothing
works. I have tried to decompile it at that point but that does nothing to
restore the code. The only way is to actually restore the entire database.
We then import the tables we need from the corrupted database into the newly
restored one and we are back to work. Unfortunately, it sometimes happens
on a shift that has no database support personnel so it is down from that
point until someone can get into work to repair it.

Oddly, which I cannot figure out is how it opens the first screen but yet
find no data, which would indicate that the drive has been mapped. We
recently created a small script that a user places on their desktop and uses
it to start the database. It checks for the presence of the mapped drive
and if it is there, the database starts. If not, it puts out a message for
the user to wait and try again in a few moments.

 

[Keith]

That is interesting. I split the database into a front end/back end a few
days ago, and it's still happening.

The nice thing about that is that we're not losing data anymore when it
goofs up, I just restore a copy of the front end (with shadow copy) and it
works again.

It's a real head-ache to do this a couple of times a week, but at least the
users aren't losing their work - it's just a pain for the IT staff.

I've never seen this behavior before, and although I don't write appliations
full time, I've done several access applications with significant code behind
them, and this is the first time I've experienced this.

This is a pretty frustrating bug - I actually can't believe more folks
aren't running into this.

 

[Fritz]

Same problem here. Happens every month or two. All code goes missing and I
need to restore from a backup. All data tables are fine, it's just the code
that disappears. One user reported the following message (but usually it
happens with no error message)

"The database cannot be opend 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 modules, forms and
reports. You should back up your database before attempting to open the
database and delete the VBA project.

To create a backup copy, click cancel and then make a backup copy of your
database. To open the database and delete the VBA project without creating a
backup copy, click OK."

If a user clicks ok, that would certainly cause the reported problem with
code disappearing!! I never had this problem with earlier versions of
Access, only 2007. From internet searches that I have done, we are not alone,
but I cant seem to find any solution.