CSD - Kick Them Off

Here are some links and information concerning the topic of kicking off users of a database and detecting idle time. I do not have any personal experience with this task as of yet, but this information should hopefully be of help to you.

1. MVP Arvin Meyer has something here:
http://www.datastrat.com/Download2.html
Look for KickEmOff2K sample database.

2. http://www.rogersaccesslibrary.com/download2k.asp?SampleName=
'LogUsersOff2k.mdb'

3. http://www.rogersaccesslibrary.com/download2k.asp?SampleName=
'LogUsersOffNonUse2k.mdb'

4. ACC: How to Detect User Idle Time or Inactivity
http://support.microsoft.com/?id=128814

5. How to determine who is logged on to a database by using Microsoft Jet
UserRoster in Access 2000:
http://support.microsoft.com/?id=198755

6. How to determine who is logged on to a database by using Microsoft Jet
UserRoster in Access 2002 or in Access 2003:
http://support.microsoft.com/?id=285822

7. http://www.candace-tripp.com/_pages/access_downloads.asp
Look for Detect and Logoff Idle Users

8. http://propertychampion.com/Developer_Tools/Addins/Addins.html
Look for "Force User Out"

9. "Who's Logged In" 97 Sample Database:
http://www.rogersaccesslibrary.com/misc/misc.htm

10. http://www.fmsinc.com/products/Admin/index.asp

11. http://www.aylott.com.au/software.htm
Look for "Log Off Idle Users"

12. http://www.worksrite.com/
Click on the News link on the left side and then look for "Roster"

13. http://www.tek-tips.com/faqs.cfm?fid=1432

14. http://www.peterssoftware.com/isd.htm

15. http://www.endprod.com/db.htm
(Click on the “Access Samples” combo box on the right)

16. http://www.accessvba.com/forum/showthread.php?t=119

17. Auto Logout Users for DB Maintenance:
http://www.databasejournal.com/features/msaccess/article.php/3548586

18. Super Moderator of MDBMakers forums Bill Mosca has a sample file for forcing the database to close here:
http://www.mdbmakers.com/forums/showthread.php?t=19687
(Look for the file called ForceDbClose.zip)

19. Another sample file by Bill Mosca here:
http://www.mdbmakers.com/forums/showthread.php?t=119

20. http://www.resources.intuidev.com/
(Look for sample called “Network-Monitor”)

21. A sample file for study here:
http://www.access-programmers.co.uk/forums/showthread.php?t=103278

22. Auto Logout Users for DB Maintenance:
http://www.databasejournal.com/features/msaccess/article.php/3548586

23. Some code provided by MVP Arvin Meyer:

"There is no setting, but you can use a hidden form which tests activity every minute or so and will simply close the app when there is none. The form's TimerInterval is set to 60000 (1 minute). This form is visible and allows a user to move the mouse or keyboard over it and delay the closing by another 30 minutes. You could easily just remove that code, hide the form and quit the app after the first 30 minutes. BTW, the form opens with the app and has no way to close it without shutting down the app."

'**************Code Start*********************
Option Compare Database
Option Explicit

Dim lngActivityCounter As Long

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
     ' Reset the timer
     lngActivityCounter = 0
     Me.Doomsday.Caption = 15
     Me.Doomsday.Visible = False
End Sub

Private Sub Form_KeyPress(KeyAscii As Integer)
     ' Reset the timer
     lngActivityCounter = 0
     Me.Doomsday.Caption = 15
     Me.Doomsday.Visible = False
End Sub

Private Sub Form_Timer()
     'NOTE: The Timer Interval = 60000 (1 minute)
     'If no activity by user for 30 minutes, then quit application
    lngActivityCounter = lngActivityCounter + 1
     '30 minutes = 1800 sec (30 min x 60 sec/min)
     'After 30 minutes (1800 sec via: 20 min x 60 sec/min) of no activity,
     'close application completely
     If lngActivityCounter >= 30 Then
         Beep
         Me.Doomsday.Visible = True
             If Me!Doomsday.Caption = 0 Then
                DoCmd.Quit acQuitSaveAll
             Else
               ' This counts down 15 seconds before quiting
                Me!Doomsday.Caption = Me!Doomsday.Caption - 1
             End If
     End If
End Sub
'**************Code End*********************

24. And some ready-made code courtesy of MVP Ken Snell for
determining who is accessing the BE file:

'**************Code Start*********************

'*******************************************
'* Subroutine WhoIsInTheDatabaseLockFile *
'*******************************************

Public Sub WhoIsInTheDatabaseLockFile()
' Written by Ken Snell (January 31, 2005)

' *** OUTPUTS A LIST OF USERS IN THE DATABASE:
' *** 1. COMPUTER NAME ("COMPUTER NAME")
' *** 2. LOGON NAME ("LOGIN_NAME")
' *** 3. WHETHER USER IS STILL CONNECTED TO THE DB (USER ID
' *** REMAINS IN .LDB FILE UNTIL LAST USER EXITS OR
' *** UNTIL THE SLOT IS CLAIMED BY ANOTHER USER)
' *** ("CONNECTED")
' *** 4. WHETHER USER'S CONNECTION TERMINATED UNDER NORMAL
' *** CIRCUMSTANCES ("SUSPECT_STATE")
' *** ADAPTED FROM MICROSOFT KNOWLEDGE BASE ARTICLE 285822

Dim cn As New ADODB.Connection
Dim dbs As DAO.Database
Dim rs As New ADODB.Recordset
Dim strNewDataSource As String, strCNString As String
Dim strCurrConnectString As String

' Replace the string in the next step with the name of a real
' linked table in the database

Const strLinkedTableName As String = "Name_of_A_Linked_Table"
Const strDatabaseString As String = "DATABASE="
Const strDataSourceText As String = "Data Source="

On Error GoTo Err_Msg

strCurrConnectString = CurrentProject.Connection
strCNString = Mid(strCurrConnectString, InStr(strCurrConnectString, _
strDataSourceText) + Len(strDataSourceText))
strCNString = Left(strCNString, InStr(strCNString, ";") - 1)

Set dbs = CurrentDb

strNewDataSource = dbs.TableDefs(strLinkedTableName).Connect
strNewDataSource = Mid(strNewDataSource, InStr(strNewDataSource, _
strDatabaseString) + Len(strDatabaseString))

Debug.Print "File containing the data tables: " & strNewDataSource

cn.ConnectionString = Replace(strCurrConnectString, strCNString, _
strNewDataSource, 1, 1, vbTextCompare)

cn.Open

' The user roster is exposed as a provider-specific schema rowset
' in the Jet 4.0 OLE DB provider. You have to use a GUID to
' reference the schema, as provider-specific schemas are not
' listed in ADO's type library for schema rowsets

Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
, "{947bb102-5d43-11d1-bdbf-00c04fb92675}")

'Output the list of all users in the designated database.

Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
"", rs.Fields(2).Name, rs.Fields(3).Name

While Not rs.EOF

Debug.Print rs.Fields(0), rs.Fields(1), _
rs.Fields(2), rs.Fields(3)

rs.MoveNext

Wend

Exit_Sub:
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Msg:
Debug.Print "Error occurred. Error number " & Err.Number & ": " &
Err.Description
Resume Exit_Sub

End Sub

'**************Code End*********************

Top

This page was last updated on Sunday, September 19, 2010.