The amazing disappearing VBA code

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

 

[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

 

[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

 

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

 

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

 

[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

 

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

 

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

 

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

 

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

 

[Chuck]

We do disable the "Compact On Close" and the "Perform Name AutoCorrect"
options, however, I have to check on the "Compile On Demand". As far as
drive mapping problems over the last five years, how do you avoid it when
you have over a 1000 people on an installation that need files across a
network?