Comments on Determining If A db Is Open

[Deleted member 147267]

Looking for verification here.
I have been trying to help someone elsewhere with automation from Excel whereby the code opens a db and previews a report. Not too difficult it seems, until one raises the prospect that the particular db is already open. In searching this, I found lots of code examples - some dependent on it being in the Access side. Lots of it is gargantuan procedures using APIs of all sorts. I thought there had to be a simpler way, and came up with this:

OPTION EXPLICIT
Dim objAcc As New Access.Application

Function testDbReport()
On Error Resume Next

Set objAcc = GetObject(, "Access.Application")
'if err = 0 Access is running, so is the target db open?
If Err.Number = 0 Then
   If objAcc.CurrentDb.Name = "complete path here" Then
      MsgBox "The specified database is already open. You must close it and try again."
   End If
ElseIf Err.Number <> 0 Then 'if err, then Access was not open so load the db
   On Error GoTo ErrHandler
   With objAcc
      .OpenCurrentDatabase "complete path here"
      .Visible = True
      .DoCmd.OpenReport "rptReportName", acViewPreview
   End With
End If

exitHere:
Set objAcc = Nothing
Exit Function

ErrHandler:
If Err <> 2501 Then '2501 = the open report action was cancelled
  MsgBox Err.Description, vbExclamation
End If
Resume exitHere

End Function

From my limited testing it seems to work and figuring out if a db is open is only about 4 lines of code. Thus I'm looking for comments and verification on the truth of that, which is why I didn't post this in the repository. I could be way out to lunch given how complicated others seem to have made this. Remember that this for the Excel side of things. I would not have expected that to be important, but using non-API code developed for Access didn't seem to work from Excel (yes, the proper references were set). Granted, I did learn from this that simply omitting the comma (,) in GetObject changes everything, so theirs might have been faulty in some way.

If this should be posted in the repository after it has been vetted, then maybe it should be a much more condensed post. Comments?

 

[arnelgp]

comment:

it will only test open db on Local machine.

if already Open, User can just look at the taskbar or Task Manager.

if there are many dbs open at the same time on the Local machine, it will only get the Last db you have worked on. so your code will Fail if you are looking for a different db.name that is already Open.

the best way is through API window, each windows are assigned Unique handle.

 

[isladogs]

The easiest way is just to check for the existence of a lock file (.laccdb/.ldb) in the same folder as the database. For example

Dim strpath As String

strpath = "path to your database folder" & ".laccdb"

If Dir(strpath) <> "" Then
  MsgBox "Database is open"
Else
  'other code goes here
  MsgBox "Database closed"
End If

Of course, a lock file may be left behind if the database crashed.
You can test for that if necessary using the Kill command.

However if a database is opened exclusively there is no lock file.
That can be checked by trying to add or change a password in the database.
If already open exclusively that will be blocked.
I use code based on that idea in one of my security challenges

You can also do other checks such as whether a database has been opened using run as administrator switch or in runtime mode. I can post code for these later if anyone would find them useful

 

[theDBguy]

Hi. I also tried to do a little experiment this morning using the following code:

Public Function IsFileOpen(FileName As String) As Boolean
'thedbguy@gmail.com
'10/3/2019

Dim boolResult As Boolean
Dim app As Object

On Error GoTo errHandler

Set app = GetObject(FileName)

If app Is Nothing Then
    boolResult = False
Else
    'app.Quit
    boolResult = True
End If

errExit:
    IsFileOpen = boolResult
    Set app = Nothing
    Exit Function
    
errHandler:
    Resume errExit
    Resume
    
End Function

However, I found out that there are a few side effects to this version. First, it will return True if the file you're trying to check is already open, no matter if it's the last one opened or not. However, if the file is not open, this code will try to open it, which probably defeats the purpose of what you're trying to achieve. I added a line to close the opened instance, but commented it out because for a generic use of this type of code, we won't want to close an existing session if the user still needs it. Hope it gives you additional information to get to where you need to go. Good luck!

 

[jdraw]

Here's a link from 2016 re Is database open.

 

[Deleted member 147267]

comment:
it will only test open db on Local machine.

True. I failed to post that this exercise was limited to that situation because it was about doing a report preview and nothing else.


if already Open, User can just look at the taskbar or Task Manager.
True, but you'd have to activate it to see what db it is. I think this was to be a repetitive thing (many reports to preview) so possibly many to activate


if there are many dbs open at the same time on the Local machine, it will only get the Last db you have worked on. so your code will Fail if you are looking for a different db.name that is already Open.
I will have to test that. I admitted that I did limited testing (was late last night).


