read data stream. from aaa.bbb.ccc.ddd port e in vba and convert to text file

By the way, I have found out most machines either Win XP or Win 7 judging by the PCs in my house don't have MSWinsock.ocx installed as default so how do I get that and package it for deployment?

Ultimately the stream of data is ASCII text that will be formatted into csv and parsed into MS Access fields or MS Excel Cells.

In this excellent article:

"Create a Syslog Sender/Receiver Using the MS Winsock Control"

http://www.devx.com/vb/Article/42242

you'll see a link on page 1 "several Places". The link is still live.
I don't know if it will work with Access 2013, but it could be worth to try.

The demo uses the default port for syslog, but you can easily change that.

/gustav

Hi,

Just spent a few hours trying to track down the winsock ocx and to remove the license issue.  This is harder than it looks.  While I persevere with that is it possible to use the system.net.sockets methods  within vba?

Backdooring the winsock seems underhand if there is a system component that I can program easily to accept connections from within excel or access.

I have bookmarked the devx page that tutorials the syslog article so I can reverse engineer what they have done.

For instance, I am presuming that the code at top of page 2 will allow me to connect to my IP address and port and as it stands, each time the DataArrival fires it would show the received bytes.  A simple append via ampersand would allow the box to accumulate the data?  Not a lot of code so a great start from that point of view.

The next problem I foresee is that each of my 4 senders have different IP addresses but same port.  How do I capture each independently?

Ta

Clay

I can't tell. The WinSock control is pre Windows Vista so it may require a little tweaking with user rights, and will only work with 32-bit Access.

You could probably create a dll using system.net.sockets and reference that from VBA.

One receiver will listen to one port and accept data from many sources. However, using UDP as syslog does, is "one-way" only so while it is extremely fast, you will have no confirmation. That is discussed in the article, and a simple method is to send everything three times, another is to use TCP.

Creating the four files is really simple. You need to identify the sender (IP address) and then write to the file and append a CrLf. The syslog demo writes to a database but that isn't much different. It should be easy to adopt. Or you could keep the database and then filter and export the data to the text files when needed.

/gustav

Hi Gustav,

I dont have an issue with user rights, these are all closed systems that I manage and after the tweaks I made to my telnet batches, the data is being captured.  Now to do it more elegantly and parse it into a database too.

So I have finally loctaed winsock and got it to ignore the license error and ready to start programming.  I also found this on the net for examples within Access 2k:
 http://support.microsoft.com/kb/209905

So I think I am well on my way now to sorting out a little prog within Access to catch and parse into fields.

Many Thanks,

Will let you know how I get on but it may take all week as this is essentially a little side project....  I am also in UAE so replies take a while with time zone differences.

Clay

OK, fine. That was the tricky part.

You may still be able to use the application from DevX. I guess you could just replace the syslogencode and -decode function with those of yours.

Also, be aware of the Unicode issue:

Option Compare Database
Option Explicit

Private Sub btnSend_Click()
  
  Dim strPackage  As String
  
  strPackage = SyslogPackageEncode()
  Me!Winsock0.SendData CStr(strPackage)

End Sub

Select allOpen in new window

Figure 2. Test Form to Send a Syslog Message: This is the simple test form to send a syslog message with the current time.


This is the minimal code to create a form for sending a syslog message. Note the missing equal sign following SendData. But why, you may ask, is CStr used? The variable strPackage is a string, so what's the deal? The reason is that Access handles strings as Unicode, thus if you passed the string variable directly like this:

Me!Winsock0.SendData strPackage

The WinSock control would send a Unicode byte stream when a clean ASCII byte stream is what you expect. CStr converts the Unicode string variable to a simple ASCII byte stream. For testing, you could send any string. However, by calling the function SyslogPackageEncode from the basSyslog module, you generate a fully qualified syslog message with the current time and default values for all other parameters, for example:  
Figure 3. Configure a Typical Router to Send Out Syslog Messages: Here is how to configure a router to send syslog messages.

/gustav

Hi Gustav,

Dont forget my 'servers' are already transmitting this data on a specific IP Address and Port, so I just need 4 instances of client each collecting data and parsing it into something I can manipulate.

