Corrupt VBA Modules 1

[BikeToWork]

I support a networked fe/be Access 2007 application. There is never a problem with the backend but the frontend frequently gets corrupted. Multiple users share the same front end. Unfortunately there is no workaround for this since there is no way to access users' hard drives to copy the front end there. I suspect users simultaneously sharing the front end on a slow network share is the cause of the corruption. The end result is that the database gets corrupted at least once a week and one of two things happen:

1. The code in events disappears, so for example, clicking on a button does nothing because the on_click event is wiped out.

2. A message comes up about corrupt VBA modules and the database will not open.

What I'm wondering is if there is any way to trap for this corruption so that I could put in a vb script that would replace the database automatically if the modules are corrupt. I would use an Autoexec macro but this won't fire if the database won't open in the first place, which is often the case. Any help would be greatly appreciated.

 

[MajP]

Bottom line is multiple users in the same front end is a recipe for disaster. It is going to get corrupted and it is going to be slow. I would never ever employ an Access db that way. Can you explain why users can not have a copy of the front end? That does not make any sense to me. They are not allowed to have files on their computers?

 

[BikeToWork]

MajP, thankyou for the response. It would definitely be better to copy the front end to users' local hard drives and keep the back end on the network. I realize this but have no choice in the matter. Users' computers are locked down and they are not allowed to access their hard drives. I am not allowed to access them either. Their so called "desktop" is actually a network location. So, with this in mind, is their any way to "trap" for corrupt vba modules on start-up? The database usually does not open when the vba modules are corrupt. It displays an error message before the db opens and advises the user to back up the database. Is there any kind of command line open or vba to deal with this. All I do when the front end is corrupted is delete the corrupt version and replace it with a backup. I just wish I could automate the process somehow.

 

[PHV]

they are not allowed to access their hard drives
OK, but hopefully they have a "private" network location to store data, doesn't they ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BikeToWork]

I believe the user "desktop" is a private network location, but it is on the same slow network as the database front end. I don't think there would be any advantage to copying the front end to the users' "desktops." I don't suppose anyone knows of a way via command line switches or vba to trap for corruption. The corruption message comes up before the database opens so the autoexec macro is not an option.

 

[BobStubbs]

How do users start the database? Do they have a shortcut to click somewhere, or click on the front end file directly? Also, how many current users do you have?

I can think of a solution that I used once, involving multiple copies of the front end on the server, but while this is fine for up to (say) 10 concurrent users, if you are expecting hundreds (!) this would not work.

Bob Stubbs (London, UK)

 

[PHV]

I don't think there would be any advantage to copying the front end to the users' "desktops."
Just get rid of corruption, I guess ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BikeToWork]

Users typically start the database via a shortcut link to the front end. There are typically up to 10 users at a time in the database.

PHV, I'm not sure it is even possible to copy the front end to users' (network) desktops, since many of them access the database via VPN. If I could just somehow check for corruption and replace the front end if it is corrupted, that would probably be the best fix, however I'm not sure this can be done.

 

[Axworthy]

What I would do is have the users start the system using a vbs script that copies the front end to a new mdb (you could use their username as the name of the new front end) and then run that front end. Even if you do this in the same directory as the front end and back end, any corruption can be remedied by exiting out and restarting the system by running the vbs script.

 

[BikeToWork]

I can see how a vbscript could copy the front end (to the same folder as the production db) and append the username to it. How would you recommend dealing with cleanup, in terms of deleting the various username versions of the front end when the users exit the database.

 

[Axworthy]

I just leave them there. Maybe once every few months, I'll go in and delete them. I copy them into a subfolder and then run from there so every so often, I can just clear the folder.

 

[BobStubbs]

You can use a command file to create and delete the personalised front end databases. Here's an example:

@ECHO OFF
TITLE=Create copy of Database and run it for %USERNAME%
REM -------------------------------------------------
REM - If an old copy exists, delete it              -
REM -------------------------------------------------
IF EXIST <DBName>_%USERNAME%.MDB DEL <DBName>_%USERNAME%.MDB
REM -------------------------------------------------
REM - Create a personalised copy of the front end   -
REM -------------------------------------------------
COPY <DBName>.mdb <DBName>_%USERNAME%.MDB /V
REM -------------------------------------------------
REM - Open the database				    -
REM -------------------------------------------------
<DBName>_%USERNAME%.MDB
REM -------------------------------------------------
REM - Delete the personalised front end		    -
REM -------------------------------------------------
DEL <DBName>_%USERNAME%.MDB

Replace <DBName> with the name of your database front end file, without the .mdb extension.
Place the command file in the same folder as the "master" front end database
As each user runs the command file, a personalised copy of the front end is created with their network UserName included in the name.


Bob Stubbs (London, UK)

 

[BikeToWork]

BobStubbs, thanks a megabyte for the bat file. Thanks to others for their posts, too. Would the front end be less likely to become corrupt even though the user copy of it is running from the same slow network location, as opposed to multiple users sharing the same front end?

One question about the batch file - Wouldn't the last line attempt to delete the file after the user opened it?

 

[KiaruB]

Hello,
I administrate a access application with daily 50 up to 85 users at the same time.
The front is sitting on a shared server, so everyone uses the same front end.
For backend i use SQL server.
My aplication is debugged, compiled and compacted, all errors are captured with code, all access menu's + windows + options + special keys etc are disabled so no user can set any access options or reach any standard access menus and the app is packed in a mde.
The app runs for about 2 years now, it never crashes or never gets corrupted and its fast.

In other words, use SQL as dbase and close every gap in your front end and you should do just fine.

greets.

 

[BikeToWork]

KiaruB, it is impressive that you can have a shared networked Access front end application with up to 85 simultaneous users and no corruption issues. I have no such luck myself. In fact, the database I'm referring to in this post is one of several with a shared Access front end that regularly get corrupted. There seems to be no issue with the back ends of these databases. It is always the front end that goes belly up. These databases are on a slow network and yours is probably on a fast one. I suspect that makes a huge difference...

 

[BobStubbs]

Hi - the command file won't delete the "personalised" copy of the database until the user closes both the database and their MS Access application window.

When an application is run from a command file, command file execution pauses at the line that runs the application, until this closes.

By the way, I tested on Windows XP Professional - I have not seen anything to suggest that Access or command files behave differently in later versions of Windows.

Bob Stubbs (London, UK)