Challenges to connect to IP address with VBA in Ms Access
Please do not get me wrong here, we have a working COM port system well loved by everyone, now what I trying to do is to try and use the serial gadget to be accessed by multiple users via TCP/IP protocol so that the users do not need to be necessarily physically connected to the gadget as is the case now.
First let me say the VBA code below is not mine, I simply modified it to work on both 64- & 32-BIT situations. Finally, the code is able to compile as Bas module standalone function.
Help required
First let me say the VBA code below is not mine, I simply modified it to work on both 64- & 32-BIT situations. Finally, the code is able to compile as Bas module standalone function.
Option Compare Database
Option Explicit
Type Hostent
h_name As Long
h_aliases As Long
h_addrtype As String * 2
h_length As String * 2
h_addr_list As Long
End Type
Public Const SZHOSTENT = 16
'Set the Internet address type to a long integer (32-bit)
Type in_addr
s_addr As Long
End Type
'A note to those familiar with the C header file for Winsock
'Visual Basic does not permit a user-defined variable type
'to be used as a return structure. In the case of the
'variable definition below, sin_addr must
'be declared as a long integer rather than the user-defined
'variable type of in_addr.
Type sockaddr_in
sin_family As Integer
sin_port As Integer
sin_addr As Long
sin_zero As String * 8
End Type
Public Const WSADESCRIPTION_LEN = 256
Public Const WSASYS_STATUS_LEN = 128
Public Const WSA_DescriptionSize = WSADESCRIPTION_LEN + 1
'Programs
Public Const WSA_SysStatusSize = WSASYS_STATUS_LEN + 1
'Setup the structure for the information returned from
'the WSAStartup() function.
Type WSAData
wVersion As Integer
wHighVersion As Integer
szDescription As String * WSA_DescriptionSize
szSystemStatus As String * WSA_SysStatusSize
iMaxSockets As Integer
iMaxUdpDg As Integer
lpVendorInfo As String * 200
End Type
'Define socket return codes
Public Const INVALID_SOCKET = &HFFFF
Public Const SOCKET_ERROR = -1
'Define socket types
Public Const SOCK_STREAM = 1 'Stream socket
Public Const SOCK_DGRAM = 2 'Datagram socket
Public Const SOCK_RAW = 3 'Raw data socket
Public Const SOCK_RDM = 4 'Reliable Delivery socket
Public Const SOCK_SEQPACKET = 5 'Sequenced Packet socket
'Define address families
Public Const AF_UNSPEC = 0 'unspecified
Public Const AF_UNIX = 1 'local to host (pipes, portals)
Public Const AF_INET = 2 'internetwork: UDP, TCP, etc.
Public Const AF_IMPLINK = 3 'arpanet imp addresses
Public Const AF_PUP = 4 'pup protocols: e.g. BSP
Public Const AF_CHAOS = 5 'mit CHAOS protocols
Public Const AF_NS = 6 'XEROX NS protocols
Public Const AF_ISO = 7 'ISO protocols
Public Const AF_OSI = AF_ISO 'OSI is ISO
Public Const AF_ECMA = 8 'european computer manufacturers
Public Const AF_DATAKIT = 9 'datakit protocols
Public Const AF_CCITT = 10 'CCITT protocols, X.25 etc
Public Const AF_SNA = 11 'IBM SNA
Public Const AF_DECnet = 12 'DECnet
Public Const AF_DLI = 13 'Direct data link interface
Public Const AF_LAT = 14 'LAT
Public Const AF_HYLINK = 15 'NSC Hyperchannel
Public Const AF_APPLETALK = 16 'AppleTalk
Public Const AF_NETBIOS = 17 'NetBios-style addresses
Public Const AF_MAX = 18 'Maximum # of address families
'Setup sockaddr data type to store Internet addresses
Type sockaddr
sa_family As Integer
sa_data As String * 14
End Type
Public Const SADDRLEN = 16
'Declare Socket functions
Public Declare PtrSafe Function closesocket Lib "wsock32.dll" (ByVal s As Long) As Long
Public Declare PtrSafe Function connect Lib "wsock32.dll" (ByVal s As Long, addr As sockaddr_in, ByVal namelen As Long) As Long
Public Declare PtrSafe Function htons Lib "wsock32.dll" (ByVal hostshort As Long) As Integer
Public Declare PtrSafe Function inet_addr Lib "wsock32.dll" (ByVal cp As String) As Long
Public Declare PtrSafe Function recv Lib "wsock32.dll" (ByVal s As Long, ByValbuf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare PtrSafe Function recvB Lib "wsock32.dll" Alias "recv" (ByVal s As Long, buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare PtrSafe Function send Lib "wsock32.dll" (ByVal s As Long, buf As Any, ByVal buflen As Long, ByVal flags As Long) As Long
Public Declare PtrSafe Function socket Lib "wsock32.dll" (ByVal af As Long, ByVal socktype As Long, ByVal protocol As Long) As Long
Public Declare PtrSafe Function WSAStartup Lib "wsock32.dll" (ByValwVersionRequired As Long, lpWSAData As WSAData) As Long
Public Declare PtrSafe Function WSACleanup Lib "wsock32.dll" () As Long
Public Declare PtrSafe Function WSAUnhookBlockingHook Lib "wsock32.dll" () As Long
Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (hpvDest As Any, hpvSource As Any, ByVal cbCopy As Long)
Sub StartIt()
Dim StartUpInfo As WSAData
'Version 1.1 (1*256 + 1) = 257
'version 2.0 (2*256 + 0) = 512
'Get WinSock version
Sheets("Sheet1").Select
Range("C2").Select
Version = ActiveCell.FormulaR1C1
'Initialize Winsock DLL
x = WSAStartup(Version, StartUpInfo)
End Sub
- Open Socket
- Send Command
- Receive Command
- Close Connection
- Clean Up
Help required
- Unfortunately, they are not compiling that is where I need your help if possible.
- How to bring the IP address , example 192.168.8.101 and Port number 8.8.8.8
- Suppose the business logic is represented by strData, then how do I factor it in the code
'OpenSocket
Function OpenSocket(ByVal Hostname As String, ByVal PortNumber As Integer) As Integer
Dim I_SocketAddress As sockaddr_in
Dim ipAddress As Long
ipAddress = inet_addr(Hostname) '...........(1)
'Create a new socket
socketId = socket(AF_INET, SOCK_STREAM, 0) '
If socketId = SOCKET_ERROR Then '
MsgBox ("ERROR: socket = " + Str$(socketId)) '...........(2)
OpenSocket = COMMAND_ERROR '
Exit Function '
End If '
'Open a connection to a server
I_SocketAddress.sin_family = AF_INET '
I_SocketAddress.sin_port = htons(PortNumber) '...........(3)
I_SocketAddress.sin_addr = ipAddress '
I_SocketAddress.sin_zero = String$(8, 0) '
x = connect(socketId, I_SocketAddress, Len(I_SocketAddress)) '
If socketId = SOCKET_ERROR Then '
MsgBox ("ERROR: connect = " + Str$(x)) '..(4)
OpenSocket = COMMAND_ERROR '
Exit Function '
End If '
OpenSocket = socketId
End Function
'SendCommand
Function SendCommand(ByVal command As String) As Integer
Dim strSend As String
strSend = command + vbCrLf
Count = send(socketId, ByVal strSend, Len(strSend), 0)
If Count = SOCKET_ERROR Then
MsgBox ("ERROR: send = " + Str$(Count))
SendCommand = COMMAND_ERROR
Exit Function
End If
SendCommand = NO_ERROR
End Function
'RecvAscii
Function RecvAscii(dataBuf As String, ByVal maxLength As Integer) As Integer
Dim c As String * 1
Dim length As Integer
dataBuf = ""
While length < maxLength
DoEvents
Count = recv(socketId, c, 1, 0) '
If Count < 1 Then '
RecvAscii = RECV_ERROR '............(1)
dataBuf = Chr$(0) '
Exit Function '
End If '
If c = Chr$(10) Then '
dataBuf = dataBuf + Chr$(0) '............(2)
RecvAscii = NO_ERROR '
Exit Function '
End If '
length = length + Count '............(3)
dataBuf = dataBuf + c '
Wend
RecvAscii = RECV_ERROR
End Function
'CloseConnection
Sub CloseConnection()
x = closesocket(socketId)
If x = SOCKET_ERROR Then
MsgBox ("ERROR: closesocket = " + Str$(x))
Exit Sub
End If
End Sub
EndIt
Sub EndIt()
'Shutdown Winsock DLL
x = WSACleanup()
End Sub
The error means, that you have declared
Type sockaddr_in
multiple times.
Type sockaddr_in
multiple times.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I slightly disagree. The business procedure SendInvoice must not know that it is working with TCP/IP or a serial port.
See my outline of how to approach it here.
See my outline of how to approach it here.
Sure, you could abstract it out a little more, but if all you are ever going to do is TCP/IP, then why bother.
But if you had the RS-232 as well, then I would change this to SendInvoiceViaTCPIP(), write a SendInvoiceViaRS232() with similar logic, but for RS232, and then a SendInvoice() that called the appropriate one based on a table flag. The TCP/IP and RS232 open's, close's, send/receive data would all be lib/helper functions under that.
Jim.
But if you had the RS-232 as well, then I would change this to SendInvoiceViaTCPIP(), write a SendInvoiceViaRS232() with similar logic, but for RS232, and then a SendInvoice() that called the appropriate one based on a table flag. The TCP/IP and RS232 open's, close's, send/receive data would all be lib/helper functions under that.
Jim.
Sure, you could abstract it out a little more [..]It is called interface segregation and dependency inversion principle a.
And I recommended this approach to the OP from the beginning even for the serial port code.. and a lot of the troubles of the OP so far where due to missing separation of conferences.
[..], but if all you are ever going to do is TCP/IP, then why bother?As it sometimes really bothers me:
There are certain principles of software development: The SOLID principles.
While they where developed around object oriented programming, they apply to almost every aspect of software development. They can be applied in engineering in general. The can be applied even in areas like process management.
Then there is the clean code movement based mainly on Uncle Bob's books.
In the end it is about my (our) professions honor, do to the things the right way. For every mid to long term project it is necessary as it will for sure save time and budget.
Some good references there and some concepts do apply, but there's one problem when you are talking about VBA; it's an object based language, not object orientated.
Where it falls short of OOP is that it doesn't have full interface inheritance. So sticking to OOP based concepts and principles doesn't always quite fit.
Besides which, OOP has it's own design problems, which often leads to overly complicated and bloated software. Yes, it has some advantages, and following OOP principles solves some problems in code design and re-use, but it also has it's own problems as well.
When I approach VBA programming, it's the KISS method. I don't see any reason to overly complicate something like this. For example, with the example you gave, should I need to change a complier constant to switch communication methods? That's a code change and seems a little inflexible to me. Am I going to be able to hold multiple conversations with this device? No, so why bother with a class and the overhead of instantiating it, keeping track of it, etc?
Far easier to keep it simple with just a few procedures.
Jim.
Where it falls short of OOP is that it doesn't have full interface inheritance. So sticking to OOP based concepts and principles doesn't always quite fit.
Besides which, OOP has it's own design problems, which often leads to overly complicated and bloated software. Yes, it has some advantages, and following OOP principles solves some problems in code design and re-use, but it also has it's own problems as well.
When I approach VBA programming, it's the KISS method. I don't see any reason to overly complicate something like this. For example, with the example you gave, should I need to change a complier constant to switch communication methods? That's a code change and seems a little inflexible to me. Am I going to be able to hold multiple conversations with this device? No, so why bother with a class and the overhead of instantiating it, keeping track of it, etc?
Far easier to keep it simple with just a few procedures.
Jim.
Don't mix up interfaces in Java or .NET with the generic term interface which only denotes the formalized, documented way to access methods.
The SOLID principles are about the latter. Thus you can take those principles and apply it for example to processes and sub processes in process management.
I agree with KISS. But SOLID and clean code does not contradict KISS.
And the sample I gave is a way to do it in VBA. Not necessarily the only one. And it is also a simplified one. The key is that it is a single change which does no change the semantics and it is for demonstration purpose. I used it because the ÓP needs mainly one strategy. Would this be my project I would use a factory method instead. But I don't think throwing another pattern into the ring is necessary here, is it?
The SOLID principles are about the latter. Thus you can take those principles and apply it for example to processes and sub processes in process management.
I agree with KISS. But SOLID and clean code does not contradict KISS.
And the sample I gave is a way to do it in VBA. Not necessarily the only one. And it is also a simplified one. The key is that it is a single change which does no change the semantics and it is for demonstration purpose. I used it because the ÓP needs mainly one strategy. Would this be my project I would use a factory method instead. But I don't think throwing another pattern into the ring is necessary here, is it?
Ambiguous name detected means you have sockaddr_in defined two times in the current scope.
For example, if you imported this code twice under different module names. sockaddr_in would be defined in two different places. Do a find for sockaddr_in in the code to find the two definition statements.
When you call OpenSockect, you would supply the IP address and port:
Dim intRet as Integer
intRet = OpenSocket("192.168.8.101", 8888)
Port #'s range from 1 - 65535 and are expressed as a number. The device is set to "listen" for incoming requests on a port, which is the one you want to use in the code.
You would not modify these routines. These form a "library" that you just call to carry out a task. Instead, you would write another routine just as you outlined:
What goes on in step #2 I cannot tell you because I don't know what process needs to be carried out, or what commands the device understands.
Note that the above is similar to the RS232 work you did (open a port, send data/receive data, close the port), but there you made the mistake of modifying the routines you had been given.
You want to write your own routine, say "SendInvoice()", which will carry out the four steps.
Jim.