INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Want to open backend mdb in exclusive mode; is it possible?

Want to open backend mdb in exclusive mode; is it possible?

(OP)
Hi,

I understand this is possible in a SQL database. But at this time, it isn't possible to upsize the backend to a SQL DB.

Having said that:

Each user has their own copy of the FrontEnd DB connected to the backend mdb. Only 4 users have 'write' rights to the backend. One of those users is responsible for performing a monthly update to the data. When this happens, I think it would be best that none of the other 'write' users have shared access to the backend. The update procedure takes anywhere from 5 min to 20 min.

There are two components to the update procedure: 1) If a member is a new member, their demographic data is immediately entered into the live backend Members table (generally there are very few new members per month); 2) If the member is an existing member, then any changes to that member's data is first made to a local copy of the members table. At the end of the update procedure, the local table batch updates the backend table using an Update query.

Is it possible to allow the updating user to have exclusive access to the backend while the update is being performed?

Of course one obvious way is to tell the other 3 'write' users to stay out of the DB while the update is taking place.

Any insights are greatly appreciated.

Thanks,
Vic

RE: Want to open backend mdb in exclusive mode; is it possible?

VicM,

Don't even attempt to expect 'courtesy' from them.

You can:
1) Set a specific time that they MUST log out every day (and they won't - they'll forget, or get dragged away whilst logged-in).
2) You can email them (but, they aren't at their desks).
3) You can 'phone them (but, they aren't at their desks).
4) They COULD start editing a record, then walk away, and you are then at their mercy.

Boot 'em - no question (and yes, you CAN boot 'em mid-edit).

I do this for 100 users (following years of faffing - I recognise that 'courtesy' costs a lot of time - dispense with it and 'own' your application).

The front end has an hidden form that periodically 'polls' a table in the back-end, when a particular field (called say 'force_boot') is set to TRUE, the user is messaged to give them X minutes to save their work, then the front-end closes their application (it closes itself).

You cannot gain 'exclusive' access once even ONE, SINGLE user already has it open, and, once you've hunted THAT user down - another one has logged-in (and it makes no difference if you as admin are already logged-in - you have to log out, then back in to gain exclusivity).
Simple rule: if an LDB file exists - you cannot gain exclusivity.

For me, this was critical, as I update the db from a more 'business-critical' Oracle database. For reasons of MS Access 'bugginess', I was forced to 'drop' these tables and import all data during the table recreation step (rather than delete all records and simply import them). This meant that I was editing database table objects, (which can ONLY be done if opened exclusively).

If there is even a remote possibility that these 3/4 users may expand to many more, who won't be sat opposite you, my strong advice is to boot 'em.

I'm sure that you can work it out, but if you want more detail - let me know.

ATB,

D

RE: Want to open backend mdb in exclusive mode; is it possible?

(OP)
Darrylles,

Thanks for your insights.

I'm just a database consultant/programmer for the organization where the DB is used. But knowing the other 3 'write' users, I believe the user tasked to perform the update can request them to log out. (It's a small office.) And they'll honor the request.

Just wanted to make sure I wasn't bypassing something I didn't know about.

Vic

RE: Want to open backend mdb in exclusive mode; is it possible?

Vic,

Please remember - if you are simply updating data (not changing the design of objects) - you can do-so without having 'exclusivity'.
If another user has a record 'locked' - the process will wait until that user stores their changes, then continue - nothing will 'crash'.

ATB,

D

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close