Exclusive access to update etc

[pedroghuk]

Advice and direction required …

My User Account is part of the Admins Group. This Admins Group has Open/Run, Open Exclusive and Administer permissions to the Database and Administer privileges to all Object Types

Every so often, I need to tweak, update or develop the underlying Database objects. More often, other Users are accessing the Database and the usual record-locking information file is evident.

When I go into the back-end of the Database I am met with the message, “You do not have exclusive access to the Database at this time. If you proceed to make changes, you may not be able to save them later”.

Therefore, in the Database Window or in the object itself (e.g. Form), I go to ‘Tools’ -> ‘Options’ -> ‘Advanced’ and change Default open mode from ‘Shared’ to ‘Exclusive’.

If I make a change to an object etc and try to save, I get the message “You do not have exclusive access to the database. Your design changes cannot be saved at this time. Do you want to close without saving your changes?”.

Without advising Users to exit the Database or deleting their record-locking file, how can I update/amend the Database whilst Users are active in it?

Thanks.

 

[MStef]

Look at "Exclusive" (attachment, word, zip).
I think it can help you.

 

[pedroghuk]

Thanks MStef

The only issue is that I have a Security.mdw file in place which assigns Users to Groups.

A shortcut to this file is used for Users (and me) to log into the Database. Using HeadwayShortcutBuilder I effectively connected the Security.mdw file to the Database.

Using the abovementioned way to access the Database means I am not met with the Open Exclusive option you referred to in the Word doc.

 

[Pat Hartman]

You should NEVER be updating objects in a database that is opened by another user. As the developer, you need to maintain your own copy of the FE where you make changes and test them. When the changes are working, you can use your standard method to distribute copies of the FE to other users. As to the BE, that's a bigger problem. You should save your schema changes to off hours or make sure that everyone is out of the database while you are working. If you must work during business hours, get everyone out, then rename the BE so they can't get back in until you are done. I presume you back up before making any changes to the BE.

 

[asmith4]

while waiting until off hours to update makes sense, sometimes people leave the application open when they leave for the night. We still need to be able to kick people off.

 

[sparks80]

Hi,

There is a workaround for this, which requires a form to be loaded at startup.
This also requires a table (in the BE) with a single field, and single yes/no value which indicates whether users should be kicked out of the database.

E.g.
table name: tblLogOut
field name: ysnLogUsersOut
field value: true or false

Next you will need a form, say frmLogOutUsers that displays a message to the user indicating that the database will shortly close, and to save and changes to records they are working on.
Set the timer interval for this form to several minutes to allow users time to make any changes.

For the Timer Event enter the following code:

Private Sub Form_Timer()
  Application.Quit
End Sub

Now you will need a new form called something like frmLogOutMonitor. Set the timer interval for the form to say 30 seconds, and in the On Timer event write some code that checks the value in the log out table, and if the value is true then load the log out form

Private Sub Form_Timer()
  If dlookup("ysnLogUsersOut", "tblLogOut") Then
    DoCmd.OpenForm "frmLogOutUsers", acNormal, , , , acDialog
  End If
End Sub

Next we need to load the log out monitor form when the database is opened. For this we can use the On Load event for the startup form. We can open the form so that it is hidden, and just runs in the background.

Private Sub Form_Load()
      DoCmd.OpenForm "frmLogOutMonitor", acNormal, , , , acHidden
End Sub

Thus far I hope I have described a way of kicking users out of the database after a given period, allowing them to save changes before this happens.

The next step is to prevent users from loading the database if it is already closed.
For this we just need to modify the code for the On Load event of the startup form.

Private Sub Form_Load()
  If dlookup("ysnLogUsersOut", "tblLogOut") Then
    MsgBox "Sorry, the database is locked for editing at present. The database will now close", vbInformation
    Application.Quit
  Else
    DoCmd.OpenForm "frmLogOutMonitor", acNormal, , , , acHidden
  End If
End Sub

Please note that the advice from Pat Hartman is very important. It is much preferable to have a master copy of the FE database which you can edit, and then distribute to users. The above solution should work, but is far from ideal!

 

[pedroghuk]

Been away for a few days.

I will trial the Form at startup method later this week - it sounds a reasonable compromise and solution!

Thanks.

 

[pedroghuk]

Sparks

Illness and paternity leave have resulted in my absence.

Thank you for your advice.

I accept what Pat Hartman advises; all the while, I wanted to try out your suggested solution.

Please see that attached Test db.

Where is the problem?

In addition, do I assume that to turn on/off the ability to "kick Users out", do I just remove the Timer Interval on the frmLogOutMonitor form? Will I be able to do this if Users are working with the Database?

Feeling brain-dead today!

Regards.

 

[pedroghuk]

Thanks Sparks - please see my last post in this thread