Posts: 1
Joined: Thu Aug 25, 2005 10:04 am
Location: Canada

Serial Tools with VBA

Thu Aug 25, 2005 10:40 am

I am attempting to use serialtools with vba code in ms access. I have successfully set up a basic vba script to communicate with a scoring machine connected to COM1 by a null modem cable. Here is a quick description of the communication and my problem.
The machine is "dumb" for the most part. It relies on the software (or in this case the vba script) to query it. If it has data, it sends it in ascii format. If there is no data, it sends a single hex key back. If there is no data the software keeps asking the same query (via a hex code) until either data is present, or the script needs to be terminated.
The original software that came with the machine basically does the querying every 150 msecs.
So far I have been able to set everything up to communicate with the scoring machine...however THEN I set up a loop to continuously query the machine and use its responses accordingly. That is when my problems arose.
I set up the vba script using serialXP. When the vba script runs from a form in ms access, as I should have guessed, nothing else can happen within ms access. I must use Ctrl-Break to exit the script because the script ties up ms access totally.
In doing a bit more reading...I am "assuming" that I must either use a virtual port that links to COM1, or I must set up a separate thread to run the polling of the serial port so ms access is not frozen and can use the data from the scoring machine.
From what I have read so far, I "think" the thread concept is the way I need to do it. Please correct me if I am wrong.
As I have not used these methods before, I am hoping that someone can give me directions on which would work properly with ms access, and possibly some code so I can work from there.

Here is a copy of part of my "test" vba script. I left out the loop part where I am querying the machine. However, I put in some of the hex codes it sends to the machine with comments about them.

Private Sub Command2_Click()

Dim objport As SerialXP.Port
Dim objLicense As New SerialXP.License
Dim X, Y As String
Dim NL As String
NL = Chr$(13) + Chr$(10)

Set objLicense = New SerialXP.License
objLicense.LicenseKey = "----------"

Set objport = New SerialXP.Port

objport.NoEvents = True
objport.BaudRate = 9600
objport.ComPort = 1
objport.Handshake = RTS
objport.Enabled = True

' Initialize communication with machine by sending Hex 0xD3
' Receive reply of 200 in ascii format
' Inform me that the initialization worked

objport.Write (Chr(211))
X = objport.Read(0, 500)
MsgBox "Transmitted Initialization and got " + NL + X + NL
Call Sleep 1000

' Query machine for data by sending Hex 0X16
' If no data, machine sends null in hex
' If data present machine sends 0x01 in hex followed by data
' Inform me either of data or null return

objport.Write (Chr(22))
X = objport.Read(0, 500)
MsgBox "Transmitted data query, and got " + NL + X + NL
objport.Enabled = False
End Sub

I hope someone can tell me which way I need to go with this so that I do not freeze ms access in the process.
By the way, I found that setting up serial tools in vba is quite easy and seamless to do!

User avatar
Posts: 15067
Joined: Wed Aug 04, 2004 10:40 pm
Location: Sweden
Contact: Website

RE: Serial Tools with VBA

Thu Aug 25, 2005 10:50 am

Setting up a new thread is nothing Access will like, and I don't think it is possible from VBA without a lot of hacking.

The virtual port is not the way to go.

You should use events. Events will be posted in the main GUI thread and keep your GUI alive. Please take a look at the Event sample in the SDK and at Port.OnRead() in the reference manual here:

That is the ActiveX/VBA way of doning it.

Another option is to call DoEvents from your code every now and then. This will keep your GUI kind of alive. However - the event method described above is the recommended solution.


Franson Support

Return to “SerialTools”