MY pseudo code would go something like this:

A    Start time check

B    Connect to IP A port E
C    Connect to IP B port E
D    Connect to IP C port E
E    Connect to IP D port E

F    On trigger of DataArrival for A, convert to ascii (using CSTR as shown above) add a Cr Lf and append to FileA.txt
G    Do same for B, C, D, and append to FileB,C,D.txt

H    24 hours complete?
I     Start a new capture for 4 files (so we dont lose data)
J    Close all 4 previous and rename as DateFileA,B,C,D
K   Read in each line from DateFileA and extract data for each specific field (using rules to be defined which should be reasonably simple as we know what each line should contain and how it is delimited) and import into accdb table
L    Repeat for DateFileB,C,D

M    Loop


How does that sound?

Clay

I'm not sure. F sounds like each of your four machines initiates the transmission to a given IP address and deliver data; then starts collecting new data and, later, opens the transmission again to deliver, and so on.
If so, you need only one receiving server (one address, one port) which sits waiting for the four machines to call. This is much like syslog operates.

However, if the four machines are real servers, they don't do anything before called.
If so, item F must be changed, so that your collecting machine at some points calls the four machines one by one and retrieves data, writes data, waits some time, then call each machine, and so on.

In neither case I see a reason to use different ports.

/gustav

Ok

I will try one step at a time by receiving from 1 server and getting that to work then progress onto 2, 3 and 4 once I have proven the steps with just 1 machine.

I should be able to accept at least 2 streams at a time because I already do that with a telnet session.

Presumably a separate winsock connected TCP will be able to collect the data from each source or I am in multithreading territory there...?

Oh well time to start coding... well tomorrow anyway.

Thanks for the guidance so far.

Clay

Hi,

So some quite quick success.  I created a server and client form in Access. Populated with IP Address, Port, received message and send message text boxes. A connect cmd, send cmd and disconnect cmd buttons.

The only difference between the 2 being connect was on client replaced with Listen on Server.  So opening server first set it to listen, connected with client.  then able to send messages between the 2.  --- SUCCESS.

So next I applied my rationale.  All 4 servers in the real world are setup to send message streams already so I just need a passive client , who once connected, just reads the incoming data and saves to file and after 24 hours starts a new one.

I forgot to bring the code with me but essentially, my winsock client has 3 routines, one to connect, one to collect data, parse to a textbox and then when it reaches a certain size parse to a .txt file, and one form load to see if the file exists and if it does rename it to something related to date and create a new file.

So the concept works for one winsock and one server.  Next step is to expand this to all 4 servers.  So do I create 4 instances of form or 1 form with 4 winsocks?

Next,  and more importantly, I am not doing any error checking or failure trapping.  What connection/disconnection errors should I look for?  Is there any state that the client could get into that would affect the server?  If so what is it and how would I mitigate.  I want this client to be as a passive as possible with little or no replies going back to server other than the initial connect.

On Thursday I will post my code for the benefit of anyone else who steps into the world of winsocks.

Regards,

Man I must have been tired last night, as I did save the code to stick before leaving here it is:

Option Compare Database

Private Sub cmdConx_Click()
   txtIP.SetFocus
   sockMain.RemoteHost = txtIP.Text
   txtPort.SetFocus
   sockMain.RemotePort = txtPort.Text
   sockMain.Connect
End Sub

Private Sub cmdDis_Click()
sockMain.Close

End Sub

Private Sub cmdSend_Click()
   txtSendText.SetFocus
   sockMain.SendData txtSendText.Text
End Sub

Private Sub Form_Close()
sockMain.Close

End Sub

Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   Dim tmpData As String
   FNold = "C:\Documents and Settings\All Users\Documents\DataCapture\DataCap.txt"
   sockMain.GetData strData, vbString
   txtStatus.SetFocus
   txtStatus.Text = txtStatus.Text & strData & vbCrLf
   If Len(txtStatus.Text) > 1024 Then
     tmpData = txtStatus.Text
     Open FNold For Append As #1
     Write #1, tmpData
     Close #1
     txtStatus.Text = ""
   End If
