Remote control over LAN with Access VBA

[tunsarod]

Machine 'A' and machine 'B' are on the same LAN. In the specific scenario I am concerned with right now machine 'B' is running Windows 10, while machine 'A' could be running 10, 7 or XP. i.e. there are several machines, with varying OS's, currently on the LAN that might need to perform this task. I'm using Access 2003 but I am looking for a solution that would work equally well with Access 2010.

Q. Is it possible, using MS Access and VBA, for an Access application on machine 'A' to detect whether a particular application (let's call it Application 'X') is running on machine 'B' and if the application is not detected for the Access application on machine 'A' to start Application 'X' on machine 'B'?

BTW. Application 'X' is not an Access app but for the sake of argument let's say it is the standard windows Notepad.exe application.

 

[jrbarnett]

Hello

Lets break this problem down into smaller pieces, and put it in more general terms rather than through Access and VBA:
1. Could application X on machine A detect if application X is running on machine B (or C or D or E)?
2. Could application X on machine A start application X on machine B?
3. Could MS Access/VBA be used to check and initiate running processes on a remote machine?

Where A and B could have different operating systems, and thus available facilities.

To answer question 1, using native Access functionality, the answer is a most definite no, but tools such as the tasklist command or Powershell Get-Process can be used to detect running processes on another computer; whether this would be granular enough to determine if it had your application X running.

Even then this makes assumptions that:
* All computers are all in the same active directory domain to have common set of users
* On your application startup, something is set to positively identify your application X as running on a particular computer. This could be a file on disk, a record in an access log; both of which are cleared on exit. Your scripts would need permissions to read this log to check if it was running elsewhere.

Question 2: Can application X on machine A start application X on machine B?
A: Tools such as PSExec allow starting processes on a remote computer.
From a general sense this is possible therefore, you would need to call this with appropriate parameters or use the underlying API functions.

Question 3: Can MS Access/VBA be used to check and initiate running processes on a remote machine?
As Access has the ability to call operating system functions through the Shell command in VBA, theoretically this is possible.

You would need to have these tools available in on each computer, and be willing to get your hands dirty to write and test the scripts individually, then tie these scripts together through VBA (including any differences across the various Access versions). Good luck if you decide to go ahead with this.

John

 

[Darrylles]

Probably many ways to do this, and one of them is:

If ALL machines can access one, single, MS Access back-end database (containing tables only if required), stored on ONE, SINGLE machine, then any activity that you wish to be 'fired' can be.
If ALL machines had 'open' access (say as admin) to this centralised db', then Machine A could tell Machine B to execute Notepad.exe, and vice-versa. (Although I don't think that's what you had in mind).

The back-end would contain table(s) that record:

*) Table of applications that each machine must 'look out' for and report to the above table with.
*) Table of central 'control' info, such as 'how often each machine should 'poll' it's environment to check for running applications, or, if to run an application.

Each machine could have standard functionality coding such as "Kill Application: " (and append the application name at run time - from the central db), or, each type of command stored in the central back-end.

The key point here is: if the USER of any of these machines has permissions to run or kill any application ON THAT MACHINE, then, the MS Access Application (front-end) on that machine is effectively THAT USER, and MS Access can initiate exactly what that user can initiate.

As for 'versioning', then:
a) It would be advisable to write the MS Access code in the lowest common denominator version (for all machines). E.g. all machines run exactly the same app version (even if they are using different MS Access).
b) The single, centralised 'back-end' database would store command options for every operating system type, so that if a Windows 10 machine was looking for instruction - it would signify that it was Windows 10 and thus retrieve only the Windows 10 command format, whereas an Win XP machine would indicate that it is XP and retrieve the Windows XP format command (if there was any difference).

It's all a matter of 'flags' in the CENTRAL database being changed, the machine A / B / C front-end apps polling that central database for 'flags' in data, which indicate to them that they should 'execute xyz' etc.

If I make this sound simple - it is, but, it all depends on:
1) permissions
2) all machines having visibility of one central database
3) the front-ends (on different machines) all running (somehow), (and even this can be automated with wake-on-lan and a startup script).
4) all different op sys command formats / application name differences stored in the back-end.

Scenario table:

tblDoIt:

PC           Active            ExecuteMe
--           ------            -----------
A            False             Notepad.exe
B            True              Notepad.exe
C            False             Notepad.exe

1) Admin changes Active field flag in this back-end table, for Machine B to True.
2) Machine A, B and C all are all 'polling' that database table (via a form timer event), with the following, (but 'AND PC = 'B' changes to 'AND PC = 'A' (for machine A) and 'AND PC = 'C' (for machine C):

"SELECT ExecuteMe "
"FROM   tblDoIt "
"WHERE  Active = True "
"AND    PC = 'B' "

Only Machine B would retrieve a record with "Notepad.exe" in the 'ExecuteMe' field, and then all it takes is a 'SHELL' command from Machine B to execute 'Notepad.exe' on Machine B.

Think of it this way: if lot's of client machines, running your MS Access app can access MS Outlook and automatically send an email to hundreds of their contacts - (without the user doing anything) - this, is effectively what you are trying to do.

So yes, it can be done (I've done it (with hundreds of client machines), although not exactly this scenario).

ATB,

D


Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[tunsarod]

Thanks guys. You have given me some great information here.

The big issue for my problem is that the target pc which is the host for my back-end database does not have a front-end running on it. In fact it's in a room on its own and nobody uses it normally, especially not at night. I have a third party Remote Access program which I leave running but every now and then the program seems to close. So when I am at home in the wee small hours trying to "get in" to do some work on the back-end tables, etc. I can't, not without driving to the office to re-start the program. Getting into the office at night is not without its difficulties so I usually end up waiting till morning.

I was hoping that I might be able to set a timer event in the front-end applications running on the other pc's elsewhere in the building, at least one of which is in use all night, to check that the remote control utility application was still running on the back-end host machine and or at least issue a command to start it. I have discovered it's not a program that opens multiple instances of itself. So clicking the start icon when its running does not open a second copy. The Shell command, so far as I know, will only open a program on the machine on which the Shell command is issued, so that wouldn't help any.

It looks like I will have to create a small Access app to leave running but hidden on the back-end host machine that will keep an eye on things and restart the remote control app periodically.

Thanks again guys.
Regards
Rod

 

[strongm]

You can use WMI both for starting a process remotely and to determine which remote processes are running.

 

[jrbarnett]

Would an operating system level script run purely on the server machine to detect closure/restart it be a better option?

John

 

[tunsarod]

strongm, thanks for that. I didn't know about WMI. More reading to do but it looks promising.

John, do you mean a dos style .bat program?

Rod