VBA Module Corruption

[Jrbeene86]

Hey guys,



Question for a genius out there...

We have an access database that is on a shared drive that users from around the country can log into to input information. (However, there are never more than 3 people in at a time, including the user who works with the information.) However, every few weeks or someone will get this error message:

The VBA modules in this database appear to have been saved with errors. Access can recover the modules, but you should backup the database first. To cancel so you can make a backup copy of the database, click Cancel. Then make a copy of the database.


I have many backups and usually I will copy the data from the main table in the database and place it in a version that appears to be working fine, but then a week or two later the message will come back.

I have tried:

1. Copying and pasting each object into a new database. (Compacting and repairing after each one.)
2. Compiling the VBA code.

I do have an API call in there that I found online that determines the users login name. Could that be causing an issue? I posted the code below just in case. (I use this again on a different if that makes a difference as well.)


Dim ADSI As Object, un As Object
Set ADSI = CreateObject("ADSystemInfo")
Set un = GetObject("LDAP://" & ADSI.UserName)
adtest = un.FirstName
adtest = adtest & " " & un.LastName
Set un = Nothing
Set ADSI = Nothing
WhoamI.Value = adtest


I have read many STRONG suggestions about splitting the fe/be of the database, but since the users are across the country, I will not be able to install the front end. I have created a batch file to check a shared drive for a new version and copy a front end over and then open it, but I have heard from a colleague that not every user is able to run a batch file. (I"m not sure if this is true...but a lot of employees have their right click disabled etc.)


With only a 3 people going into the access database at one time, is it possible to avoid splitting the database and avoiding these errors? I know I have an easy fix now, but it is very embarassing for me.

Any ideas/criticism is welcome!! Thank you VERY VERY much in advance! I can upload this DB if needed!

[pbaldy]

I would add to the strong recommendations to split the db. If nothing else, all you'd have to do is replace the corrupted front end with a fresh copy. I'd have a copy for each user in a subfolder. I do basically the same thing in a Citrix environment. My version control utility puts a copy for the logged in user in their own folder:

C:\AccessApps\JohnDoe
C:\AccessApps\BillJones

That way every user is running their own copy. Are your users logging into a local machine, or?

[Jrbeene86]

Paul,

Thanks for the quick reply!

Yes, the users are logging into a local machine.

So I split the database and I have the front end in on the shared drive. If I were to create a shortcut to this folder and email that shortcut out to the users, then everything should work? Or do I really need to find a way to get it on everyones local machine?

Also, you said you have a version control utility that puts a copy for the logged in user in their own folder? This sounds very interesting...Any links to how I might be able to do do the same thing?

Thanks again!

[Jrbeene86]

Ok so I stopped trying to avoid the inevitable and created a batch file that will distribute the database to all the users. So far we have had no security issues.

Thanks for your help!

[pbaldy]

Glad you got it working.