Socket tcp communication for Access 64bit - what we can do?

[amorosik]

What's the best way to implement TCP socket communication for 64-bit Access?
The winsck.ocx library works fine in a 32-bit development environment, but what can I do to use 64-bit Access?
I saw this code on Spiceworks
Have any of you used it with good results?

 

[Albert D. Kallal]

What's the best way to implement TCP socket communication for 64-bit Access?
The winsck.ocx library works fine in a 32-bit development environment, but what can I do to use 64-bit Access?

I would suggest using the vb.net sockets, and then call that code from VBA. It's not clear if you using the ActiveX controll (dropped into a Access form), or using the TCP library direct from VBA?

Over the years, going back to VB5, VB6 days, and even now in .net?
I never had to use TCP library.

So, depending on what you need, you may well be able to write such code in VB.net, and simply call that code from VBA.

The nice part is:
You don't need a VBA reference.
You have x32 and x64 bit support...

So, it kind of depends on what your VBA code now looks like - and depending on what you need, then I suggest calling such code from VBA...

Perhaps you post a wee bit of code of what you VBA did/does.

R
Albert.

 

[amorosik]

Thanks for the advice
But it's the "..call from VBA code.." part i want to avoid
It would mean constantly polling the external code from VBA to see if new messages are received
And polling is precisely the part I want to avoid.

 

[sonic8]

This is an example where @Albert D. Kallal's philosophy of Late Binding with his .Net loader is not the optimal solution because here events from the .Net component are an important part of efficiently integrating such an external component into an Access application.
This is not a general .Net weakness. If you create a .Net COM component and register it in the Windows registry, you can consume its events as you previously did with the Winsck.ocx. You generally just cannot use Late Binding if you want/need to consume events from the external component.

@amorosik , did you consider using a 3rd-party control that is 64bit compatible instead of the Winsck.ocx?

 

[amorosik]

This is an example where @Albert D. Kallal's philosophy of Late Binding with his .Net loader is not the optimal solution because here events from the .Net component are an important part of efficiently integrating such an external component into an Access application.
This is not a general .Net weakness. If you create a .Net COM component and register it in the Windows registry, you can consume its events as you previously did with the Winsck.ocx. You generally just cannot use Late Binding if you want/need to consume events from the external component.

@amorosik , did you consider using a 3rd-party control that is 64bit compatible instead of the Winsck.ocx?

Yes, of course, even external controls with acceptable results
Perhaps I didn't quite understand Albert D. Kallal's advice
I assumed that "..call it from VBA.." meant polling the communication channel
But that might not be the case
He was probably suggesting I create a component with similar functionality to 'mswinsck.ocx' to use in conjunction with Access
And that's a perfectly acceptable option

 

[sonic8]

I assumed that "..call it from VBA.." meant polling the communication channel
But that might not be the case

This actually was the case as he referenced Late Binding indirectly by "You don't need a VBA reference.".

He was probably suggesting I create a component with similar functionality to 'mswinsck.ocx' to use in conjunction with Access

This is also possible with .Net, but you must register the library you create to use events similar to the Winsock OCX.

 

[Albert D. Kallal]

Thanks for the advice
But it's the "..call from VBA code.." part i want to avoid
It would mean constantly polling the external code from VBA to see if new messages are received
And polling is precisely the part I want to avoid.

Well, we have two goals:
First goal, find some kind of code or "thing" to replace the OCX.
2nd goal: eliminate current polling that you have????

So, we have to keep both of these things somewhat "separate" for our goals here.

So, you may well not need some kind of "polling", but if you looking to have the Access application run, (say editing some form), and ALSO have Access trigger + run code when activity occurs, then yes, we "probably" need to sink events.

On the other hand, maybe we have a postal scale, and in a access form, we want the data from that postal scale to appear in the form - so, in that case, a timer that calls/runs every say 1/2 second, and calls the .net winsocket code would work just fine......

So,, it kind of depends on how/what/when the current winsock code you have is to run, and does it need to trigger events in Access?

R
Albert

 

[amorosik]

No, I've decided not to use polling
I'd like to implement exactly the functionality provided by mswinsck.ocx
It just needs to work correctly in a 64-bit Access environment.

 

[Albert D. Kallal]

No, I've decided not to use polling
I'd like to implement exactly the functionality provided by mswinsck.ocx
It just needs to work correctly in a 64-bit Access environment.

Ok, I wish I had some example for you!
It's not hard to build the .net class (use vb.net - code is much like VBA).

So, in theory, you would use the .net sockets (System.Net.Sockets)

So, not clear if you using TcpClient, networkSteam, or Socket?

