in message:
I have a linked table database and from time to time i need to perform
maintenance on the database but because people are commected to the table I
am unable to do this . Is there a way to kick off all users so I can perform
maintenance ?
Hi,
I don't have any personal experience with this task, but see if these links help.
Some of these are indirectly related, but should help I believe.
Watch out for any possible line wrapping on these links!
http://www.datastrat.com/Download2.html
Look for KickEmOff2K sample database.
http://www.rogersaccesslibrary.com/download2k.asp?SampleName='LogUsersOff2k.mdb'
http://www.rogersaccesslibrary.com/download2k.asp?SampleName='LogUsersOffNonUse2k.mdb'
http://support.microsoft.com/?id=198755
http://support.microsoft.com/?id=285822
http://www.candace-tripp.com/_pages/access_downloads.asp
Look for Detect and Logoff Idle Users
http://propertychampion.com/Developer_Tools/Addins/Addins.html
Look for "Force User Out"
"Who's Logged In" Sample Database:
http://www.rogersaccesslibrary.com/misc/Whoson.97
http://www.fmsinc.com/products/Admin/index.asp
And some ready-made code courtesy of MVP Ken Snell
for determining who is accessing the BE file:
*******************************************
'* 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*********************
Hope that helps,
