Allow only one person to modify database at a time

[Keetso]

Hello.

I have a database that needs to be accessed at times by more than one person. In other words, Employee "A" opens the database. Since Employee "A" is the first person to open it, she/he would be able to add/delete/modify records as necessary. While Employee "A" has the database open, Employees "B" and "C" would have Read-Only rights.

Basically, I need to have a single database available to 3 users but only allow add/delete/modify to one person at a time.

Environment:

-Access 2000
-Win XP
-Database not split and located on a network share. All three users have full rights.
-"Default Open Mode" is set to "Exclusive"

Currently, with the above settings, ALL 3 users can open and modify records.

Can anyone tell me what I'm missing here?

TIA!

K

 

[mobyduck]

Do you really mean only one person at a time can modify the database or do you want to just lock each record as it's accessed.

If its the latter, then set the record lock in the form\data properties to edited record.

Hope this helps

MD

 

[Keetso]

Thanks for your quick reply, moby.

To clarify,

A single, stand alone database is located in a folder on Employee "A"s network share called, for lack of better words, "AccessDB".
Employee "A" has given full rights to the "AccessDB" folder to Employee "B" and "C". "B" and "C" has created shortcuts on their own desktops to this folder.

What they want to do is to allow only one of the three employees at a time to be able to add/modify/delete data.
If either of the other two (or both of them) opens the database while one is already in, they will only have read-only rights at that time.


For example:

Employee "A" - Opens DB first and is entering data
Employee "B" or "C" - Opens DB during this time but only
has read-only rights or is locked out

** 20 minutes later **

Employee "A" - Finishes, closes DB and informs Employee "B"
or "C" that they are finished
Employee "B" or "C" - Can then re-open the database and
add/delete/modify as necessary


I made sure that the "Default Open Mode" on the database is set to "Exclusive" but they are all still able to open the database and add/modify/delete data at the same time.

Hope this makes sense.
Please let me know if you need any add'l info.


K

 

[Keetso]

Hi.

Anyone have any ideas?

Anyone?

K

 

[alvechurchdata]

Anyone have any ideas?

If I had to do this I'd create a new sentinel table which just holds a single character field. Before User A starts to edit any data the form will open this table exclusive and put "User A" in the field. When User B comes along and starts to edit they too will try to open the sentinel table first but this will fail because of the exclusive lock. Your code will detect this error and open the main table read-only. Meanwhile you'll open a hidden form and go into a timer loop trying to open the sentinel table. When it succeeds you'll know that User A has finished and you can pop up a message to tell User B that they can now start to edit.

Having said that, I'd refuse the request if it were my job.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[psemianonymous]

Yeah. This is probably a flawed question - they shouldn't be asking you to do this. So here are some things I want to explain:

* Access has record-locking, so this isn't necessary. Read up on optimistic and pessimistic record locking (help files).

* Access allows you to open the file in "Exclusive Mode" - this is an option that allows one person to lock the database for their use only. All other users will be prompted, noting that the database is locked and that they will not be able to edit records. You can set this in Access (File->Open) and (my guess is) this is probably stored in the registry, so you can set it from there as well. Set everyone's machine/profile to try to open the db in Exclusive Mode, and you're done.

* Why is this a problem in the first place? This shouldn't be a problem. One of the awesome features about Access is that it warns the user when they're about to 'overwrite' someone else's changes. If your users are abusing this feature, then you need to get their manager to explain to them that this is not ok...I just don't see the problem.

* The 'lock the file in a table row "semaphore"' is a bad idea because the semaphore never unsets--i.e. if someone logs into the database and then rips the power cord out of the wall, the database will be 'locked' permanently until you fix it. Expect lots of support calls with this scheme.

 

[alvechurchdata]

rips the power cord out of the wall

You're right - I'd forgotten that scenario.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Keetso]

I certainly agree that it's not a huge issue by any means.

However, I had set the "Default Open Mode" (Tools - Options - Advanced) to "Exclusive" about 6 months ago and all was fine. Only the first person to open it could modify records. Recently I was informed that all 3 can now open and modify the database concurrently which isn't a problem by any scope.

Nothing had been changed either within the database or network. I'm now curious as to why the database is acting like it's in "Shared" mode even though the setting is still showing as "Exclusive".

Do you think it would pay to set the command line parameter to "/excl" (w/o the quotes)?

Thanks again for your help and input!

K