MS Access VBA Application.Quit Command To Another PC On The Network

I am basically wanting to Quit Access on PC2 from PC1 by using VBA.  This is to keep 1 user logged into only 1 computer at a time.  I have a table which keeps track of which PCs the user is logged into and if the user tries to log into PC1 when they are still logged into PC2 then I want the VBA to Application.Quit the access on PC2 before logging into PC1. I have everything up until this Application.Quit done.

So how would this go?

Thanks for the help.
LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
You are not going to be able to close down the application on one computer from another.  However, I think you can accomplish this by having a table in the BE (tbl_User_Computers) which contains fields:

UserID
ComputerID
LoggedOnAt
ForceOff

You could query this table to identify whether the user is already logged in at another computer, and if so, set the ForceOff = True.

Then, you would have code in the application to check whether the ForceOff flag was set for the User/Computer combination and if so, that machine would shut itself down by closing out of the application.

The problem with this is that you have to create an entry in that table each time the user logs in and out, and if an application is shut down using task manager or some other method, you won't overwrite that record and you might end up waiting for the application to close on a computer where it is actually no longer running.  One method might be to delete these login entries if they are over a day old, but that still leaves you with the possibility of not being able to log in from another computer.
1
Dustin StanleyEntrepreneurAuthor Commented:
I was just thinking of maybe a BAT file to run a Module like so:

Option Compare Database
Option Explicit

Public Function RemoteApplicationQuit()
'This Module is for remotely logging out of access and closing it from another PC
On Error GoTo ErrorHandler

  Call UserActivityLoggedOutOfDatabase 'This marks the record that the user has logged out of the database and is stored for future reference.
   If Forms!frmDetectIdleTime!cbxOKToClose = True Then
    DoCmd.SetWarnings False
     DoCmd.OpenQuery "qryUpdateUserLoggedInFalse" 'This marks that the user is logged Out of this PC.
    DoCmd.SetWarnings True
     Application.Quit 'Close the Database
   Else
    Forms!frmDetectIdleTime!cbxOKToClose = True 'This is a flag on the hidden form to check if it is ok to close the database.
     DoCmd.SetWarnings False
      DoCmd.OpenQuery "qryUpdateUserLoggedInFalse" 'This marks that the user is logged Out of this PC.
     DoCmd.SetWarnings True
      Application.Quit 'Close the Database
   End If
 
Exit Function

ErrorHandler:
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error in RemoteApplicationQuit"
End Function

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
Dustin,

Where would you call this code from?

I'm not a huge fan of Application.Quit, as you have used it here, because you really are not closing the application elegantly.  You are not making any effort to close out open forms which might contain unsaved records.

Dale
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
In the Remote DB ... create a Function with one line of code ....  Application.Quit

Form the other DB ... use the VBA Run command to call the Function in the remote db.

Look up the 'Run' Command
0
Dustin StanleyEntrepreneurAuthor Commented:
DALE
Where would you call this code from?

I don't know exactly. But I tried a BAT file and it just opens a new application of the same Front end of access and then closes it. Not closing the existing Front End that is already open.

DATABASEJOEMX
Form the other DB ... use the VBA Run command to call the Function in the remote db.

This is the part I am struggling with right now.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is an example:

Public Function ADIM_RunTest() As Boolean

 
    ADIM_RunTest = False
    With New Access.Application
        .OpenCurrentDatabase filepath:="<Full UNC Path To Remote DB>\ADIM.accdb"
        .Run Procedure:="ADIM.QMS_Exec", Arg1:=True     'Arg1 = DebugMode, True/False
    End With
    ADIM_RunTest = True
   
End Function

In this example, ADIM.accdb is the remote db, QMS_Exec is the Function in the Remote DB
1
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"This is the part I am struggling with right now."

ADIM.accdb is the Remote db

ADIM_RunTest() is the sample code for the 'other' db

The VBA Run command is not that well known.  And you can pass up to 30 String parameters to a Function in another db as well.
0
Dustin StanleyEntrepreneurAuthor Commented:
Thanks DatabaseJoeMX. I have ran out of time today but I will try in the morning to see what I can do. Thanks all for the help.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Trust me ... it works.
It's in daily use here at work.
Ping back with any more questions ,,,
1
Dustin StanleyEntrepreneurAuthor Commented:
Joe
.Run Procedure:="ADIM.QMS_Exec", Arg1:=True     'Arg1 = DebugMode, True/False