So, you can/could create a .net class, and expose
Connect method, Connect event
Send method, Send event (not sure if you need??)
Close method, close event (??)
And then you need a dataReceived event, Connected event, Disconnected event.

So, you can "very" likely duplicate the basic WinSock.ocx features you need........

The advantage would be that you can now have a Access x32, or Access x64 solution.
And you would not have to used some 3rd party solution......

So, yes, you can "sink" events from .net into VBA - and thus no need for some "polling" solution.

As pointed out - you of course can't use late binding and my .net loader - but that's a minor price/issue in this context.....

As always, I have limited time, but I suppose I would not mind cooking up say a Android to ms-access winsock example, and thus my Android phone could say talk to ms-access on my home network......

R
Albert

 

[amorosik]

So, not clear if you using TcpClient, networkSteam, or Socket?

Sorry but I don't understand the question, i assume the answer is TcpClient and NetworkStream.
On client side (Access 32bit), with mswinsck.ocx, i use this code

40        If Me.cmdConnect.backColor = COLOR.COLOR_IS_red Then
50            Set tcpClient = New MSWinsockLib.winsock
60            tcpClient.RemoteHost = Me.txtIpAddress
70            tcpClient.RemotePort = Me.txtPorta
           
80            Me.txtIpAddress.Enabled = False
90            Me.txtPorta.Enabled = False

100           tcpClient.connect
110           DoEvents
           
120           Me.cmdConnect.backColor = COLOR.COLOR_IS_green
130           Me.cmdConnect.caption = "Click to disconnect"
140           DoEvents
           
150           If tcpClient.State <> SCK_CONNECTED Then
160               Me.txtStorico = "NON CONNESSO = STATO SOCKET => " & tcpClient.State & Chr(13) & Chr(10) & Me.txtStorico

170               tcpClient.Close
180               Set tcpClient = Nothing
190               Me.cmdConnect.backColor = COLOR.COLOR_IS_red
200               Me.cmdConnect.caption = "CONNECT"
210               Me.txtIpAddress.Enabled = True
220               Me.txtPorta.Enabled = True
230               End If
           
240           Else
250           tcpClient.Close
260           Set tcpClient = Nothing
           
270           Me.cmdConnect.backColor = COLOR.COLOR_IS_red
280           Me.cmdConnect.caption = "CONNECT"
290           Me.txtIpAddress.Enabled = True
300           Me.txtPorta.Enabled = True
310           End If

On server side, this code:

40        If cmdBeginListen.caption <> "Server Listening..." Then
50            Set tcpServer = New MSWinsockLib.winsock
60            tcpServer.Protocol = sckTCPProtocol         ' sckUDPProtocol
70            tcpServer.LocalPort = Me.txtPorta
80            tcpServer.Listen
           
90            cmdBeginListen.caption = "Server Listening..."
100           cmdBeginListen.backColor = COLOR.COLOR_IS_green
       
110           Me.caption = "Main - <IP>" & tcpServer.LocalIP & ":<Port>" & tcpServer.LocalPort
120           Me.txtMESSAGGI = Prefisso_Log() & "BeginListen - Connected - Main - <IP>" & tcpServer.LocalIP & ":<Port>" & tcpServer.LocalPort & Chr(13) & Chr(10) & Me.txtMESSAGGI
130           Me.txtIpAddress.Enabled = False
140           Me.txtPorta.Enabled = False
           
150           Else
160           tcpServer.Close
170           Set tcpServer = Nothing
           
180           cmdBeginListen.caption = "Begin Listen"
190           cmdBeginListen.backColor = COLOR.COLOR_IS_red
       
200           Me.cmdConnesso.caption = "NON COSNESSO"
210           cmdConnesso.backColor = COLOR.COLOR_IS_red
           
220           Me.caption = "Main"
230           Me.txtMESSAGGI = Prefisso_Log() & "BeginListen - DisConnected" & Chr(13) & Chr(10) & Me.txtMESSAGGI
240           Me.txtIpAddress.Enabled = True
250           Me.txtPorta.Enabled = True
260           End If


Private Sub tcpServer_ConnectionRequest(ByVal requestID As Long)
10        On Error GoTo eh_tcpServer_ConnectionRequest
       
          Dim Tempo_Ingresso_Routine As Single, Tempo_uscita_Routine As Single
       
20        Tempo_Ingresso_Routine = timer2()
30        Debug.Print "Form_frmVerificaSocketServer-tcpServer_ConnectionRequest", "Timer Ingresso = " & Format$(Tempo_Ingresso_Routine, "00000.000")
       
40        If tcpServer.State <> sckClosed Then tcpServer.Close
       
