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

 

[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?

 

[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.

 

[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

 

[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).

 

[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

 

[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?

 

[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

 

[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 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.