Solved How to check if a database is opened?

[KitaYama]

I'm looking for a reliable way to check if a given database is opened or not.
For now, I'm checking if the lock file exists or not. But it's not for sure. A database maybe closed and for a lot of reasons, the lock file is not deleted.

Thanks for any kind of advice.

 

[Uncle Gizmo]

I wonder, could you try deleting the log file? I'm assuming you won't be able to delete it if the given database is open?

So first step check if the log file exists, if it does, run code to delete it, check again and if it's gone then you know that the database is closed...

I have no idea if this is possible or not, it just seems a route that might work....

 

[The_Doc_Man]

You can try for a WinAPI function. This first link tries to open the file exclusively and if it fails, gives you a sharing error. The routine in question then returns either a true or a false Boolean for whether the file is open.

How to check if the file is open (Windows API)-VBForums

I am using API function to move files from multiple folder to one folder. B'coz file can be dropped constantly in their folders. How can i check if the file is open or not.. Thanks in advance

www.vbforums.com

You could also try to directly lock the file exclusively. Again, if it is open, you would get a sharing error because you can't take out an exclusive lock on an already opened file.

LockFileEx function (fileapi.h) - Win32 apps

Locks the specified file for exclusive access by the calling process. This function can operate either synchronously or asynchronously and can request either an exclusive or a shared lock.

learn.microsoft.com

A file has a set of usage locks associated with it. These usage locks can be "interest locks" (the mildest lock there is) all the way up to "exclusive write lock" (the strongest lock there is.) If you have the ability to connect to the file's lock information, you could determine how many users have taken out locks. If 0, the file should be free. The problem with this statement is that you have to be privileged in order to get to any information that would identify the other users of the file. (I.e. Run as Admin)

Note that for the two links, (a) either method needs to immediately be reversed if successful (so that YOU don't leave the file locked for others) and (b) if the file is not in use but its lock list has not been correctly reset due to any kind of network glitch or user procedural error, you will get a false positive indication that the file is in use. The only solutions for a false positive are to reboot the system that holds the file OR to wait for a system-wide timeout that causes idle locks to go away OR use admin privileges to try to manually unlock the file. The lock timeout depends on your system admin's choices for the corresponding system setting. The manual unlocking method requires you to be an Admin. The reboot can be done by anyone who has access to the power cord, but doing a brutal power shutdown has its own penalties from users who might come after you with pitchforks and lit torches.

 

[KitaYama]

Thanks for the quick response.
Will check both and see how it goes.

Thanks again.

 

[arnelgp]

If you can Open the Db in Exclusive Mode then nobody is using the db.

Public Function IsDbOpen(Byval strDBPath As String) As Boolean
Dim dbs As DAO.Database
If (Dir$(strDBPath)) = 0
    'the db does not exists
    'show message here
    Exit Function
End If
'Opendatabase(strThedbName, bolExclusive, bolReadOnly)
Set dbs = Opendatabase(strDBPath, True, False)
IsDbOpen = (Err = 0)
Err.Clear
Set dbs = Nothing
End Function

 

[KitaYama]

@Uncle Gizmo your assumption was correct. Your suggestion worked, but using DELETE, terrifies me (for no reason). Thanks for your solution.

@The_Doc_Man I used the code from your first link. That was perfect. Million thinks.

@arnelgp thanks for taking your time and sharing your solution
1- If (Dir$(strDBPath)) = 0 should be changed to If (Dir$(strDBPath)) = "". I received a type mismatch error.
2- If a the target database is already opened and I run your code, it stops on Set dbs=.... line with a 3706 code error. It doesn't continue running to return True.
3- If the target database is not opened, it always returns true.

I changed your code to the following to be able to use it.

Public Function IsDbOpen(ByVal strDBPath As String) As Boolean
    Dim dbs As DAO.Database
    On Error GoTo errortrap
    IsDbOpen = True
    If (Dir$(strDBPath)) = "" Then
        'the db does not exists
        'show message here
        Exit Function
    End If
    'Opendatabase(strThedbName, bolExclusive, bolReadOnly)
    Set dbs = OpenDatabase(strDBPath, True, False)
    IsDbOpen = Not (Err = 0)
errortrap:
    Err.Clear
    Set dbs = Nothing
End Function

 

[arnelgp]

If (Dir$(strDBPath)) = 0 should be changed to

i forget to add Len()


If Len(Dir$(strDBPath)) = 0

 

[isladogs]

Just to add that the presence of a lock file isn't by itself a foolproof test as no lock file is created when a database is opened exclusively.

 

[KitaYama]

Just to add that the presence of a lock file isn't by itself a foolproof test as no lock file is created when a database is opened exclusively.

Didn't know that. Thanks for mentioning it.

 

[KitaYama]

I just received a message from one of my colleagues that @arnelgp's solution to our question in following post covers this case too.

We had an assumption that Open Filename For Input can only be used for text files, zip or the kind of files that an external file can write to them.
Since we have already that function in our database, we're going to use it for above check too.
@arnelgp Million thanks again

Solved - Problem with attaching a report to a mail

I'm trying to 1- Export a report as pdf 2- zip and password protect the pdf, 3- Attach it to a mail 4- Send it to a customer. If I step into the code with F8, everything is just fine. Not even a single error. The task is done. But when I run the same code normally, the line that adds the...

www.access-programmers.co.uk