How can I determine if a database file is already open

[rednoise]

Hello,

I want to create a simple database to check if other databases files (accdr files in my case) are open and if not overwrite files from server.

I can always try to overwrite and supress any error but I like doing things in the proper way.

[isladogs]

Look for the presence of a laccdb lock file in the target folder. If it exists, the file is in use.
The only complication is if the file has been opened exclusively as that doesn't create a lock file.

[rednoise]

Yeah, I thought of that option but I really want to know if there is another/better way because sometimes there might be orphan laccdb files. I was wondering if there is an access native method to do it.

[isladogs]

Not that I'm aware of. You can always try and delete the lock file if it exists.
If the file is in use, you won't be able to delete it...so you'll need error handling to manage that.
Which brings us full circle...I would just handle the original error caused when you try and overwrite a file in use - IIRC it's error 70

[ArviLaanemets]

You can have for Main form an Open/Load event , which inserts user id (or some other identificator) into special table, and a Close/Unload event, which deletes user id (identificator) from this table. Then you can simply check the database using an ODBC query to check this file - when there are entries, then the database is in use.

[isladogs]

I was also going to mention that approach as I've used it in the past.
However, if the database crashes or the user shuts down their PC without closing the app first, the logout code won't run leaving an orphan record behind.

[ArviLaanemets]

Nobody is protected from user!

I have seen enough cases where lock file exists, and in reality nobody uses the database!

[Minty]

This code checks if an instance of the database is running; (I'll happily give credit where due for it if anyone knows the source - I found it somewhere on the interwebs)

Function IsDatabaseRunning(strDBName As String) As Boolean
    '   Function to check if a database is already running
    '   Accepts:
    '       The path and name of an Access database
    '   Returns:
    '       True if the database can't be opened (because it is already open)
    '       False if the database can be opened (because it is not already open)
    On Error GoTo E_Handle
    IsDatabaseRunning = True
    Dim db      As Database
    Set db = DBEngine(0).OpenDatabase(strDBName, True)
    IsDatabaseRunning = False
fExit:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Function
E_Handle:
    Select Case Err.Number
        Case 3704                                ' Database already opened
        Case 3045                                ' Database already open as well
        Case Else
            MsgBox "E_Handle  " & Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    End Select
    Resume fExit
End Function

[ArviLaanemets]

What when database is split (front- and back-end)? Any number of users can be connected to back-end, and I'm afraid it will not be locked? (But for front-end it will work for sure!)

And to add to confusion - what to do with back-end as e.g. SQL Server database?

[rednoise]

This code checks if an instance of the database is running; (I'll happily give credit where due for it if anyone knows the source - I found it somewhere on the interwebs)

Function IsDatabaseRunning(strDBName As String) As Boolean
    '   Function to check if a database is already running
    '   Accepts:
    '       The path and name of an Access database
    '   Returns:
    '       True if the database can't be opened (because it is already open)
    '       False if the database can be opened (because it is not already open)
    On Error GoTo E_Handle
    IsDatabaseRunning = True
    Dim db      As Database
    Set db = DBEngine(0).OpenDatabase(strDBName, True)
    IsDatabaseRunning = False
fExit:
    On Error Resume Next
    db.Close
    Set db = Nothing
    Exit Function
E_Handle:
    Select Case Err.Number
        Case 3704                                ' Database already opened
        Case 3045                                ' Database already open as well
        Case Else
            MsgBox "E_Handle  " & Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
    End Select
    Resume fExit
End Function

Thank you! it seems it's working fine.

ridders52's approach looks good too.

Thanks everyone for the answers.

[isladogs]

On behalf of us all, you're welcome.
Notice that the code Minty uses also depends on detecting errors.
Although you were keen to avoid that, it is a standard approach in this type of code.

[isladogs]

Just found another solution which I'd forgotten about and which requires no allowance for errors.

This checks whether its possible to open a database exclusively.
If the answer is no, its already open. If the answer is yes, its not open so is safe to replace
It works even if the database being checked is itself opened exclusively

Public Function CanBeOpenedExclusively(ByVal FullPath As String) As Boolean

On Error GoTo Err_Handler


    Dim D As DAO.Database
    Dim P As DAO.PrivDBEngine
    
    Set P = New PrivDBEngine
    On Error Resume Next
    Set D = P(0).OpenDatabase(FullPath, True)
    CanBeOpenedExclusively = Not (D Is Nothing)
    Debug.Print CanBeOpenedExclusively
    
    Set D = Nothing
    Set P = Nothing
    
Exit_Handler:
    Exit Function


Err_Handler:
    MsgBox "Error " & Err.Number & " in  CanBeOpenedExclusively procedure : " & Err.Description
    Resume Exit_Handler
    
End Function

Hope that helps