Circumventingn exclusive locks

[TooGoon]

My company has several dbs which follow the normal model of being developed and released to the users. However there is a unique and important db which is under constant developement by its owner, and is viewed by several users. So whenever the owner makes a new report, which happens rather frequently, everyone else is locked out if they try and enter the db before she is finished.

We have just upgraded from Access 97 to Access 2003, so the exclusive lock issue is going to be a new, and rather annoying problem.

So, I am curious what sort of methods people have for dealing with databases that are being constantly modified by the users?

I am currently considering making the database dynamic, with generated queries being applied to default reports, but as that is a large undertaking, I'm keen on other suggestions.

Thanks.

 

[KornGeek]

You should split the database into a front-end and a back-end. The back-end data can live on a server so that everybody shares the same data. Each user will have a separate copy of the front-end interface installed locally on their computer.

When the owner wants to add a report or make other interface changes, only the owner's copy is locked. When the changes are done, simply distribute the new front-end to each user.

 

[TooGoon]

ok. But would you recommend that if the reports are published several times a day?

 

[KornGeek]

If you search this site, there are examples of ways to automate rolling out new versions of the front-end.

The general concept is to have a utility for a newer version available. If one is available, it copies it down over the top of the previous front-end. After checking (and copying if necessary), it launches the front-end. Have your users run this utility to start the database, rather than running the database directly.

Hope this helps.

 

[KornGeek]

Upon further consideration of your situation, I have a few comments:

1) I not only recommend splitting your database, but I <b>strongly</b> recommend it. If you are making these modification several times a day, that means your users are locked out several times a day. You will see a great increase in availability this way.

2) Because you mentioned that this is database is both unique and important, it is critical that you split it. Having multiple users logged in to the same database increases the chance of corruption. It is also vital that you make regular back ups to protect you "just in case". Murphy's law seems to dictate that the odds of a database needing to be restored from back up are much greater if there is no back up available.

3) Instead of rolling out new versions several times a day, it might be better to simply do a roll out once a day (or stretch it out to once a week if possible). If you have to do release more often than that, you should probably do more testing and/or requirements definition in advance.

I hope things are going well for you. Good luck.