RS232 data to Access database - general approach

[Chris]

Here's the situation:

I work at a scientific institution and I have a portable electronic
device which is used to take measurements. The device produces a very
small amount of numerical data, about 10 to 15 numbers per
measurement. This operation is performed frequently and I would like
to implement an automatic solution that moves this data across a
windows network and into the fields of an Access database application.

The device has an RS232 port to transmit the data each time a
measurement is made. I have tested this and can save the data to a
text file by using the HyperTerminal application which ships with
windows 98.

HyperTerminal is fine but in order to read in data from the device I
have to run the HyperTerminal application and manually open a serial
port before the measurement is executed. I need a solution that opens
the port automatically i.e. the user only has to operate the device
and not the computer terminal that the device is connected to. The
solution should either send data straight to Access or store the data
in a text file for middleware to deal with.

What should be my general approach? Would it be possible to implement
a solution in, for example java, or could I use Access VBA?

Maybe I don't need to write any code at all if there is middleware
available that could do the transfer?

Any suggestions appreciated.

 

[PC Datasheet]

Check this site:
http://www.granite.ab.ca/access/serialport.htm

 

[James Fortune]

Here's the situation:

I work at a scientific institution and I have a portable electronic
device which is used to take measurements. The device produces a very
small amount of numerical data, about 10 to 15 numbers per
measurement. This operation is performed frequently and I would like
to implement an automatic solution that moves this data across a
windows network and into the fields of an Access database application.

The device has an RS232 port to transmit the data each time a
measurement is made. I have tested this and can save the data to a
text file by using the HyperTerminal application which ships with
windows 98.

HyperTerminal is fine but in order to read in data from the device I
have to run the HyperTerminal application and manually open a serial
port before the measurement is executed. I need a solution that opens
the port automatically i.e. the user only has to operate the device
and not the computer terminal that the device is connected to. The
solution should either send data straight to Access or store the data
in a text file for middleware to deal with.

What should be my general approach? Would it be possible to implement
a solution in, for example java, or could I use Access VBA?

Maybe I don't need to write any code at all if there is middleware
available that could do the transfer?

Any suggestions appreciated.

http://www.tinaja.com (Don Lancaster) talks about the Basic Stamp
microprocessor that, from what it appears, can be programmed in VBA
style coding. I would be interested in hearing about anyone else who
has used this processor to log data outside of the computer into
Access or to control a machine/device/process.

James A. Fortune

 

[perry]

well you on a java board so the default answer is going to be to get
java involved and your lovely project can have many elegant solutions.
however, right now, you just want the job done. if you have a few hours
(or days) you can fiddle with it using Access VBA. windows people would
want you to launch a major campaign to active your downloading software
the moment the user connects the handheld to the serial port. this could
require praying to jesus or buddah as 99% of your software developement
time and money could go to that alone.

depending on how fast the user wants his handheld back, you effectively
have to either run an app that checks the port for the presense of the
handheld every five minutes or an interrupt that will activate a service
routine if there's change on the port.

the easu part is writing to an access database or a text file.

wish i were there, i'd spend the day at it. ofcourse, i'd want you to
consider linux but hey, you can't have everything...

cheers

- perry

(e-mail address removed)

 

[Chris]

So are you saying that I can't just open the port automatically when
the data arrives? Do I have to have it open already in preparation
for the data to arrive - I suppose this seems like an obvious thing to
say. And I guess it isn't a good idea to have the port open the whole
time just incase some data comes along?

Maybe the user could just click on a "prepare for measurement" button
on a small java app on the desktop which opens the port and then shut
it again after?

Chris

 

[Brendan Reynolds]

Who are the manufacturers of the device? Often, manufacturers' web sites may
have examples showing how to communicate with the device.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[jack]

Here's the situation:

I work at a scientific institution and I have a portable electronic
device which is used to take measurements. The device produces a very
small amount of numerical data, about 10 to 15 numbers per
measurement. This operation is performed frequently and I would like
to implement an automatic solution that moves this data across a
windows network and into the fields of an Access database application.

