Is there a way to tell if a certain Access application or instance is open?

[bkdraper via AccessMonster.com]

Hello all,
Is there a way of determining if a cerain Access app is open?

I have a number of Access apps I've written for an office and at any given
time any number of those app's could be open at the same time by various
users doing their daily work.
So now I'm building a generic front-end updater to update all of them based
on parameters sent to it (like 'ok updater, this users front-end x is out of
date so update it, that users front-end y is out of date so update it, new
version of front-end z is available so push to everybody - kinda like my own
Access SMS) but in doing this, my updater needs to know if user x has front-
end x open and if so, close it before running the update (on the users local
machine, not across a network).
Problem is I can't figure out if Access DB or Access instance x is open or
not. I tried GetObject(,"Access.Application) .name, and I tried the DBEngine.
Workspaces collection but they only give me the first instance of Access
thats open whether I have one or a dozen Access app's open at once, and thats
where I ran out of ideas short of using Windows API calls to iterate through
open windows (didn't wanna do that cuz it seems unclean somehow).

So here I am asking the group, how do I find out which Access DB's or
instances (or whatever the correct terminology is) are open?

Thanks,
Kelly D.

 

[Stuart McCall]

Hello all,
Is there a way of determining if a cerain Access app is open?

I have a number of Access apps I've written for an office and at any given
time any number of those app's could be open at the same time by various
users doing their daily work.
So now I'm building a generic front-end updater to update all of them
based
on parameters sent to it (like 'ok updater, this users front-end x is out
of
date so update it, that users front-end y is out of date so update it, new
version of front-end z is available so push to everybody - kinda like my
own
Access SMS) but in doing this, my updater needs to know if user x has
front-
end x open and if so, close it before running the update (on the users
local
machine, not across a network).
Problem is I can't figure out if Access DB or Access instance x is open or
not. I tried GetObject(,"Access.Application) .name, and I tried the
DBEngine.
Workspaces collection but they only give me the first instance of Access
thats open whether I have one or a dozen Access app's open at once, and
thats
where I ran out of ideas short of using Windows API calls to iterate
through
open windows (didn't wanna do that cuz it seems unclean somehow).

So here I am asking the group, how do I find out which Access DB's or
instances (or whatever the correct terminology is) are open?

Thanks,
Kelly D.

If \\machine_name\folder_name\access_file.ldb exists, then
\\machine_name\folder_name\access_file.mdb is open. When the app closes, the
ldb file is deleted.

HTH

 

[Stuart McCall]

If \\machine_name\folder_name\access_file.ldb exists, then
\\machine_name\folder_name\access_file.mdb is open. When the app closes,
the ldb file is deleted.

HTH

Errata: That last sentence should have read:

When all instances of the app are closed, the ldb file is deleted

 

[bkdraper via AccessMonster.com]

Very clever Steve.
I got so single-minded thinking of how to iterate through open instances of
Access that I forgot to step back and think if there was a functional work-
around.

if FileExists(.ldb) then GetObject(.mdb) : obj.Quit : Do until not FileExists
(.ldb) : move on.

Since I personally get annoyed when I find a thread of exactly what I'm
looking for only to discover that nobody posts a solution, just a comment
that says 'oh, i get it', So for any future readers of this thread, I shall
elaborate on my discovery based on Steves solution:

Instantiate a FileSystemObject, your project needs a reference to the
Microsoft Scripting Runtime.
Also Dim an Access object

Dim FS as New Scripting.FileSystemObject
Dim Acc as Access.Application

(yeah, i know it's better to declare then Set instead of New, but I'm too
lazy to type that much - otherwise I'd be writing in C instead of VB )

Use that FS object to see if a lock file exists for your app (thanks Steve)
and close it.

If FS.FileExists("C:\MyPath\MyAccesApp.LDB") then 'app is open so Set
your object
Set Acc = GetObject("C:\MyPath\MyAccessApp.MDB")
Acc.Quit
End if

It takes a minute for the LDB to delete so give it some time but Murphey's
law says something could mess up so you better give yourself an escape or
else you could end up in an infinite loop.

Do Until Not (FS.FileExists("C:\MyPath\MyAccesApp.LDB")) OR (C=10000)
C=C+1 'Even on a fast machine the LDB should be gone long before C
reaches 10000
Loop

Clean up after yourself cuz we're not sloppy coders here

Set Acc = Nothing
Set FS = Nothing

then finish doing what needs doing with your code.

 

[bkdraper via AccessMonster.com]

Errata: I'm an idiot. your name is Stuart, not Steve. I humbly appologize.

 

[Stuart McCall]

Do Until Not (FS.FileExists("C:\MyPath\MyAccesApp.LDB")) OR (C=10000)
C=C+1 'Even on a fast machine the LDB should be gone long before C
reaches 10000
Loop

I would stick a DoEvents in that loop, otherwise your app may 'freeze' for a
bit.

Better still, forget the loop altogether and use the Sleep api function.
Paste this into the declarations section of your module:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As
Long)

Then replace the loop with (say) Sleep 1000. This function only suspends the
thread it's launched in, so other threads will still run normally.

PS the name substitution has happened before and no doubt will again. I'm
bullet-proof so it's no problem

 

[bkdraper via AccessMonster.com]

Good call, Sleep is even better. Thanks again.