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

[VicM]

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

 

[Darrylles]

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

 

[VicM]

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

 

[Darrylles]

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