Messages and Closing a Network database

[BrianFawcett]

I maintain a database that is used by approximately 15 users on my team. Most of them use it for look up, only a few for entry and running reports.

My problem is that whenever I want to make changes (update forms, new reports, etc.) I have to find everyone who currnetly has the database open and them have them close it. I wanted to know if it is possible to do two things:

1.) Create a pop-up that would appear on the screen of everyone who has that db open asking them to please exit.

2.) Automatically closed the db on the PCs of all the users who have the db open.

 

[boblarson]

IMPORTANT DETAIL -

You need to SPLIT this database so that you have a backend with only tables on the server. Then you need to have a COPY of the FRONTEND, which has everything else, on EACH user's machine. Then you have no problem in modifying everything but tables. And then you can deploy the new frontend by many different methods including using auto updating which is available for free on my website. My auto update enabling tool will enable your database to make sure the users have the latest frontend.

As for tables, that is something you will need to work on when nobody is in the database and so for that you can use something like this:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=328&title=logusersoffmdb-intermediate

 

[CBrighton]

Yes, you can do both of these things.

However it would be better to split the database into a front end / back end setup (back end holds tables, stored on a network drive. front end holds everything else, copies stored on each workstation), that way people would only have to get out if tables are being changed.


If you want to add this anyway:

Form_Open event on the first form to open (switchboard / login form):

Private Sub Form_Open(Cancel As Integer)
    ' Set Count Down variable to false
    ' on the initial opening of the form.
    boolCountDown = False
    Dim strFileName As String
    strFileName = Dir("e:\Enabled.db")
    If strFileName <> "Enabled.db" Then
        MsgBox "Database being updated, please try again later."
        Application.Quit acQuitSaveAll
    End If
End Sub

Set the timer to "60000" so it refreshes each minute and add the following to the Form_Timer event:

Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("e:\Enabled.db")
    If boolCountDown = False Then
        ' Do nothing unless the check file is missing.
        If strFileName <> "Enabled.db" Then
            ' The check file is not found so
            ' set the count down variable to true and
            ' number of minutes until this session
            ' of Access will be shut down.
            boolCountDown = True
            intCountDownMinutes = 3
            GoTo Warningform
        End If
    Else
        ' Count down variable is true so warn
        ' the user that the application will be shut down
        ' in X number of minutes.  The number of minutes
        ' will be 1 less than the initial value of the
        ' intCountDownMinutes variable because the form timer
        ' event is set to fire every 60 seconds
        intCountDownMinutes = intCountDownMinutes - 1
Warningform:
        DoCmd.OpenForm "frmAppShutDownWarn"
        Forms!frmAppShutDownWarn!txtWarning = "Due to database maintenance this application will automatically shut down in approximately " & intCountDownMinutes & " minute(s).  Please save all work and close the database ASAP."
        If intCountDownMinutes < 1 Then
            ' Shut down Access if the countdown is zero,
            ' saving all work by default.
            Application.Quit acQuitSaveAll
        End If
    End If
Exit_Form_Timer:
    Exit Sub
Err_Form_Timer:
    Resume Next
End Sub

Then create a form called "frmAppShutDownWarn" with a textbox called "txtWarning" to hold the warning message for the users.



This code will prompt current users each minute giving them a 3 minute countdown warning. Anyone trying to open the database will be told it is unavailable.

The only problem with this is any user with a messagebox / inputbox on screen will have their database frozen and it will not effect them.