the best way is through API window, each windows are assigned Unique handle.

Thanks for commenting.

 

[Deleted member 147267]

Of course, a lock file may be left behind if the database crashed.
You can test for that if necessary using the Kill command.

However if a database is opened exclusively there is no lock file.
That can be checked by trying to add or change a password in the database.
If already open exclusively that will be blocked.

Maybe I'll play with some of those, but is a password the only change you can attempt to make? Would not want to add a password property and code fails to remove it.

 

[Deleted member 147267]

However, if the file is not open, this code will try to open it, which probably defeats the purpose of what you're trying to achieve.

I believe you discovered the same effect of the missing comma in GetObject that I mentioned.

 

[theDBguy]

I believe you discovered the same effect of the missing comma in GetObject that I mentioned.

Hi. I'm not sure if it's the same. What exactly did you discover that was different when you omit the comma in your code? In my version, the comma is not required. In yours, it was.

 

[Deleted member 147267]

Here's a link from 2016 re Is database open.

Saw that. Didn't make sense to have code to test if the current db was already open.

to check if the current db is already open:

How can CurrentDb not be the one you and the code are in??

 

[isladogs]

Maybe I'll play with some of those, but is a password the only change you can attempt to make? Would not want to add a password property and code fails to remove it.

I used that as a method of checking if a database had been tampered with by a hacker. It may not be the only property that could be used for testing for exclusivity. In general, people only tend to open an app exclusively when modifying / setting a password.
In any case, if code is able to add/change a password as a check, its equally capable of reversing it immediately afterwards

 

[Deleted member 147267]

Hi. I'm not sure if it's the same. What exactly did you discover that was different when you omit the comma in your code? In my version, the comma is not required. In yours, it was.

It opened regardless, plus Is Nothing was always False and Error was always 0. When I placed the comma and omitted the path, it worked inasmuch as I tested it (only as far as having one Access file open).

 

[CJ_London]

How can CurrentDb not be the one you and the code are in??

sure it doesn't apply in this case - but for an access library file currentdb used in the library file refers to the db that has that library file as a reference, whilst codedb refers to the library file

 

[arnelgp]

this experiment is for opening report in access..
from my db, on my computer, on another vba capable app, hmmm...
it's kinda shortcut.

 

[Deleted member 147267]

this experiment is for opening report in access..
from my db, on my computer, on another vba capable app, hmmm...

Not quite, unless I've misunderstood the comment. From Excel, not from any database. That was stated in my original post. The procedure is Automation of Access from Excel. I never asked why as the intent of my thread was to solicit comments on what I thought might have been a simpler method than API calls and such - at least for the particular situation. And that might be the crux of it - it's only good enough for that situation, assuming it's good enough at all. I won't have the time to test for multiple Access files (same one or not) until much later today.

 

[isladogs]

Building on my original suggestions. see attached for an Access utility which
checks whether an external LOCAL database is running by checking the following in turn:

1. Is Access running? (this would only be useful if code run from Excel etc)
2. Does file exist at specified path and is it an Access database?
3. Does a lock file exist?
a) If yes, can it be deleted?
YES - database is not running
NO - database in use
b) If not, check if database is open exclusively
YES - database open exclusive
NO - database is not running

The code should work for all types of Access database (.accd* & .md*)
It does not open the external database during the checks.

I've successfully tested it on various local databases
However, some changes would be needed to deal with file/path errors for networked databases

The code would also need modifying to be run from Excel etc but before attempting to do so, please can others test and provide feedback

 

[Deleted member 147267]

to whom it may concern:
I've had to park this and the thread in code repository about randomizing data until things slow down a bit. You might see me peeking in on not-so-complicated issues but not on anything that I figure will take any concerted effort.

 

[The_Doc_Man]

Colin, before I start this, I want you to know this is NOT a knock on anything you have done, but rather is a commentary on how difficult it is to answer the REAL question of specifically whether Access has the DB open. Thinking about this, the problem is more complex than it might first appear. You can make some inferences, but limitations exist in proving the point.

The problem is task-memory isolation. You cannot see what task owns a particular data structure unless you are running as an Admin who can look at physical memory structures. Therefore, the only definitive way to know that Access is running XYZ.ACCDB is to look at the file handles to determine which task owns the handle on that database. This is a privileged API function. If you don't have that level of access to Windows internal structures, you can't answer the question definitively. Anything else will have to be based on inferential tests if you don't have adequate privilege to examine system structures through the Win32 API or the 64-bit equivalents. If the file in question is a back-end and you are running your tests from the front-end side of things, you might need a higher level of admin rights, like domain admin or network admin since at that point, the lock structures aren't on the same machine you are on.

