Detect if a database is open (1 Viewer)

calvinle

Registered User.
Local time
Today, 03:10
Joined
Sep 26, 2014
Messages
332
Hi,

I have created multiple accde for many units in the department, and now, I would like to create like a central database with just a simple Form so that the user needs to go there in order to access to any other application.

I have that main file called "Central Apps.accde"
and another application "Processing ITU.accde"

When someone open the Processsing ITU, it will verify if the "Central Apps.accde" is open, if not, it will close the "Processing ITU".
This will ensure that the user has to go through the "Central" file to access to any other application.

Anyone has something to help me ?

Thanks
 

Ranman256

Well-known member
Local time
Today, 06:10
Joined
Apr 9, 2015
Messages
4,337
Check to see if the lock file exists, then deny entry.
File.laccde

If dir("c:\folder\file.laccdb") <> "" then
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
not always can you rely on .laccdb file.
if somebody forcefully close the access application or power failure occurs, it will be left over.
 

calvinle

Registered User.
Local time
Today, 03:10
Joined
Sep 26, 2014
Messages
332
Any solution to propose? I want to have all application to be open only from that application center.

Any thought?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
this is mere suggestion:

in your central db, run all other db with any extra parameter, ie:

"c:...\path to msoffice\msaccess.exe" "\folder\Processsing ITU.accde" /cmd "FromCentral"

this will pass the text "FromCentral" to Processsing ITU.accde.
you can retrieve this text using the Command() function.

you can make an encripted text or any hard word, so it will be hard for ordinary users to decipher what is the command line argument.

from Processsing ITU.accde:

debug.print Command()

soooo, you only need to test for the existence of this text (or any text you provided).
if it is not passed, stop Processsing ITU from opening.
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
I'm not sure where I borrowed this from , so apologies in advance if it was anyone here;
Code:
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
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:10
Joined
Sep 12, 2006
Messages
15,613
as arnelgp says, the locking file may be left there.

you could try to delete (kill) it first, which will fail if it REALLY is open.

I think the test needs to be the other way to ranman's suggestion though


Code:
If dir("c:\folder\file.laccdb") = "" then 
    msgbox "sorry you cannot open this database through the dbs hub"
    quit the database
else  
    on error resume next
    kill  "c:\folder\file.laccdb"
   'the kill will fail if the laccdb is REALLY in use
    'so now just test again
    If dir("c:\folder\file.laccdb") = "" then 
            msgbox "sorry you can only this database through the dbs hub"
            quit the database
    end if
end if

ok to open ...
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
that was nice code Ms.Minty. I like it a lot.
But I think what the OP want is to launch all "other" db from a central db.
meaning each "other" db should not be run on its own.
same principle as in form/report where it must be run with openargs before it can be opened.
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
Hi Arnelpg - That is pretty much how I use it. I have a small DB listing all the development db's I am currently working on. It records when I last opened it, creates backup's automatically and performs centralised updates for the FE's to our network.
If the target db I'm trying to open is already open then I pop a message up saying it's already open, if not I open it.

I would probably open each target database with a command line that runs a specific macro. (Not Autoexec). If that macro isn't called simply use Auto exec to open a modal form up saying go away! and then close the database This won't stop everyone but would be a good starting point.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
yes, i understand ms.minty.
the question is can you open each separate db, individually, without being opened from the central db?
if yes, then that's a no. since it should only be opened from the central db.

"I would probably open each target database with a command line that runs a specific macro. (Not Autoexec). If that macro isn't called simply use Auto exec to open a modal form up saying go away! and then close the database This won't stop everyone but would be a good starting point"

that was i am trying to say to the op. using /cmd
 

calvinle

Registered User.
Local time
Today, 03:10
Joined
Sep 26, 2014
Messages
332
this is mere suggestion:

in your central db, run all other db with any extra parameter, ie:

"c:...\path to msoffice\msaccess.exe" "\folder\Processsing ITU.accde" /cmd "FromCentral"

this will pass the text "FromCentral" to Processsing ITU.accde.
you can retrieve this text using the Command() function.

you can make an encripted text or any hard word, so it will be hard for ordinary users to decipher what is the command line argument.

from Processsing ITU.accde:

debug.print Command()

soooo, you only need to test for the existence of this text (or any text you provided).
if it is not passed, stop Processsing ITU from opening.

I didn't know openargs can pass through different access instance?

Here is the code I use to open another Access application:
Code:
 Dim accapp As Access.Application
 Set accapp = New Access.Application
 accapp.OpenCurrentDatabase ("C:\Users\Desktop\Application\Processing ITU.accde")
accapp.Visible = True
 End Sub

