Solved Multiple instances of accde file (1 Viewer)

[Poco_90]

Hi,
I am back again looking for some help.

Never encountered this before but have a new user that opened my database(accde) 10 times, and it made a bit of a mess of the data entry(nothing major and fixable). I would like to prevent this happening again. I have been trialing bits of code all day and can't seem to get anything to work in a satisfactory way. Below is the nearest to working. Works ok and you get a message saying the database is already open, but once you ok the message box, you are left with the database still open twice. The initial instance (displaying the main form) and the second instance with no forms displayed. Once close the second instance the lock file is deleted, which allows the database to be opened again without the warning leaving me with 2 instances open.

Is there a better way? Or should I quit all open instances of the application after ok'ing the message box?

Thanks in advance,

Poco.

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

 

[Gasman]

Just quit the app ?

 

[Poco_90]

I will, I am over thinking it. Thanks.

 

[theDBguy]

I will, I am over thinking it. Thanks.

Let us know how that goes. I'm not so sure that will be enough though. When you quit the app, it might delete the lock file which could allow another instance to open.

 

[LarryE]

Hi,
I am back again looking for some help.

Never encountered this before but have a new user that opened my database(accde) 10 times, and it made a bit of a mess of the data entry(nothing major and fixable). I would like to prevent this happening again. I have been trialing bits of code all day and can't seem to get anything to work in a satisfactory way. Below is the nearest to working. Works ok and you get a message saying the database is already open, but once you ok the message box, you are left with the database still open twice. The initial instance (displaying the main form) and the second instance with no forms displayed. Once close the second instance the lock file is deleted, which allows the database to be opened again without the warning leaving me with 2 instances open.

Is there a better way? Or should I quit all open instances of the application after ok'ing the message box?

Thanks in advance,

Poco.

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

If Len(currentproject.Path & "\database.laccdb")>0 Then
DoCmd.Quit
End If

 

[Pat Hartman]

I agree that you don't want to allow multiple instances to be open but why is that messing up your data? You need to add validation code to all your forms in the form's BeforeUpdate event to ensure that only valid data gets saved.

 

[AHeyne]

If it is possible for you to let your users open the database via a link (or maybe a batch file) you could use the Microsoft Access command line switch "/excl".
This will open the Access database for exclusive access.
Once the database is opened in this way a user can't open it a second time, no matter in which way he tries, until the exclusive session is closed.
Info: If Microsoft Access opens an database exclusive, no lock file will be generated at all.

 

[Poco_90]

Thank you for all the replies, I appreciate it.

My database is a packaging database, with each package getting a unique ID and 10 items scanned into each package against the ID. Even if you close the database midway through an order it will resume where it left off. I have no idea how the user was able to increment the ID when the previous package hadn't been fully filled. I tried, but cant replicate.

While quitting the process and opening the application exclusively worked, I needed something with feedback to the end users, as some people operating the database wouldn't be computer savvy.

I ended up using the code below, which I scavenged from several places. Thanks again for all the feedback.

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

 

[theDBguy]

Thank you for all the replies, I appreciate it.

My database is a packaging database, with each package getting a unique ID and 10 items scanned into each package against the ID. Even if you close the database midway through an order it will resume where it left off. I have no idea how the user was able to increment the ID when the previous package hadn't been fully filled. I tried, but cant replicate.

While quitting the process and opening the application exclusively worked, I needed something with feedback to the end users, as some people operating the database wouldn't be computer savvy.

I ended up using the code below, which I scavenged from several places. Thanks again for all the feedback.

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

Glad to hear you got it sorted out. It looks like the solution you decided to use checks for multiple instances of the Access program instead of the multiple instances of your specific database application (ACCDE). Hopefully, your users won't need to run different Access database applications at the same time with your packaging database. Good luck!