Temprarily locking out other users?

[Kim M.]

We are using an unsplit access 2007 database that resides on a server.

I'd like to prevent anyone else from logging on whenever an administrator is
making changes to the table of staff/users (for obvious reasons) -- about
once every few months. I have a "check for exclusive" barrier before
allowing the admin to even open the form, such that the form won't open if
anyone else is logged on. But my worry is that someone else will log on
after the form is opened and while the admin is in the process of making
changes to the data in the users table.

I know from experience that if I have made unsaved design/layout changes,
all other users are locked out until I save the changes. Is there a way to
mimic this sort of lockout behaviour whenever the form in question is open?

Hope I'm wording this right...
Thanks,
Kim M.

 

[Douglas J. Steele]

Split your database. Sharing an unsplit application is a disaster waiting to
happen.

 

[John W. Vinson]

But my worry is that someone else will log on
after the form is opened and while the admin is in the process of making
changes to the data in the users table.

Unless you're making changes to the *design structure* of the database I don't
see why this would be a problem. If you're just updating the users table,
other users will have a conflict only if they try to edit the record the
administrator is currently editing.

Making changes to the structure of the table, or of a form or report, is a
different issue.

I absolutely agree that the database should be split, but I don't see why
anything special is needed for this particular issue!

 

[Kim M.]

I have users log on whenever they access the database. Their user number and
initials are then stored as public variables the whole time they are logged
on, and are used for a variety of things. The user numbers are tied to staff
positions, so that if, say a person got promoted, their number would change.
I know this is unlikely to happen very often, but in theory could lead to
some unintended results if the admin makes changes to their status while they
are logged on.

Splitting will happen soon, but the design changes are still coming so fast
and furious that I have been reluctant to do it. Just curious, why it is so
important? We have six users and have not had any problems thus far (knock
wood!). And, other benefits aside, would splitting solve the issue I mention
here?

Thanks!!
Kim M.

 

[Piet Linden]

I have users log on whenever they access the database.  Their user number and
initials are then stored as public variables the whole time they are logged
on, and are used for a variety of things.  The user numbers are tied tostaff
positions, so that if, say a person got promoted, their number would change.  
I know this is unlikely to happen very often, but in theory could lead to
some unintended results if the admin makes changes to their status while they
are logged on.

This just sounds like a dodgy design... Why not use StaffID (linked to
*Person* not Position) to record who did what? Renumbering all the
time sounds like a problem waiting for a place to happen.

Splitting will happen soon, but the design changes are still coming so fast
and furious that I have been reluctant to do it.  Just curious, why it is so
important?  We have six users and have not had any problems thus far (knock
wood!).  And, other benefits aside, would splitting solve the issue I mention
here?

Thanks!!
Kim M.

Splitting... because otherwise, you send EVERYTHING over the network -
forms, code, all that static stuff that will never change. What's the
point? And what happens when the network hiccups? It blows out some
of your database. Not a pretty scene. Wait until you lose a chunk of
your data or your database... you won't be laughing anymore. I hope
you have a backup and a blank copy of your database... you might need
it soon. You're playing with fire.

 

[John W. Vinson]

Splitting will happen soon, but the design changes are still coming so fast
and furious that I have been reluctant to do it.

Frequent changes to the forms, reports and code? That's a major ADVANTAGE to
splitting, and a major disadvantage to a shared backend.

Changes to table structure? It's a hassle either way, split or unsplit.

Split. Now.

Just curious, why it is so
important?

Performance is better; the risk of database corruption goes way down; you have
the ability to work freely on your own copy of the frontend editing code,
macros, forms and reports (NONE of which you can do if anyone else is in the
database) and then distribute the new frontend when it's working...

We have six users and have not had any problems thus far (knock
wood!). And, other benefits aside, would splitting solve the issue I mention
here?

Yes. Just make the change. I think your concern about a user's privileges
changing during the minute or two that the admin is editing the data is
overblown - you only need to phone that user and ask her to close the
database. You don't need to kick everyone out.

 

[Kim M.]

Thanks guys -- I know you're right!

Kim M.

 

[Tony Toews [MVP]]

Splitting will happen soon, but the design changes are still coming so fast
and furious that I have been reluctant to do it. Just curious, why it is so
important? We have six users and have not had any problems thus far (knock
wood!). And, other benefits aside, would splitting solve the issue I mention
here?

Don't worry about the design changes coming fast and furious. I've
been known to "publish" updates once or twice a day. But then I'd
always make table changes after hours anyhow.

Note that you want to put the FE on each machine or place in a user
specific directory on the server. This will help avoid some weird
error messages when users are changing the same forms record source,
filters and such as well as corruptions. It is also much easier to
implement a new version of the database with changed queries, forms,
reports and VBA code.

I specifically created the Auto FE Updater utility so that I could
make changes to the FE MDE as often as I wanted and be quite confident
that the next time someone went to run the app that it would pull in
the latest version. For more info on the errors or the Auto FE
Updater utility see the free Auto FE Updater utility at
http://www.granite.ab.ca/access/autofe.htm at my website to keep the
FE on each PC up to date.

In a Terminal Server or Citrix environment the Auto FE Updater now
supports creating a directory named after the user on a server. Given
a choice put the FE on the Citrix server to reduce network traffic and
to avoid having to load objects over the network which can be somewhat
sluggish.

Tony