50        tcpServer.Accept requestID
       
60        cmdConnesso.caption = "CLIENT CONNESSO"
70        cmdConnesso.backColor = COLOR.COLOR_IS_green
       
90        Me.txtMESSAGGI = Prefisso_Log() & "ConnectionRequest - Richiesta connessione da ID = " & requestID & " - Remote Host = " & tcpServer.RemoteHost & "/" & tcpServer.RemoteHostIP & "/" & tcpServer.RemotePort & Chr(13) & Chr(10) & Me.txtMESSAGGI
100       Debug.Print "Richiesta connessione da ID = " & requestID
110       Exit Sub
       
120       Tempo_uscita_Routine = timer2()
130       Debug.Print "Form_frmVerificaSocketServer-tcpServer_ConnectionRequest", "Timer Uscita   = " & Format$(Tempo_uscita_Routine, "00000.000") & "  Tempo Impiegato = " & Tempo_uscita_Routine - Format$(Tempo_Ingresso_Routine, "00000.000")
140       Debug.Print "----------------------------------------------------------------------------------"
150       Exit Sub

eh_tcpServer_ConnectionRequest:
160       msg_err err.Number, Erl, Error, "tcpServer_ConnectionRequest of Documento VBA Form_frmVerificaSocketServer"
170       Resume Next

End Sub

Private Sub tcpServer_DataArrival(ByVal bytesTotal As Long)
10        On Error GoTo eh_tcpServer_DataArrival
       
          Dim Tempo_Ingresso_Routine As Single, Tempo_uscita_Routine As Single
       
20        Tempo_Ingresso_Routine = timer2()
30        Debug.Print "Form_frmVerificaSocketServer-tcpServer_DataArrival", "Timer Ingresso = " & Format$(Tempo_Ingresso_Routine, "00000.000")

          Dim DATA As String
       
40        tcpServer.GetData DATA
50        Me.txtMESSAGGI = Prefisso_Log() & "DataArrival - " & DATA & Chr(13) & Chr(10) & Me.txtMESSAGGI

60        Tempo_uscita_Routine = timer2()
70        Debug.Print "Form_frmVerificaSocketServer-tcpServer_DataArrival", "Timer Uscita   = " & Format$(Tempo_uscita_Routine, "00000.000") & "  Tempo Impiegato = " & Tempo_uscita_Routine - Format$(Tempo_Ingresso_Routine, "00000.000")
80        Debug.Print "----------------------------------------------------------------------------------"
90        Exit Sub

eh_tcpServer_DataArrival:
100       msg_err err.Number, Erl, Error, "tcpServer_DataArrival of Documento VBA Form_frmVerificaSocketServer"
110       Resume Next

End Sub

Private Sub tcpServer_Close()
10        On Error GoTo eh_tcpServer_Close
       
          Dim Tempo_Ingresso_Routine As Single, Tempo_uscita_Routine As Single
       
20        Tempo_Ingresso_Routine = timer2()
30        Debug.Print "Form_frmVerificaSocketServer-tcpServer_Close", "Timer Ingresso = " & Format$(Tempo_Ingresso_Routine, "00000.000")

40        cmdConnesso.caption = "NON CONNESSO"
50        cmdConnesso.backColor = COLOR.COLOR_IS_red

          ' Chiusura connessione...
60        tcpServer.Close
70        Set tcpServer = Nothing
80        cmdBeginListen.caption = "NON CONNESSO"
90        cmdBeginListen.backColor = COLOR.COLOR_IS_red
       
          ' ... e successiva riapertura
100       Set tcpServer = New MSWinsockLib.winsock
110       tcpServer.LocalPort = Me.txtPorta
120       tcpServer.Listen
       
130       cmdBeginListen.caption = "Server Listening..."
140       cmdBeginListen.backColor = COLOR.COLOR_IS_green
       
150       Me.caption = "Main - <IP>" & tcpServer.LocalIP & ":<Port>" & tcpServer.LocalPort
160       Me.txtMESSAGGI = Prefisso_Log() & "tcpServer_Close - Client Disconnected - Main - <IP>" & tcpServer.LocalIP & ":<Port>" & tcpServer.LocalPort & Chr(13) & Chr(10) & Me.txtMESSAGGI

170       Tempo_uscita_Routine = timer2()
180       Debug.Print "Form_frmVerificaSocketServer-tcpServer_Close", "Timer Uscita   = " & Format$(Tempo_uscita_Routine, "00000.000") & "  Tempo Impiegato = " & Tempo_uscita_Routine - Format$(Tempo_Ingresso_Routine, "00000.000")
190       Debug.Print "----------------------------------------------------------------------------------"
200       Exit Sub