The device has an RS232 port to transmit the data each time a
measurement is made. I have tested this and can save the data to a
text file by using the HyperTerminal application which ships with
windows 98.

HyperTerminal is fine but in order to read in data from the device I
have to run the HyperTerminal application and manually open a serial
port before the measurement is executed. I need a solution that opens
the port automatically i.e. the user only has to operate the device
and not the computer terminal that the device is connected to. The
solution should either send data straight to Access or store the data
in a text file for middleware to deal with.

What should be my general approach? Would it be possible to implement
a solution in, for example java, or could I use Access VBA?

Maybe I don't need to write any code at all if there is middleware
available that could do the transfer?

Any suggestions appreciated.

Windows NT/2000 and XP has strict control over I/O ports and will cause
an exception (privileged instruction) error if an attempt is made to
access a port that you are not privileged to write to. Applications such
as MS Office run in a ring 3 privilege level and do not have direct
write access to the port without the use of system calls using the API.

I suggest writing VBA code to use a OCX/DLL and provide access that way.
I'm sure you can find reference material on www.allapi.net or
http://msdn.microsoft.com or planetsourcecode.com may be of some help to
you.

You could instead use the DDE feature of Microsoft Office to communicate
with HyperTerminal

If you can find middleware or code it, you could use the import tools in
access (simple vba macro) to import the data.

 

[Ron Hinds]

So are you saying that I can't just open the port automatically when
the data arrives? Do I have to have it open already in preparation
for the data to arrive - I suppose this seems like an obvious thing to
say. And I guess it isn't a good idea to have the port open the whole
time just incase some data comes along?

Maybe the user could just click on a "prepare for measurement" button
on a small java app on the desktop which opens the port and then shut
it again after?

Chris

there is no penalty for having the port open all the time. I would suggest
using a pure VBA solution in Access. All you need to do is include the
MSCOMM32.OCX in your app - it is a MS-supplied ActiveX control that manages
serial ports. It is very easy to use. I wrote an interface to our phone
system to automatically log all calls in less than a day using the control.
Here is a sample code snippet. MSComm1 is the OCX, fContinue is a Global
Boolean. I start the loop when a button is clicked, and stop it when the Esc
key is pressed (or the app closes):

Private Sub DataLoop()

Dim InBuffer As String

'Tell the control to read entire InBuffer when Input
'is used.
MSComm1.InputLen = 0
'Set control's InBuffer size to 1K
MSComm1.InBufferSize = 1024
'Use port chosen
MSComm1.CommPort = cboPort.Text
'9600 baud, even parity, 7 data, and 1 stop bit.
MSComm1.Settings = "9600,E,7,1"
'Open the port.
MSComm1.PortOpen = True

Do
lblStatus.Caption = "Waiting for data..."
'Send start command
MSComm1.Output = Chr$(14)
'Clear input InBuffer
InBuffer = vbNullString
Do
DoEvents
InBuffer = InBuffer & MSComm1.Input
Loop Until InStr(InBuffer, vbCrLf) Or Not fContinue

'Send stop command
MSComm1.Output = Chr$(15)

If Len(InBuffer) > 0 Then
lblStatus.Caption = "Data received - posting..."
Call PostData(InBuffer)
End If
Loop While fContinue

'Close the serial port.
MSComm1.PortOpen = False

End Sub

 

[Thomas Lutz]

The easiest tool that I know of for feeding serial data directly into
an Access database application would be a product called WinWedge from
TAL Technologies.
WinWedge is a serial communications program that is designed to run in
the background and feed incoming serial data directly to other Windows
programs. It works by either "stuffing the keyboard buffer" so that
the other program sees the data as if it were being typed in on the
keyboard or it can also feed data to other programs using a feature of
Windows called Dynamic Data Exchange (DDE). Access has excellent
support for DDE and it works great with WinWedge to do exactly what
you are looking to do.
For more information about WinWedge visit:
http://www.taltech.com/products/winwedge.html