Your test "Is Access running?" would clearly answer the question as "NO" if Access is not running. As you point out, running the test code from Access defeats the inference. But in a network environment, your "NO" answer must be read as "not on this machine" but that answer isn't sufficient to prove the point.

The test "Is there such a file and is it of the right type?" would clearly answer the question as "NO" if there is no such file. It is clearly valid network-wide if your file spec was network-aware and specific.

The test "Is there a lock file?" might work, but don't forget that the database can be opened read-only if the opener can't create a lock file (e.g. due to permission issues). The absence of a lock file doesn't definitively say that the DB isn't open. It just assures that the DB isn't open for full service if that exceptional case applies. And I believe that an exclusively-opened DB doesn't open a lock file either, but that would be a full-service opening. Your side comments seem to bear this out though I don't recall having actually tried this recently enough to recall the answer.

The test "Can the lock file be deleted?" fails if the read-only exception or the exclusive-open exclusion is under way because there would be no lock file, yet the DB would be open in some way.

Crossing the network adds in the complication of whether you can even see the session of Access that has the file open if the target file is a shared back-end. This situation would fail the "Is Access open?" test if the FE is open on another desktop, because you can't query the task list of another machine unless you are logged in on that other machine. From the viewpoint of the machine owning the target file, you can't see what task is running across the network to get to that file. Further, in a domain environment, network permissions will often block you from being able to see any info about other users anyway.

I have no simple answer for this one that doesn't involve being an admin (or worse, a network-level or domain-level admin). However, let's just say that the more complex the network environment, the harder this question will be to answer.

 

[isladogs]

Doc
Before I reply, I also want you to know that this isn't a jibe at your lengthy reply.
Your explanation was mainly just spelling out why I did all of those steps in turn rather than just checking for a lock file.

However, you did get your facts scrambled with regard to lock files
- opening read only does create a lock file so no need to test separately for that condition
- opening the app exclusively does NOT create a lock file so this condition does need to be tested

Some further comments:

1. Is Access running?
I only included it for anyone (such as Micron) who wanted to try running the code from Excel
2. Does file exist at specified path and is it an Access database?
Self explanatory
3. Does a lock file exist?
a) If yes, can it be deleted?
In 'normal use', a lock file is created though an orphan lock file can be left behind if the app crashes or is closed incorrectly
The lock file can be deleted if the app isn't in use

b) If not, check if database is open exclusively
As stated above, no lock file is created if a database is opened exclusively.
To test for this we check whether or not the app can be opened exclusively!
If it cannot, the app was already open exclusive

If the database is split, it makes more sense to test whether the BE is open.
However as we both pointed out, testing network files brings up issues of file & folder permissions (error 75)

However this can often be overcome by running Access as an administrator.
Doing that allowed me to check whether an app was running on a different workstation on my network.

However, that won't help if the network permissions block that kind of access.

Updated version 1.1 attached

On a related note, if you regularly need to open Access exclusively, in runtime mode or read only etc, you may find this Explorer right click context menu useful:

You can get this context menu creator from Daniel Pineault's website: https://www.devhut.net/page/19/

 

[The_Doc_Man]

One specific comment, perhaps explaining a previously weak statement:

- opening read only does create a lock file so no need to test separately for that condition

No. My point is taken from the other direction. When a permissions problem prevents you from CREATING a lock file in the appropriate folder and you would have been the only person in the DB at the time, you CANNOT create the file so MUST open the DB as Read-only.

It is possible for you to have permissions that omit the Create/Delete Files low-level permission (perhaps through an error in setup or a network permissions issue). If you had the "Modify" high-level permission set properly, this wouldn't occur. But if you had some custom or lesser level of permissions that didn't extend to the folder, only to the files in it, you might be unable to fully open the file. I recall having seen this once or twice with the Navy site. If you have Modify on the files but not on the folder, this will happen. Of course, it is incorrectly set up when that happens. But according to Murphy's Law, "stuff happens."

My comments were in this context. If I said it in a confusing way, sorry!

And thanks for confirming my memory that an Exclusive open doesn't create a lock file.

EDIT: Here is the way I spoke of the read-only case, key phrase highlighted:

but don't forget that the database can be opened read-only if the opener can't create a lock file (e.g. due to permission issues).