What is "ADIM.QMS_Exec"?????

I thank you for the help.

So Far I have:
Option Compare Database
Option Explicit

Public Function ADIM_RunTest() As Boolean

On Error GoTo ErrorHandler

Dim FullUNCPathToRemoteDB As String
Dim sUserName As String

   'Get Current User Name
    sUserName = Environ$("username")
    'Check to see if this is the main computer name "PC 1" that uses a different path
  If sUserName = "PC 1" Then
  'Main PC
    FullUNCPathToRemoteDB = "C:\Users\PC 1\Desktop"
    Else
    'Other PCs
     FullUNCPathToRemoteDB = "C:\Users\PC 2\Desktop" 'Something like this IDK yet
    End If
'**********************************************************************************************************************
    ADIM_RunTest = False
    With New Access.Application
        .OpenCurrentDatabase filepath:=FullUNCPathToRemoteDB & "\PC Database_FE.accdb"
        .Run Procedure:="ADIM.QMS_Exec", Arg1:=True     'Arg1 = DebugMode, True/False
    End With
    ADIM_RunTest = True
    

 Exit Function
ErrorHandler:
  MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error in ADIM_RunTest"
End Function

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"What is "ADIM.QMS_Exec"?????"

QMS_Exec is a Function in the ADIM.ACCDB database

BTW ... you don't need , Arg1:=True     'Arg1 = DebugMode, True/False
I was just passing an additional argument for special use.

Assuming this is the name of your remote DB:

PC Database_FE.accdb

Then in that db you need a Function ... let's call it

QuitAndClose()

So ...

Public Function QuitAndClose()
     Application.Quit
End Function

Then to call this from another db:

    With New Access.Application
        .OpenCurrentDatabase filepath:=FullUNCPathToRemoteDB & "\PC Database_FE.accdb"
        .Run Procedure:="PC Database_FE.accdb.QuitAndClose"
    End With

That's it.

As far as the Path goes ... it's whatever Folder Path + the Remote DB name (not just the Folder path)

FullUNCPathToRemoteDB = "C:\Users\PC 1\Desktop\SomeDbName"

And SomeDbName  contains the Function

Public Function QuitAndClose()
     Application.Quit
End Function
0
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Joe. It keeps giving the error for line:
.Run Procedure:="PC Database_FE.accdb.QuitAndClose"

Open in new window


Access just says:
Error 2517: Microsoft Access cannot find the procedure 'PC Database_FE.accdb.QuitAndClose.


I created another public sub like so:
Public Sub MessageBox()
MsgBox "Houston We Have Lift Off!", vbInformation
End Sub

Open in new window


I can not get .Run Procedure:="QuitAndClose" to work but .Run Procedure:="Messagebox" works perfectly.....


**UPDATE**
The Messagebox works perfectly when calling back to PC1 from PC1 but not when calling to PC2
Access Error:
Error 40351: Method 'Run' of object '_Application' failed in Mod_QuitAndCloseCaller
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
It can't be a Sub ... only a Public Function

What is the Path to where the db is located?

What is the exact Name of the db ?

This is the Public Function you need:

Public Function QuitAndClose()
     Application.Quit
End Function
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I don't think a MsgBox will work in the Remote db ... only a Function that can be executed.
1
Dustin StanleyEntrepreneurAuthor Commented:
I ran out of time at work. I will see what I can do Monday.
ASAP when I get the path I will let you know. I can't remember and I am away.

I appreciate the help.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
No problem.
0
Dustin StanleyEntrepreneurAuthor Commented:
What is the Path to where the db is located?
Remote DB Path is:
FullUNCPathToRemoteDB = "\\PC4-PC\Users\User1\Desktop\PC Database_FE.accdb"

What is the exact Name of the db ?
All the PCs have the same DB Name for the Front End
"PC Database_FE.accdb"

I hope this helps. Thanks.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... Create this Function in PC Database_FE.accdb in a Standard VBA Module.

Public Function QuitAndClose()
     Application.Quit
End Function