Will you be able to elaborate the code thru mine?

Thanks
 

Minty

AWF VIP
Local time
Today, 10:10
Joined
Jul 26, 2013
Messages
10,353
You can use a command line switch - see here http://www.utteraccess.com/wiki/index.php/Command-Line_Switches to open access and call a specific macro. Use that macro to open the database normally. If that macro isn't called use the target database's Autoexec macro to actually stop the database from loading. ( I don't know if this will work by the way I haven't the time to play with it I'm afraid)
 

calvinle

Registered User.
Local time
Today, 03:10
Joined
Sep 26, 2014
Messages
332
I have another idea that comes to my mind:
What if I created a main table in my Central App and log their Network ID in the table once they open the Central App.
There will also be a code as Unload to clear the Network ID in that table.
So when Central App is open, Network ID is recorded. Central App is closed, Network ID is deleted.
Then, in every application that will be created, it will link the main table of the Central App, and once that "Processing ITU" on open, it will read the linked table. If Network ID found, then its ok to open. If Network ID not found then close that application.

Is this sound good?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:10
Joined
May 7, 2009
Messages
19,169
still same issue you will run as with existence of .laccdb file.
if Processing ITU is opened then, you will check if the ip is already logged in.
if not you log it in backend or central app and the the db opens normally.

what if there is power interruption, Processing ITU is closed forcefully, the user is not logged out.
when the user tried to open Processing ITU again, it can't since he is already logged in.
the user is locked-out.
 

calvinle

Registered User.
Local time
Today, 03:10
Joined
Sep 26, 2014
Messages
332
In this situation, we cant cover the whole scenario of computer, event issue.
However the user will not be locked out, as they can reopen the central again. The central log all network id and can be multiple. If exit properly, it removes all network id of that person.
 

waris

New member
Local time
Today, 15:10
Joined
Apr 7, 2020
Messages
1
i don't know how it sounds but you can have a common table for both apps to keep a record of users log-in activity ,lets say login time and logout time ,
so if the user opens the Process app, verify if logout is filled for the current user in Main file.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:10
Joined
Feb 28, 2001
Messages
26,996
Well, if you were worried about a central hub, then you could have a central BE file for that hub. When your user tries to launch app XYZ, you could make an entry in a table that is part of the hub's BE file. Because there is no rule that says you have to link all data only to a single BE file, you could also have the individual apps link to that same hub BE table even though all of their other data is in the app's specific BE file. That would be fairly obscure and difficult to spoof, particularly if whatever you wrote in the central table was some sort of hash code or formula that left matters unclear as to how that code was generated.

If ALL of your hub-controlled apps have access to that specific control table in the hub BE, then each one can look there to see if the HUB app said they were expected - and for that matter, who was expected. You can also write code to assure that the app will remove its own entry from the hub's BE table when that app exits.

The final wrinkle would be a way for you to say "remove all expected-usage entries more than X hours old" as a way to unlock the app after a system crash or reboot. Or you build put a control that only works for you (or your designated admins) to remove an entry using the hub app rather than the individual app so that you could handle "dangling" apps that exited improperly. That is because as noted by others, there are times when "stuff happens" and as a result, proper closure DOESN'T happen.

Does that make sense?
 

bastanu

AWF VIP
Local time
Today, 03:10
Joined
Apr 13, 2010
Messages
1,401
I'm with arnelgp here, here is what I have in my db launcher app (http://forestbyte.com/ms-access-utilities/fba-db-launcher/):
Code:
'to open external Access db'
Dim sAccessPath As String, sOpenFileCommand As String      
       
sAccessPath = SysCmd(acSysCmdAccessDir) & "MSACCESS.EXE"
sOpenFileCommand = Chr(34) & sAccessPath & Chr(34)
       
sOpenFileCommand = sOpenFileCommand & " " & Chr(34) & strFrontEnd & Chr(34) & " " & "/cmd " & Chr(34) & "FBA_DB_Launcher" & Chr(34)




Private Sub Form_Open(Cancel As Integer)
'the Open event of the opening form of the newly opened db'

'vc 20150921
If Command <> "FBA_DB_Launcher" Then
MsgBox "Sorry, but you must open the FBA database using the FBA DB Launcher!", vbCritical, FBA WILL NOW CLOSE!"
Application.Quit
End If

Cheers,
Vlad
 

Isaac

Lifelong Learner
Local time
Today, 03:10
Joined
Mar 14, 2017
Messages
8,738
sounds like the best advice you can be given at this point is .... you need to split your accde database. this entire problem and associated set of problems exists only because you are worried about sharing the application, which is a non issue if db is properly split.
 

Users who are viewing this thread

Top Bottom