Auto Logout of my Database

[TBC]

I’ve been trying to get this Auto-Logout.mdb to work.

Problem I’m having is that it looks like it’s working on my end but doesn’t kick the users off, but it will close on my end.

What I’m expecting to happen:

If anyone is in a cretin database I should be able to open the database and click on the: Auto Logout Scan (Run Now) button. A popup for open at anyone’s computer that has the database open and warns then that it will be closing and then after 3 minutes it automatically closes all databases that are open.

What I’m seeing. I can open the database on my end and click the Auto Logout Scan (Run Now) button a popup will open every 30 -50 seconds warning me and then after 3 minutes it closes.

Can I please get some help either correcting my database of advice on a better way

Please see attachment

Thank you
TCB

 

[boblarson]

So do you have the table - tblVersionServer in the backend on the server and it LINKED to the frontend that everyone should have on their computer?

 

[TBC]

Maybe I missed that, so I need a front and back end to make this one work?

 

[boblarson]

Maybe I missed that, so I need a front and back end to make this one work?

If you have multiple users or the data needs to be on a network, the database NEEDS to be split or else you will be playing Russian Roulette with corruption of your data.

From what I see it should work even the way you have it now but the question is does code run on the other users' machines? Which version of Access are you using?

 

[TBC]

the version is office access 2003. Would you have another recommendation for a type of auto log off?

 

[boblarson]

the version is office access 2003. Would you have another recommendation for a type of auto log off?

No, I don't have another. As for 2003, it is possible your users have disabled code by clicking YES to the question of DO YOU WANT TO DISABLE UNSAFE CODE which puts it into sandbox mode. See if you can run any other code on their machine as well.

 

[TBC]

did you mean by saying have them run the code on there computer, just opend the attached database on thier system and see it if runs and then closes?

 

[CBrighton]

I haven't looked at your database, but I do a similar thing.

However, I do it via an external file.

My switchboard on timer event checks for the existance of a file every 60 seconds. If the file is not found it gives a 3 minute warning, the warning form is then reopened / refreshed each minute to give them a count down.

Because I use an external file name as the trigger every instance of the database (i.e. every user) will get closed as each of the databases always has the switchboard open.

If the suggestion is all you need then good luck, otherwise come back and I'll supply my code.

 

[TBC]

yes please can i see the code you put together

 

[CBrighton]

Code is all on the switchboard (Timer interval 60000):

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("[URL="file://\\Fillgpet03a005\D_credrisk0001$\Staff"]\\Fillgpet03a005\D_credrisk0001$\Staff[/URL] Database\Archive\Enabled.db")
    If strFileName <> "Enabled.db" Then
        MsgBox "Database being updated, please try again later.
        Application.Quit acQuitSaveAll
    End If
End Sub
 
Private Sub Form_Timer()
On Error GoTo Err_Form_Timer
    Dim strFileName As String
    strFileName = Dir("[URL="file://\\Fillgpet03a005\D_credrisk0001$\Staff"]\\Fillgpet03a005\D_credrisk0001$\Staff[/URL] Database\Archive\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

The filepath & name will obviouslly need to be changed, you will also need a form called "frmAppShutDownWarn" with a textbox control called "txtWarning".

Personally I also have a timer event on the warning form with an interval of 800 (and a hidden textbox called "Text2"):

Private Sub Form_Timer()
If Text2 = "1" Then
    Me.Detail.BackColor = 65535
    Text2 = "2"
Else
    Me.Detail.BackColor = 255
    Text2 = "1"
End If
End Sub

This causes the warning message to flash between 2 bright colours to ensure it's noticed.



The downside of timer events is they only work when Access is ready. If it's displaying a messagebox or an inputbox, etc then the timer event will not fire and the database will remain open for that user (although the event will start it's 3 min countdown when Access becomes ready if the filename differs).

 

[TBC]

Thanks CBrighton, I'll let you know how it works out for me

Thanks again

 

[TBC]

CBrighton – Do you have a example within a access database you can send me?

 

[TBC]

I have one database that on our server that 5 people login to. Sometimes throughout the month I need then to logout so I can add, update ect.

Is there a simple code or way I can log everyone that’s logged into my database. I’ve seen code that will log people off if the database is split; I’m not ready to split the database.

I was thinking sometime like creating a hidden form and then do something to make the database close.

Any help would be appreciated

 

[DCrake]

one approah I use is to have a text file sat in the same folder as the back end that contains a broadcast message. This file is either called "Lock.Txt" or "Free.Txt"

I have my main menu form (which is open thoughout the full session of the application) that has a timer on it. Currently set at five mins. So every 5 mins the form does a search on the folder for a file called "Lock.Txt" if it finds it runs a function that opens the text file and reads in the broadcast message and thows up a message box telling the user to log out. After the message box has been clicked it writes the name of the computer to another file using the name of the computer a the file name. if a second sweep of the folder finds this file it deletes the file and quits the database. This overrides the desire of the current user not wanting to log out.

Once all the users have logged out and I have done my jobs I simply rename the "Lock.Txt" to "Free.Txt" So when they log back in and the main form sweeps the folder the "Lock.Txt" is not found so it does not do the above.

Thats the basic concept there is more to it than what is described abive but the concept is correct.

 

[TBC]

DCrake, I think this is a great way to log people out of the database. Could you please provide more details so I can set this up on my db

 

[DCrake]

Do what you can yourself and then I will help you finish it off.

Tasks:
Use Open File for Output/Input as #1 to write/read to a text file

Use timer events on main menu

Use YesNo message boxes to confirm log out now actions

Use Environ() to get computer name/username

I do not have the code with me at the moment.

 

[CBrighton]

CBrighton – Do you have a example within a access database you can send me?

See attached.

It checks for c:\Enabled.db

If the file exists the database can be used, else it provides a 3 minute warning & closes the database for existing users and does not allow others to open the database.

 

[TBC]

Thanks everyone for the advice and help. Looks like I’ll need to wait until I split my database.

Thanks again
TCB

 

[CBrighton]

FYI the code in my example will work on an un-split database.

In fact, once you have a split database it's no longer needed as the front end can be replaced with the development version each time an update is done. There's no need to manually edit each front end.

 

[TBC]

CBrighton - This is aswsome. but I thing I'm soing something wrong, it only closes the database on my end and not other people that have it open. did I miss a step?

Thanks for your help