Initiate VBA code remotely on different access users

[MrDeej]

Hello!

Is there a way to do this?

Like i am sitting at Computer2 and want code on Computer1 to initate.

Purpose could be to run docmd.quit or docmd.openform "New message" and things like that

[Stewart Ross]

Not possible as far as I know - and even if such an approach from one PC to another was possible it could be blocked by firewall software or anti-virus software as a potential attack on the system...

-Stewart

[MrDeej]

One solution is (i use this today)

Computer1 has a form going in the background which chekcs field1 on table1 every 1000ms or so.

Computer2 makes a edit to field1 on table1.

Computer1 has

Private sub form_timer()
dim rst as new adodb.recordset 
with rst
     .open "SELECT * from [table1]",currentproject.connection, adopenforwardonly, adlockreadonly
 
       If !field1 = "somevalue" then
            'do something useful
       end if
       .close 
end with
set rst = nothing
 
end sub

in its timer

But this makes the form hang some milliseconds every cycle and i think this is somewhat n00b programming :=)

I use this to display a message popup to all users, but all users gets the message at different times and i think that is unproffecionale programming.
I also use this to initiate a 'Docmd.quit' at the users which has forgotten to log of.

But i really want to find a better way to intiate code. Hope someone here has some tips :=)

[ChipR]

I use code very similar to MrDeej. I have a table called LogMeOff, which I can add user names to from a form. Since the frontend has a form that stays hidden to keep the connection open, I put the timer in that form. userName is a global variable set to the windows logon name when the user opens the app.

Private Sub Form_Timer()
On Error GoTo ErrorHandler
    If DCount ("UserName", "LogMeOff", "UserName = """ & userName & """") > 0 Then
        DoCmd.OpenForm "Auto Log Off"
    End If
ExitCode:
    Exit Sub
ErrorHandler:
    HandleError Err.number, Err.Description, Me.Name & ":Form_Timer"
    Resume ExitCode
End Sub

If you use a method like this, don't forget to remove the user from the table once you display the message or log them off or whatever.

[MrDeej]

Nice. But since we both have found this to be a solution to remote executin code, then i am starting to think that it is no other way.

We should have had a afterupde event which is triggered by the change in a table rather than a code to check values every xxx ms...

[ChipR]

That seems like a good idea. I guess you'd have a hidden form bound to any table that you wanted to watch for messages.
Also, my timer is set to 1 minute since logging people off isn't really urgent.

[MrDeej]

@ChipR

But it isnt possible to trigger a VB code to run unnles you use a Form_Timer event or the user interacts. As i can see this is the only way

I use 10 seconds or so because i use this to bring messages to all employes at the same time. They almost get the message at the same time, and when the message opens i have a sound played which you can hear troughout the factory ... Sometimes i get a bit of a laugh of this because it is monty Python "Message for you sir" hehe

[ChipR]

You're right, I don't see any event that works. That data change event sounded promising until I found it only applied to pivot tables. And Access can manage triggers for tables in a SQL Server backend, but not it's own.
I wonder if any experts can share their experience, whether they have used a method like this or would steer clear.