Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Who's using the Database? (A2K SR2)

    Is there a way for me, as the Administrator, to determine who is using my unsecured, network-based database application at any given time?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Who's using the Database? (A2K SR2)

    In a nutshell, it isn't a trivial problem. One solution is to look at the lock file and see who has an entry in it - file name is XXXXXX.ldb and is always in the same directory as the XXXXXXX.mdb file. Microsoft has a downloadable utility to let you view it in a structured fashion, but you can view it in notepad or Word also. The problem is that no everyone showing there is still in the database. Generally the last entry is always there - unless their workstation crashed.

    Another approach if your server is running some version of NT is to use the Server function in the control panel and see who it thinks has the file open - not always terribly reliable, but... In 2000 Server, the Manage Resources has a facility to see who has what file open, but not for a file who has it open. Finally, the old app WinFile let you click on a file, do File Properties, and then see who had it open. It was always reliable either.

    In summary, no simple solution is available, but the combination beats walking around trying to find which user has a given database open. The life of the Administrator is never simple.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Who's using the Database? (A2K SR2)

    Thanks, Wendell.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Who's using the Database? (A2K SR2)

    One minor point - LDBView (I assume that's the downloadable utility you referred to) does indicate whether the user is still connected to the database. (I have no idea how reliable it is, but it hasn't "lied" to me yet).

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Who's using the Database? (A2K SR2)

    Here's a bit of code I got and modified from somewhere on MS web site. This lists the machine names that are running a process that has locked the db (that is, put an entry into .ldb file). If machine names are always used by specific people, you could do a lookup into a table that joined machine names to user names.

    <pre>Sub ShowUsers(sDatabaseName As String)
    Dim cn As New ADODB.Connection
    Dim cn2 As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim sTemp As String
    Dim sTemp2 As String

    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    On Error Resume Next
    cn.Open "Data Source=" & sDatabaseName
    If Err.Number <> 0 Then
    MsgBox "There is a problem opening the database [" & sDatabaseName & "]", _
    vbCritical
    Exit Sub
    End If

    On Error GoTo 0
    cn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & sDatabaseName

    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4 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 current database.
    sOutput = "Machine" & vbCrLf & vbCrLf

    While Not rs.EOF
    sTemp = Trim(rs.Fields(0))
    sTemp2 = Left(sTemp, Len(sTemp) - 1)
    sOutput = sOutput & sTemp2 & vbCrLf

    rs.MoveNext
    Wend

    'Display output
    MsgBox sOutput, vbInformation, "List of users"

    End Sub
    </pre>

    Edited to reduce horizontal scrolling--Charlotte

  6. #6
    New Lounger
    Join Date
    Feb 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Who's using the Database? (A2K SR2)

    Hi
    Go to www.fmsinc.com/free/utilities/index.html and download the Jet4Admin utility.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Who's using the Database? (A2K SR2)

    Just be aware that that tool is designed to administer database, so you can't just add one to it, take a peek, and then remove it again.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •