如何查看谁正在通过网络使用我的Access数据库? [英] How to see who is using my Access database over the network?

问题描述

我实际上有2个问题:

• 例如:有人打开了Access数据库，并创建了.ldb文件，我想看看谁打开了该数据库的列表(可能有多个人).

• 例如:我有10个不同的Access数据库，并且它们都使用相同的链接表.我想看看谁在使用该链接表.

我什至不知道这是否真的可能，但我非常感谢您的帮助！

I don't even know if it's really possible, but I really appreciate your help!

供您参考:主要问题是很多人在同一网络驱动器中使用相同的Access，因此当我需要更改它时，我不得不将它们全部淘汰，但我不知道实际上是谁在使用它.

For you information: The main problem is that lots of people use the same Access in the same network drive, so when I need to change it I have to kick them all out, but I never know who is actually using it.

推荐答案

更新:与其读取和解析.ldb/.lacdb文件相比，一种更好的方法是使用Access OLEDB提供程序的用户名册"功能如知识库文章中所述

Update: Rather than reading and parsing the .ldb/.lacdb file, a better approach would be to use the "User Roster" feature of the Access OLEDB provider as described in the Knowledge Base article

https://support.microsoft.com/en-us/kb/285822

以及另一个SO问题

通过VBA获取laccdb文件的内容

---

原始答案:

我前几天整理了以下内容.看起来很有希望，但是后来我发现断开连接后，计算机不会立即从锁定文件中删除.相反，Jet/ACE似乎(在内部)将它们标记为非活动状态:如果ComputerA断开连接，然后ComputerB连接，则ComputerB将覆盖锁定文件中ComputerA的条目.

I put together the following a while ago. It looked promising but then I discovered that computers are not immediately removed from the lock file when they disconnect. Instead, Jet/ACE seems to (internally) mark them as inactive: If ComputerA disconnects and then ComputerB connects, ComputerB overwrites ComputerA's entry in the lock file.

它仍然提供了种类列表.我将其张贴在这里，以防有人可以提出一些改进建议.

Still, it does provide a list of sorts. I'm posting it here in case somebody can offer some suggestions for refinement.

我在后端数据库中创建了两个表:

I created two tables in my back-end database:

Table: [CurrentConnections]
computerName  Text(255), Primary Key

Table: [ConnectionLog]
computerName  Text(255), Primary Key
userName      Text(255)

后端数据库中的VBA模块包含以下代码，以读取(副本)锁定文件并更新[CurrentConnections]表:

A VBA Module in my back-end database contained the following code to read (a copy of) the lock file and update the [CurrentConnections] table:

Public Sub GetCurrentlyConnectedMachines()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim fso As Object  '' FileSystemObject
    Dim lck As Object  '' ADODB.Stream
    Dim lockFileSpec As String, lockFileExt As String, tempFileSpec As String
    Dim buffer() As Byte

    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM CurrentConnections", dbFailOnError
    Set rst = cdb.OpenRecordset("SELECT computerName FROM CurrentConnections", dbOpenDynaset)

    lockFileSpec = Application.CurrentDb.Name
    If Right(lockFileSpec, 6) = ".accdb" Then
        lockFileExt = ".laccdb"
    Else
        lockFileExt = ".ldb"
    End If
    lockFileSpec = Left(lockFileSpec, InStrRev(lockFileSpec, ".", -1, vbBinaryCompare) - 1) & lockFileExt

    '' ADODB.Stream cannot open the lock file in-place, so copy it to %TEMP%
    Set fso = CreateObject("Scripting.FileSystemObject")  '' New FileSystemObject
    tempFileSpec = fso.GetSpecialFolder(2) & "\" & fso.GetTempName
    fso.CopyFile lockFileSpec, tempFileSpec, True

    Set lck = CreateObject("ADODB.Stream")  '' New ADODB.Stream
    lck.Type = 1  '' adTypeBinary
    lck.Open
    lck.LoadFromFile tempFileSpec
    Do While Not lck.EOS
        buffer = lck.Read(32)
        rst.AddNew
        rst!computerName = DecodeSZ(buffer)
        rst.Update
        buffer = lck.Read(32)  '' skip accessUserId, (almost) always "Admin"
    Loop
    lck.Close
    Set lck = Nothing
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
    fso.DeleteFile tempFileSpec
    Set fso = Nothing
End Sub

Private Function DecodeSZ(buf() As Byte) As String
    Dim b As Variant, rt As String
    rt = ""
    For Each b In buf
        If b = 0 Then
            Exit For  '' null terminates the string
        End If
        rt = rt & Chr(b)
    Next
    DecodeSZ = rt
End Function

前端数据库的Main_Menu形式的以下代码更新了[ConnectionLog]表

The following code in the Main_Menu form of the front-end database updated the [ConnectionLog] table

Private Sub Form_Load()
    Dim cdb As DAO.Database, rst As DAO.Recordset
    Dim wshNet As Object  '' WshNetwork

    Set wshNet = CreateObject("Wscript.Network")
    Set cdb = CurrentDb
    Set rst = cdb.OpenRecordset("SELECT * FROM ConnectionLog", dbOpenDynaset)
    rst.FindFirst "ComputerName=""" & wshNet.computerName & """"
    If rst.NoMatch Then
        rst.AddNew
        rst!computerName = wshNet.computerName
    Else
        rst.Edit
    End If
    rst!userName = wshNet.userName
    rst.Update
    Set wshNet = Nothing
End Sub

最后，后端数据库中的以下表格列出了[可能是对当前连接的最佳猜测]

Finally, the following form in the back-end database listed [its best guess at] the current connections

这是一个Record Source为

SELECT CurrentConnections.computerName, ConnectionLog.userName 
FROM CurrentConnections LEFT JOIN ConnectionLog 
    ON CurrentConnections.computerName = ConnectionLog.computerName 
ORDER BY ConnectionLog.userName; 

和后面的代码很简单

Private Sub Form_Load()
    UpdateFormData
End Sub

Private Sub cmdRefresh_Click()
    UpdateFormData
End Sub

Private Sub UpdateFormData()
    GetCurrentlyConnectedMachines
    Me.Requery
End Sub

这篇关于如何查看谁正在通过网络使用我的Access数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！