Then ... put this code in whatever db (let's call it Local) will be used to call the 'Quit' code in the remote db:

Public Function QuitAndCloseRemote()

    With New Access.Application
        .OpenCurrentDatabase filepath:="\\PC4-PC\Users\User1\Desktop\PC Database_FE.accdb"
        .Run Procedure:="PC Database_FE.accdb.QuitAndClose"    '  Note the 'dot' between the Db name and the Function name
    End With

End Function

Then you can execute the QuitAndCloseRemote() code from wherever (like a Command Button) inside the Local Db

Call  QuitAndCloseRemote()

====
0
Dale FyeOwner, Developing Solutions LLCCommented:
Joe,

I've never used the .OpenCurrentDatabase  method;  I'll have to take a closer look at this, do you use this, along with WhosLoggedIn in your nightly compact and backup procedure?

Dale
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Hi Dale ...
Can't remember if it's used in the backup code or not. will have to check.
I use Brent's (with my mods) Who Is Logged In tool in conjunction with Backup if ... there are any DBs open (.LDB/.ACCDB) to get the User Name - so we can send an email to get their act together and close dbs down before leaving.  Of course, my Forced Shutdown Module - part of the Backup - will shut down any DBs open ... about 98% of the time. Only if an FE is stuck in memory (on a user's system) does FSD not work.
On any given night ... there are typically 1-3 users who have left db open,
0
Dustin StanleyEntrepreneurAuthor Commented:
With .Run Procedure:="PC Database_FE.accdb.QuitAndClose" I get:
Error 2517: Microsoft Access cannot find the procedure 'PC Database_FE.accdb.QuitAndClose.' in Mod_QuitAndCloseRemote

Open in new window


Does there have to be any special permissions or any references check marked in the DB to refer back to?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Are you sure you created QuitAndClose() in the remote DB ... and it's a Public Function in a Standard VBA module ?

Error message actually seems pretty clear.

Does the code in the Remote DB Compile ?

I'm in a 4 hr meeting so can't respond for a bit ...

Seems like you are close.

So me the exact code you have in both DBs related to this.
0
Dustin StanleyEntrepreneurAuthor Commented:
On the PC in the other room:
Name Mod_QuitAndClose
Option Compare Database
Option Explicit

Public Function QuitAndClose()
     Application.Quit
End Function

Open in new window



From the PC I am making the Call From.
Mod_QuitAndCloseRemote

Option Compare Database
Option Explicit

Public Function QuitAndCloseRemote()

On Error GoTo ErrorHandler

    With New Access.Application
        .OpenCurrentDatabase filepath:="\\PC4-PC\Users\USER1\Desktop\PC Database_FE.accdb"
        .Run Procedure:="PC Database_FE.accdb.QuitAndClose"    '  Note the 'dot' between the Db name and the Function name
    End With

    
Exit Function
    
ErrorHandler:
 MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error In QuitAndCloseRemote"
End Function

Open in new window


Test Form With A Button:
Private Sub btnButton1_Click()

On Error GoTo ErrorHandler

Call QuitAndCloseRemote

Exit Sub

ErrorHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
   VBE.ActiveCodePane.CodeModule, vbCritical, "Error"
End Sub

Open in new window

0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... it all looks good. Only thing maybe is

PC Database_FE

the space between PC and Database.

As a TEST ... can you rename this PCDatabase_FE ... and make the appropriate changes in the code ?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Also ... re

\\PC4-PC\Users\USER1\ .....

Not sure you need \\  when referring to the C Drive on another computer.

Try with  C:\PC4-PC\Users\USER1\ ...
0
Dustin StanleyEntrepreneurAuthor Commented:
I have tried 2 ways and both give me error
Error 7866: Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file. in Mod_QuitAndCloseRemote

C:\PC4-PC\Users\USER1\ ...
and
PC4-PC\Users\USER1\ ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... not sure where the issue is.
Are you able to open that db and module directly from your PC where the calling code is?
It seems ... to be finding the module Mod_QuitAndCloseRemote ok .. which is ODD.

"or opened exclusively by another user"
Also ... is the remote DB physically open (there is an laccdb file) when you are calling this code ?
That would be one difference between your situation and mine.  The ADIM database is not physically open.  
If so ... try closing the ... then run your code. Of course, the Application.Quit won't do anything since it's already closed.  But let's see if that error happens
0
Dustin StanleyEntrepreneurAuthor Commented:
Sorry I have been swamped with many other things at work.  It is open with the laccdb file in play. I tried several ways with the DB off and the results are:

1. With \\PC4-PC\Users\USER1\ ..... I get:
Error 40351: Method 'Run' of object '_Application' failed in Mod_QuitAndCloseCaller

2. With C:\PC4-PC\Users\USER1\ ...  (OR)
PC4-PC\Users\USER1\ ... I get:
Error 7866: Microsoft Access can't open the database because it is missing, or opened exclusively by another user, or it is not an ADP file. in Mod_QuitAndCloseCaller

