Seeing who's in the database

[Guest]

Is there a way to identify (presumably from the .ldb file) who's currently
logged into a database?

I have security set up on the database so can use currentuser().

I need exclusive access to make some fundamental changes to the backend of a
database and someone's ignoring my general requests to log-out!

Thanks

 

[Rick Brandt]

Is there a way to identify (presumably from the .ldb file) who's
currently logged into a database?

I have security set up on the database so can use currentuser().

I need exclusive access to make some fundamental changes to the
backend of a database and someone's ignoring my general requests to
log-out!

Thanks

You can open the LDB file with Notepad and you will see the all of the users who
have been in the file since the first person opened it. Doing that you will
only know that at least one of those users is still in the file because this
method doesn't purge people who leave the app until the last person closes the
file (at which point the LDB is deleted).

Microsoft has a free LDBViewer utility though that goes further and tells you
who is actually in the file *right now*. There is also a DLL that you can use
to determine this from within your own VBA code.

 

[Ken Snell [MVP]]

This subroutine will return the desired information to you in the Immediate
Window:

*******************************************
'* 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

 

[Guest]

Thanks very much Rick and Ken.

Your subroutine looks great Ken although haven't managed to get it to work
yet. I think it's because the paths to my databases are rather long and
strCurrConnectString is going over 255 characters.

Best wishes,
Martin

 

[Ken Snell [MVP]]

Note that ACCESS will run slower as you increase the length of the path to
the database file. Best if the file is in a root directory or in a parent
folder in a root directory.

--

Ken Snell
<MS ACCESS MVP>

 

[Tony Toews]

You can open the LDB file with Notepad and you will see the all of the users who
have been in the file since the first person opened it. Doing that you will
only know that at least one of those users is still in the file because this
method doesn't purge people who leave the app until the last person closes the
file (at which point the LDB is deleted).

I'd remove the word all in the first line and replace it with some.
Also the "slots" in the ldb file can get reused by the next person
into the MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm