Question split database to avoid file corruption (1 Viewer)

[tommac]

Apologies if this is an old chestnut but I can't find any previous reference...

the books recommend always splitting networked access databases into front end/ back end versions to avoid data corruption if a client pc crashes.

However its a bit of a pain having to install the front end on many client pcs not to mention updating them when necessary.

My question is:
Is the back end data just as safe, if the front end is also on the shared server, perhaps in the same folder. The client pcs would then just have shortcuts to the front end. So only the front end on the server would need to be updated with newer versions.

thanks in anticipation

 

[pbaldy]

I wouldn't; you'll still get front end corruption and it increases network traffic. There are numerous ways of automating the updating of versions. I write my own using a table in the front end, but there's also:

http://thatlldoit.com/howtosarticles.aspx#fe_updater

 

[tommac]

Thanks for the suggestions. I'll certainly give them a try. The thing is I have a dozen or so small databases residing on a server and the users currently access them via shortcuts on their desktops, so I didn't think I would be increasing network traffic any more than it is now. The users currently have to pull the integrated front-end and the back-end down the network when they access a database. I haven't had any file corruption yet and some of these databases have been in use for years.... (fingers crossed), but I thought it might be an easy way to minimise the possiblity, if I just split the databases into separate front and back ends and kept both parts on the server.

Thanks again..

 

[gemma-the-husky]

one issue is whether your database makes changes to local tables in the front end.

if you do this, and different users share the same copy of the database, one user's work may interfere with another's.

i think it's issues like this that make people suggest it is better to make everyone have a different copy.

you can try it your way, but if you get into problems, you will know the suggested imporvement is to only have one user per database.

As far as backends go, yes you may still get corruptions. But in a corporate enviromment a backend on the server will (should) be backed up overnight, so there ought to be a recovery option that will not cause too much data loss.


It weasn't clear from your post whether you have split databases. You definitely ought to split data and code -it makes it easier to provide modification to the code portion, and it helps avoid any possibility of one user damaging data.

 

[Lightwave]

Tommac

I would suggest the following

Create a directory on your server for the front end and put the front end in their. Now create a small batch file and distribute this to every machine. Ensure that each machine has the same mapping to the network drive and put a shortcut to the bat file on each persons desktop.

Each batch file on everyone's desktop will have a variation of the following code.

@Echo Off
REM copy from network drive to local drive
xcopy "\\cc-edplan-01\PandIData\NetworkDatabases\SPaDE\autoupdate\SPADE_FE.mde" "C:\Program Files\BlueCoyote\SPaDE\SPADE_FE.mde" /Y /R /F
REM call your database file - Default Access program
Start "SPADE_FE.mde" "C:\Program Files\BlueCoyote\SPaDE\SPADE_FE.mde"
GOTO End

The above batch file copies the spade_fe.mde down from the server and places it into the c:\program files\bluecoyote\spade directory and then fires it up. You will need to adjust the unc path and the local path / database name according to your particular application.

Using the above users are always running a local copy but if you want all your users to use a new front end. You make your adjustments and then push it into the autoupdate directory on the server. Provided you overwrite the old mde with the exactly same named new file all your users will get the new file when they click on the bat file on their desktops. Works for me but not quite as elegant as the VB script check for updates idea.

In your case you could have a bat file for each application you have or you could have a batch file that updates all the applications.

Note Access 2003 at least doesn't seem to be able to link using UNC paths so all the user computers need to have the same name for their mapped drives. (that can be a bit of a pain sometimes but usually ok if you pick an obscure letter for the drive.)

 

[tommac]

Hi Lightwave,
I've used that suggestion in one locality, quite successfully. So thanks for that. I take gemma's point about local tables in the front end copies. I'd never considered that danger. Thanks again.

Cheers,
Tommac

 

[Galaxiom]

I take gemma's point about local tables in the front end copies. I'd never considered that danger.

I never write to tables in the front end because I consider it is an application and treat it as such. I use a Side End for local tables.

Google it on this site for previous discussion if interested.