I appreciate the help. I am not exactly familiar with this area but I am trying whatever I know.

Can you clarify on this please>>>
Are you able to open that db and module directly from your PC where the calling code is?



**UPDATE**
For testing purposes only I have created a second DB on my Calling PC called "Database2". I figured I will try to get this to work on the same PC first before on another PC on the network.

in Database2 I have 2 modules:

QuitAndClose:
Option Compare Database
Option Explicit

Public Function QuitAndClose()
     Application.Quit
End Function

Open in new window



MessageBox:
Option Compare Database
Option Explicit

Public Function MessageBox()
MsgBox "Houston We Have Lift Off."
End Function

Open in new window


From the Caller DB1 I have tried several ways with the run procedure and only the MessagBox method works perfect and this is all with the Database2 open:
.Run Procedure:="Database2.accdb.QuitAndClose"

Open in new window

I get (Error 440: Automation error in Mod_QuitAndCloseRemote)

.Run Procedure:="Database2.accdb.MessageBox"

Open in new window

(WORKS PERFECT!) Even works when the Databse2 is closed.


I have found this link to a different technique but it is working so far.
https://support.microsoft.com/en-us/help/304408/how-to-shut-down-a-custom-access-application-remotely
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So you are saying that ... the MessageBox in the 'remote'  (other) db actually opens from within that db?  That is surprising.

Can you try this real quick:

Public Function QuitAndClose()
     Application.Quit acQuitSaveNone
End Function

"and this is all with the Database2 open:"
Does the MessageBox work with Database2 closed ?
0
Dustin StanleyEntrepreneurAuthor Commented:
database2 is on the same PC as the Calling DB (DB1).

As for with Database2 and the code you sent I still got a Error of 440 Automation error.

Does the MessageBox work with Database2 closed ?
Yes it still works when closed. It pops up the message box and nothing else...Kinda neat.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
I'm totally mystified, lol.  I see no reason for the 440 error.  I need to experiment some with this.

"Yes it still works when closed. "
So ... when the Message Box is opened from within Database2 (while closed), the Database2 then have an .LACCDB associated with it ?
0
Dustin StanleyEntrepreneurAuthor Commented:
the Database2 then have an .LACCDB associated with it ?
Yes it does. and as soon as you click ok on the msgbox the box and the .LACCDB file disappear.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So ... I ran the same test with the MessageBox and got the same result you did.
But fails on the QuitAndClose().
I'm thinking there is a conflict with ... we are opening the 'remote' db with code ... and then telling it to 'Quit' ... and the Application.Quit is in conflict with the OpenCurrentDatabases method.  

I'm going to try something .... and I will report back.
0
Dustin StanleyEntrepreneurAuthor Commented:
Sounds good. I ended up getting where I needed to go with that above link but tweaking the code by adding a table in my DB that keeps track of the users being logged in to each PC. Basically a table with 4 fields (IsLoggedIn, PCNameID,EmployeeID, AppShutdown) then there are records for every combination of PCNames to EmployeeID.

EmpID.    PCNameID.    
1.                1
1.                 2
1.                 3
2.                 1
2.                 2
2.                 3
Etc.........

I then check to see if the EmpID is logged into any other computer before logging into the new DB and if so then AppShutdown is checked yes for that other PC that the user was previously logged into.

In each DB Front End there is a hidden form that on timer checks the UserLoggedIn table for the AppShutDown to be checked yes and if so it Application.Quit.

I hope that makes sense.

And thanks for the help. I definitely learned something new. I think that message box idea of popping up even without the DB is a very useful tool for messages.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well what I tried did not work.  Pretty sure it's because of the conflict I mentioned above.
I might have missed that you were actually trying to implement a Forced Shutdown, otherwise I would have just suggested this:

http://www.peterssoftware.com/fsd.htm

I built an entire FSD module using Peter's app. It's used nightly to shutdown and dbs that may still be open, or any other time where one or more db (up to 35-40) for whatever reason (although somewhat rare).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin StanleyEntrepreneurAuthor Commented:
Thanks Joe for the help.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
FWIW here is what the Backup Utility looks like ...

Backup Utility with Forced Shutdown Module
Backup Utility with Forced Shutdown Module
Backup Utility with Forced Shutdown Module
Standalone Forced Shutdown Utility ...
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you very much for the visual. This will help me further.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.