End Sub

Private Sub Form_Load()
  
Dim fso As Object
Dim fsoFile As Object
Dim FNold As String
Dim FNnew As String

FNold = "C:\Documents and Settings\All Users\Documents\DataCapture\DataCap.txt"
'*******Check whether output file exists**********
If Len(Dir(FNold)) = 0 Then
'*******This does not exist so create it**********
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set fsoFile = fso.CreateTextFile(FNold, True)
  fsoFile.Close
Else
'*******File exists so rename it**********
  FNnew = Left(FNold, (Len(FNold) - 4)) & "-" & Day(Now) & Month(Now) & Year(Now) & "-" & CStr(Hour(Now)) & CStr(Minute(Now)) & ".txt"
  Name FNold As FNnew
'*******Now Create a new one**********
  Set fso = CreateObject("Scripting.FileSystemObject")
  Set fsoFile = fso.CreateTextFile(FNold, True)
  fsoFile.Close
End If

End Sub

Select allOpen in new window

Connect Sub
using 2 text boxes for IP and Port
Then simple call to winsock method

Disconnect Sub
simple call to winsock to close port  (don't know if this is the best way to do this but all I could find)

Send Sub
Not required any longer but shows how a text box can be used to send a message

DataArrival Sub
sets up a couple of variables to tidy the received data before first displaying in the status text box.  Then once the data has mostly filled the box, it then sends it to file (hopefully with the extra line breaks included).  Important to close the file each time.
Clear the text box and wait for next set of data

Form Load Sub
Just a test of concept.  Ultimately this will become a function triggered every 24 hours to see if its the first time run or subsequent.  If first time then create datacap.txt as the file for storage, otherwise rename it as 24 hours has lapsed and we need to create a new one for the next 24 hours.

And there you have it.

As stated above, now I want to make it bullet proof and ensure the client doesn't do anything to disrupt the server by handling any exceptions.  This is where in depth knowledge from an expert is essential.  What do I need to look out for and how would I trap it and mitigate any errors?

Many thanks,

Clay

Great!

I still believe one receiver will do. It is extremely unlikely that two or more of the senders will send at the same time. At the receiver, each sender can be identified by their IP address.

As for error handling, you will need some at the sender in case it cannot connect, and if you use UDP you may have to send each message several times as UDP packets are not guarantied to reach the receiver. With TCP, however, you first establish a connection and only then you start transmission.

/gustav

Okay,

I have read a bit about multi receive by setting up an array of sockets.  I presume a slight change such as :

'*************This to connect to multiple servers stored in Access Table *******
Private Sub cmdConx_Click()
dim db as database
dim rs as dao.recordset
dim socket as integer

set db= currentdb
set rs= "Select * FROM tblServer", dbdynaset)
socket = 0
rs.MoveFirst
do while not rs.eof

   sockMain(socket).RemoteHost = rs![IPAddr]
   sockMain(socket).RemotePort = rs![IPPort]
   sockMain(socket).Connect
socket = socket +1
rs.MoveNext
Loop

Select allOpen in new window

Sorry if that's slightly wrong but that's access code written from memory but serves the purpose.

Now the tricky part... How do I determine which server is talking to me if I have four sockets now waiting for data called
sockMain(0)
sockMain(1)
sockMain(2)
sockMain(3)

How do I determine within my dataArrival Code which one has arrived?

Is it 4 subs such as :
Private Sub sockMain(0)_DataArrival(ByVal bytesTotal As Long)
.....
End Sub

AND
Private Sub sockMain(1)_DataArrival(ByVal bytesTotal As Long)
....
End Sub
 
etc

I will carry on looking but any pointers gratefully received...

Ta

Clay

You should use one port only. To listen to several ports, you would need as many receiver instances running.

/gustav

Okay

so how does that look in code.....

Do I raise 4 connects as shown above or 4 data receives and if so how do I know which is which?  In the TCP/IP data header will be a MAC address and a IP Address and Port from originator but I  cant see anywhere to capture that just a data packet....

Regards,

Clay

