How to add 'events' to Access form?

[amorosik]

For the benefit of those interested in the subject I would like to share the results of my research at the present time
With the aim of sending information to an Access procedure in asynchronous mode (for example messages arriving from an Mqtt server or from Firebase) so that they can be processed by the code inside the procedure itself, they are:
- vbs script
- tcp / udp socket
- activeX

-------------------------------------------------
Vbs script
-------------------------------------------------
t is the only system that does not interfere with the main Access procedure, in the sense that from the vba code it is not necessary to do anything, except to set up a Sub of the Public type to be started in order to perform the planned activities
The content of the vbs script, to be started every time you want to communicate with the main Access procedure, is of the type

On Error Resume Next
dim mde_name mde_name = "c: \ test \ Erp2_2013.accde"
Set wmi = GetObject ("winmgmts:")
Set procs = wmi.ExecQuery ("select * from Win32_Process Where Name = 'MsAccess.exe'")
If procs.Count = 0 Then
wscript.Quit
end if

Set oApp = GetObject (mde_name) .Application
If Err.Number <> 0 Then
Set oApp = Nothing wscript.Quit
End If

oApp.Run "EVENT_LISTENER", strArgomenti
Set oApp = Nothing

Where the parameter strArgomenti is obviously one or more arguments that the Sub Event_Listener (of the main Access procedure) accepts as input

-------------------------------------------------
Socket tcp / udp
-------------------------------------------------
It is about taking advantage of the MsWinsck.ocx control produced by Microsoft which allows you to activate a tcp or udp server in a form of the main Access procedure, which must always remain active, and await the commands sent by a client 'tuned' to the same address / port of the PC where the Access procedure is running
In essence, it is:
- register the control (regsvr32.exe c: \ windows \ mswinsck.ocx)
- insert it in the Access References
- create the txcServer object
- define the Sub corresponding to the events connected to the newly created object and at least ConnectionRequest, DataArrival, Close
This system has pros and cons, the pros are the speed in managing the event (2-3 mSec from each single event) because the connection with the client is always active and does not require negotiations, the cons is that the use of the additional control is not always ' simple
Some examples available on network

-------------------------------------------------
ActiveX
-------------------------------------------------
It is essentially a system similar to the one above that uses the standard MsWinsck.ocx control, but being the control completely to be developed from scratch, it certainly allows greater control over every necessary functionality.
Technically you could also use the Visual basic 6 environment
Personally I have tried several guides to the realization of ActiveX using Visual Studio and C # as language, but despite having managed to obtain the compilation of a skeleton of the required control, I have not yet been able to complete a prototype with the minimum functionalities
Useful link

https://codedocu.com/Details_mobile?d=2075&z=1&t=Code%3A+Create+your+own+ActiveX+Component+in+Visual+Studio+2017+and+Office+2016+365
https://www.codeguru.com/csharp/.net/net_general/comcom/article.php/c16257/Create-an-ActiveX-using-a-Csharp-Usercontrol.htm
https://stackoverflow.com/questions/49469926/using-withevents-in-vb6-with-net-vb-dll

 

[Saphirah]

For the benefit of those interested in the subject I would like to share the results of my research at the present time

As i am also quite interested in the topic, here is a usefull link that can definitely be of use.

How to automate Microsoft Access by using Visual C# - Office

Describes how to automate Microsoft Access from Visual C# 2005 or Visual C# .NET.

docs.microsoft.com

The simplest solution that comes into my mind, if you want to communicatefrom c# to access is to create a vba function in a module, similar to this:

Public Function Listener(Arguments As DataType)
    'Your Code here
End Function

Then you can use your C# code to send requests, and execute the code in access vba

oAccess = new Access.ApplicationClass();

//To my knowledge this will get the current instance if already opened
oAccess.OpenCurrentDatabase("C:/File/Path/To/My/Database.accdb");
oAccess.Run "Listener", ArgumentsHere

For the reverse the following link seems appropriate:

Sending an array of doubles from Excel VBA to C# (using COM interop) - CodeProject

Tips on sending an array of data from an Excel VBA to C#.

www.codeproject.com

The short version is the following, but please go to the website for further information
First you need to create a C# class or method to handle the request

public class callableClass
{
    public int callableMethodSimple(double a)
    {
        return (int)a;
    }
}

Then you write the vba code to send the request.

'VBA code
Public cssObject As New SendArray.callableClass
'SendArray is the name of the project in which callableClass resides.

Sub MyRoutine()
         Dim result As Integer
         result = cssObject.callableMethodSimple(5.0)
End Sub

You do need to set up and import the C# project in vba, so please take a look at the website.
While this does not allow you to communicate with your running application instance directly, you can then use the power of C# to redirect the requests to your running instance.
Up till now i sadly do not have a direct way to communicate with your running C# application using vba, but your options are way broader using C# with this "Adapter".

This code is untested though, so if this does not work out, please write me

 

[amorosik]

Yes the code

oAccess = new Access.ApplicationClass();
oAccess.OpenCurrentDatabase("C:/File/Path/To/My/Database.accdb");
oAccess.Run "Listener", ArgumentsHere

Yes, the code indicated is definitely working
But it is not suitable for communications that must take place quickly
The loading of the Access instance is very long, we are talking about seconds, if compared with the need for rapid reaction to the commands sent from the outside to the Access procedure
For this reason in my post I indicated that it is the simplest way (vbs script) because it does not in any way affect the code of the main procedure in which the command is 'injected'
But it is also the worst method in terms of reaction times

 

[Saphirah]

Yes the code

oAccess = new Access.ApplicationClass();
oAccess.OpenCurrentDatabase("C:/File/Path/To/My/Database.accdb");
oAccess.Run "Listener", ArgumentsHere

Yes, the code indicated is definitely working
But it is not suitable for communications that must take place quickly
The loading of the Access instance is very long, we are talking about seconds, if compared with the need for rapid reaction to the commands sent from the outside to the Access procedure
For this reason in my post I indicated that it is the simplest way (vbs script) because it does not in any way affect the code of the main procedure in which the command is 'injected'
But it is also the worst method in terms of reaction times

oAccess.OpenCurrentDatabase("C:/File/Path/To/My/Database.accdb");

Wait, doesn't this code return the existing instance? Does it create a new Access instance? Then i need to look up a different method It never occured to me, because i am only running a background process, that does not depend on timing so i never noticed!