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]

Re: RS232 data to Access database - general approach

Check this site:

http://www.granite.ab.ca/access/serialport.htm

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"Chris" <c_g_taylor2000 @yahoo.co.uk> wrote in message
news:33598cff.0 407020314.b4f62 46@posting.goog le.com...[color=blue]
> 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.[/color]

[James Fortune]

Re: RS232 data to Access database - general approach

c_g_taylor2000@ yahoo.co.uk (Chris) wrote in message news:<33598cff. 0407020314.b4f6 246@posting.goo gle.com>...[color=blue]
> 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.[/color]

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]

Re: RS232 data to Access database - general approach

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

perry@cplusplus .org


Chris wrote:[color=blue]
> 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.[/color]

[Chris]

Re: RS232 data to Access database - general approach

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

[jack]

Re: RS232 data to Access database - general approach

Chris wrote:[color=blue]
> 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.[/color]

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 planetsourcecod e.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]

Re: RS232 data to Access database - general approach

"Chris" <c_g_taylor2000 @yahoo.co.uk> wrote in message
news:33598cff.0 407050142.1af59 e97@posting.goo gle.com...[color=blue]
> 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[/color]

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.InputLe n = 0
'Set control's InBuffer size to 1K
MSComm1.InBuffe rSize = 1024
'Use port chosen
MSComm1.CommPor t = cboPort.Text
'9600 baud, even parity, 7 data, and 1 stop bit.
MSComm1.Setting s = "9600,E,7,1 "
'Open the port.
MSComm1.PortOpe n = True

Do
lblStatus.Capti on = "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.Capti on = "Data received - posting..."
Call PostData(InBuff er)
End If
Loop While fContinue

'Close the serial port.
MSComm1.PortOpe n = False

End Sub

[Thomas Lutz]

Re: RS232 data to Access database - general approach

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

On 2 Jul 2004 04:14:19 -0700, c_g_taylor2000@ yahoo.co.uk (Chris)
wrote:
[color=blue]
>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.
>
>HyperTermina l 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.[/color]