I believe you should catch the connection as you already do. It will hold the sending IP address which you will need to route the data to the corresponding file.

Then receive and store the data. Finally the sender will close the connection.

As I recall, the syslog receiver holds the received data in a buffer to not slow things down, then saves the data. I guess your data volume per transmission is quite low so you could do the same.

/gustav

That still doesn't answer how I setup 4 connections to the 4 sending devices

If you really want this - just launch the app four times with different parameters.

/gustav

I think we misunderstand each other.  My client will need to receive 4 streams and direct each to a specific file.  if I am able to use the one winsock receiving on its 1 port then how do I determine which server it is listening to?  Is there info in the data packet that the winsock knows which one the data is from?

Is that a better rephrase of the question?

Sorry. When the receiver detects a connection and raises the event, a property holds the IP address of the sender which you can use to identify the sender.

If you study the syslog demo, the property RemoteHostIP holds the sending IP address.

/gustav

Fantastic thank you.  I will devise some code and try it out after the weekend.

Thanks for understanding.

Gustav,

I decided to concentrate on receiving everything properly and getting it stored so that I can then parse it properly into an accdb table.

However, with my data arrival only slightly changed, I now throw an error 7878 Data has been changed.  Sometimes the code will carry on but if I close and open my form again I get the same thing.

7878 appears to be an issue when 2 users are writing to the db but there is no writing to the table at this point, I am just receiving the data and sending to file.

Could you look at the code and see if there is anything I have missed?  We did say that I have no error handling included yet.  Is this one of those things that needs including?

The line that gets highlighted as error 7878 is txtStatus.Text = txtStatus.Text & strData

Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   Dim tmpData As String
   strData = ""
   FNold = txtPath & txtItem & "DataCap.txt"
   sockMain.GetData strData, vbString
   txtStatus.SetFocus
   
   txtStatus.Text = txtStatus.Text & strData
   If Len(txtStatus.Text) > 1024 Then
     tmpData = txtStatus.Text
     Open FNold For Append As #1
     Write #1, tmpData
     Close #1
     txtStatus.Text = ""
   End If
End Sub

Select allOpen in new window

Is there a need to do a DoEvents() after showing the data?  Thinking about it, maybe I should change the code to this instead so that the dta gets written to file straight away and the text box is not holding up proceedings?

Private Sub sockMain_DataArrival(ByVal bytesTotal As Long)
'*******Now parse the data out to our capture file**********
   Dim FNold As String
   Dim strData As String
   strData = ""
   FNold = txtPath & txtItem & "DataCap.txt"
   sockMain.GetData strData, vbString

     Open FNold For Append As #1
     Write #1, strData
     Close #1

   txtStatus.SetFocus
   txtStatus.Text = txtStatus.Text & strData
   If Len(txtStatus.Text) > 1024 Then
     txtStatus.Text = ""
   End If
End Sub

Select allOpen in new window

Incidentally what's the difference between receiving as bytes and receiving as vbstring?

Kind regards,

To answer my own question about bytes and strings the answer is nothing as far as I can see.

After a lot of head scratching it was down to an update to the datacapture table using currentdb.execute on the connect sub.  I see no reason for this so I have fixed issue by removing the flag.  I just wanted to show the user that data for this server was being captured but I will find another way.
So now all is well and I can parse the data to file no issues.

Except, why am I receiving data with quotes at start and end?  Not really a problem if I just strip them out but its just odd....

Thanks Gustav.  I will take that as the final piece of the puzzle.  Incidentally, I have now constructed a form that is able to receive UDP or TCP data, correctly connecting and disconnecting as required.
I will put this into action back at work tomorrow with the added timer function that decides a day has gone by and a new file can be created.
I have a little work to do to break up each line of data and parse it to a new table but that's the easy bit :-)

Thanks for guiding me through.  Maybe it will be beneficial to others who are taking their first steps in winsocks although I wonder what a similar operation would look like in sockets.net

A project for another day.

Anybody looking for a solution should read the whole saga as the final answer is only a part of the overall solution.  Gustavs guidance was key to keeping on the right track

I'm glad you worked it out, and thanks for the feedback.

/gustav