eh_tcpServer_Close:
210       msg_err err.Number, Erl, Error, "tcpServer_Close of Documento VBA Form_frmVerificaSocketServer"
220       Resume Next

End Sub

Private Sub tcpServer_ConnectionRequest(ByVal requestID As Long)
10        On Error GoTo eh_tcpServer_ConnectionRequest
       
          Dim Tempo_Ingresso_Routine As Single, Tempo_uscita_Routine As Single
       
20        Tempo_Ingresso_Routine = timer2()
30        Debug.Print "Form_frmVerificaSocketServer-tcpServer_ConnectionRequest", "Timer Ingresso = " & Format$(Tempo_Ingresso_Routine, "00000.000")
       
40        If tcpServer.State <> sckClosed Then tcpServer.Close
       
50        tcpServer.Accept requestID
       
60        cmdConnesso.caption = "CLIENT CONNESSO"
70        cmdConnesso.backColor = COLOR.COLOR_IS_green
       
90        Me.txtMESSAGGI = Prefisso_Log() & "ConnectionRequest - Richiesta connessione da ID = " & requestID & " - Remote Host = " & tcpServer.RemoteHost & "/" & tcpServer.RemoteHostIP & "/" & tcpServer.RemotePort & Chr(13) & Chr(10) & Me.txtMESSAGGI
100       Debug.Print "Richiesta connessione da ID = " & requestID
110       Exit Sub
       
120       Tempo_uscita_Routine = timer2()
130       Debug.Print "Form_frmVerificaSocketServer-tcpServer_ConnectionRequest", "Timer Uscita   = " & Format$(Tempo_uscita_Routine, "00000.000") & "  Tempo Impiegato = " & Tempo_uscita_Routine - Format$(Tempo_Ingresso_Routine, "00000.000")
140       Debug.Print "----------------------------------------------------------------------------------"
150       Exit Sub

eh_tcpServer_ConnectionRequest:
160       msg_err err.Number, Erl, Error, "tcpServer_ConnectionRequest of Documento VBA Form_frmVerificaSocketServer"
170       Resume Next

End Sub

 

[Albert D. Kallal]

Well, how cool is that! I did not realize that both the client and server were Access here.

I had "guessed" that you were communicating with some type of machine (photo copier, press machine, or whatever).
The fact that both ends are Access? Well, then that would/should facilitate some testing, or even proof of concepts on my end.

Given you outlined what events, and what features of winsock you are using? Then the "scope" of the problem/challenge is now MORE limited.

In other words, we only have to duplicate the functions/features you are using.
(this is why I kind of asked what parts and what you are doing). I mean, I have a custom outlook object in .net - and it automates Outlook (this is called from ms-access - it's a custom email class). However, when users are using remote desktop, the "object" detects this, and thus creates and automates outlook on the client side! So, did I have to re-create the WHOLE outlook object model in this custom class (that uses a custom RDP channel?

No, I ONLY created and had to write the features I needed (basic email, sendto, CC, BC, attachments). So with limited scope, I would not just say well, I want all features of outlook! So, my solution only had to deal with a few outlook features (the custom object thus sends the email, attachments to the client side, launches Outlook, sets up the email, and attaches the file(s)). (in fact, I "seralize" the custom outlook object for transmission to client side).

Anyway, the long winded point here is by limiting the scope of the problem, then the problem becomes more bite sized.

Looking at your code - quick skim?
You only using a few features, like say DataArrival, connection ok, and a few more.

So, THOSE are the only ones we need implement. Hence, our custom vb.net code just has to trigger those events you are now using, and we can write (build) those events in the vb.net code - looks to be only about 4-6 events required.

Anyway - as always - limited time, and this week I had to buy a new Android phone (dropped current one). So, that blows out a good day of time to setup that phone. As always, setup of a new device takes oh so much time, be it windows desktop, or one's phone - here it's Friday, and I'm still setting up things on my phone as time permits (and I got my phone on Monday!)

Anyway, if some time can free up (does it ever!!!), I'll see if I can post a vb.net class that gives you the winsock events you are using. As noted, since the object can "trigger" the events in your VBA code (as does winsock), then your goal of not wanting to "poll" for such code is a great goal to keep.

R
Albert

 

[amorosik]

Yes, in the example above, the communication is between PCs, or rather between individual modules on the same PC
In reality, this is only to get the code working on the client workstations
The actual communication is between program X installed on the central server, which sends commands to the individual workstations PC1, PC2, PC3, etc. PCN
The commands sent to the workstations must be processed quickly, hence the need to work in "push" mode from the server to the various client workstations, avoiding periodic polling of each client