如何避免Access数据库系统在一台电脑被多次重复打开（Multiple instances of accde file）

总结国外论坛 access-programmers的一篇文章。因为平台不允许发链接，所以大家直接用 Multiple instances of accde file 搜索这篇原文。技巧来自几位不同的作者。

一共有4种方法来实现，推荐最后面2种方法

方法一、创建一个本地lock文件来判断有否多次打开

Option Compare Database

Option Explicit

Function IsDatabaseAlreadyOpen() As Boolean

    ' Check if the lock file exists

    Dim lockFilePath As String

    lockFilePath = CurrentProject.Path & "database.lock"

   

    If Dir(lockFilePath) <> "" Then

        IsDatabaseAlreadyOpen = True

    Else

        IsDatabaseAlreadyOpen = False

    End If

End Function

Sub CreateLockFile()

    ' Create the lock file

    Dim lockFilePath As String

    lockFilePath = CurrentProject.Path & "database.lock"

   

    Dim lockFileNumber As Integer

    lockFileNumber = FreeFile()

    Open lockFilePath For Output As lockFileNumber

    Close lockFileNumber

End Sub

Sub DeleteLockFile()

    ' Delete the lock file

    Dim lockFilePath As String

    lockFilePath = CurrentProject.Path & "database.lock"

   

    If Dir(lockFilePath) <> "" Then

        Kill lockFilePath

    End If

End Sub

Private Sub Form_Load()

    ' Check if the database is already open

    If IsDatabaseAlreadyOpen() Then

        MsgBox "The database is already open.", vbInformation

        DoCmd.Close acForm, Me.Name

    Else

        ' Create the lock file

        CreateLockFile

        Me.cboPartNumber.Locked = False

        Me.cboPartNumber.BackColor = 16777215

        Debug.Print "Clearing Image"

        Me.Image0.Picture = ""

    End If

End Sub

Private Sub Form_Unload(Cancel As Integer)

    ' Delete the lock file when the form is closed

    DeleteLockFile

End Sub

方法二、判断Access自身的临时文件

If Len(currentproject.Path & "database.laccdb")>0 Then

   DoCmd.Quit

End If

方法三、使用Access命令行参数独占打开

 

如果您可以让用户通过链接(或者批处理文件)打开数据库，您可以使用Microsoft Access命令行开关“/excl”。

这将打开Access数据库进行独占访问。

一旦数据库以这种方式打开，用户就不能再次打开它，无论他尝试哪种方式，直到独占会话被关闭。

信息:如果Microsoft Access以独占方式打开数据库，则根本不会生成锁定文件。

方法四、使用winmgmts 判断进程数

我最终使用了下面的代码，这是我从几个地方搜集来的。再次感谢所有的反馈。

代码:

Function CountProcessInstances(process As String) As Integer

    Dim objList As Object

    Dim count As Integer

   

    Set objList = GetObject("winmgmts:") _

        .ExecQuery("select * from win32_process where name='" & process & "'")

   

    count = objList.count

    CountProcessInstances = count

End Function

Sub IsProcessRunning()

    Dim processName As String

    Dim processCount As Integer

   

    ' Checks if msaccess.exe is running

    processName = "msaccess.EXE"

    processCount = CountProcessInstances(processName)

   

    ' Checks if there are more than one instance of msaccess.exe running

    If processCount > 1 Then

        'MsgBox "The process " & processName & " is already running " & processCount & " times. Please close one of the instances before continuing.", vbExclamation

        MsgBox "The Database is already open. Please close one of the instances before continuing.", vbExclamation

        Application.Quit

    End If

End Sub

使用的解决方案是检查Access程序的多个实例，而不是特定数据库应用程序(ACCDE)的多个实例

 如果觉得文章对您有帮助，可收藏及关，，注下。