RS-232 and USB hooks in Access

[Paul E. Schoen]

I would like to be able to establish communication with an external serial
port device (or a USB device) from an Access form. I have recently
purchased and started using Access 2007, but I am mostly familiar with
Access 97 that I have used until recently. I have limited knowledge of VB
and I am more comfortable programming in Borland Delphi and various forms
of C, but I know that VBA is the standard and I may just need to use it.

I have found a commercial product
http://www.taltech.com/products/winwedge.html that costs about $250-$500
and would probably be the easiest solution, since I will be using it in a
commercial application where development time = money. But I also found a
free VB library of comm functions at
http://www.granite.ab.ca/access/serialport.htm, and it may be ultimately
more satisfying and educational to have the low-level code.

My immediate goal would be to have a form with various text boxes, some of
which would be used for test data which is displayed on a device that can
connect via an RS-232 port. In its present form it will send the data out
as a formatted string, which would need to be parsed to insert the data
into the proper text boxes.

The next step would be to create a toolbar which would have various
controls which would send commands to the remote device to set ranges,
initiate testing, and perform other functions. I would need to modify its
firmware to accept commands, but I have done that previously when I made a
version that communicated with a hand-held terminal. This toolbar could be
included in the form, but preferably would be a moveable and dockable
toolbar that could be positioned as desired on top of the form.

I would also like to be able to display continuously the immediate value of
current or voltage as well as elapsed time, which I also accomplished
previously by sending formatted strings at a rate of about 4/second.

Eventually, the connected instrument will be redesigned with a USB
interface. It could very easily be configured as a virtual serial port, and
the Access application would not need to be modified. But it might be more
efficient to use direct send and receive of data on the USB port, which
would involve a different, and perhaps simpler, software interface.

TIA for any ideas and suggestions. I'd rather discuss this with experts and
people with experience before jumping in and learning by making mistakes.

Paul

 

[Albert D. Kallal]

My immediate goal would be to have a form with various text boxes, some of
which would be used for test data which is displayed on a device that can
connect via an RS-232 port. In its present form it will send the data out
as a formatted string, which would need to be parsed to insert the data
into the proper text boxes.

I don't see a problem with the above. The only un-known issue (at least for
me) is if the above sammple code librieas work correclty when USB ports are
re-mapped as serial ports. A lot of computers don't have serial ports
anymore, so one of those USB->serial port cables + software likey will be
used here. So, if the usb->serial cables work with the above software, then
I think you much home free here. Also, often manufactors do include some
sample dirvers + code for their devcies.

The next step would be to create a toolbar which would have various
controls which would send commands to the remote device to set ranges,
initiate testing, and perform other functions.

Well, you can build a form that has buttions beside each other accross the
top that will "look" like a toolbar. You can also choose to build a custom
ribbon. I have a sample ribbon class that makes chaning pictures and text on
ribbons very easy. You can find my sample here:

http://www.members.shaw.ca/AlbertKallal/Ribbon/ribbon.htm

I would also like to be able to display continuously the immediate value
of current or voltage as well as elapsed time, which I also accomplished
previously by sending formatted strings at a rate of about 4/second.

In VB, this was often accomplished by using a activeX timer object. In
access, you can set a form's "timer" event to fire 4 times a section (enter
250 in the Timer Interval).

Eventually, the connected instrument will be redesigned with a USB
interface. It could very easily be configured as a virtual serial port,
and the Access application would not need to be modified. But it might be
more efficient to use direct send and receive of data on the USB port,
which would involve a different, and perhaps simpler, software interface.

Well, likey you want to adopt one libary that is serial code, and then
"hope" that the serial->usb will work for you. Or, you going to be hunting
for a USB libary of code, not those above serial libareys you found...

I think if you need a *REAL* fancy graphical control type panel display,
then I not convinced that access would be the best tool to develop such an
interface. Certainly sending and receiving data, and making a nice interface
with options is well within the access development scope.

However, at the end of the day, access is a data centric product, and is
designed around being good at building screens to edit data, not building a
cool WinAmp or fancy radio control panel type of interface. If your looking
for a real rich graphical "control panel" type of display to build, then
access is somewhat weak in that regards. On the other hand, you can go wild
and purchase some activeX controls that have cool radio sliders and dials.

So, I think as long as you keep the user interface scope within the limits
of access, you should be ok...

 

[Paul E. Schoen]

I don't see a problem with the above. The only un-known issue (at least
for me) is if the above sammple code librieas work correclty when USB
ports are re-mapped as serial ports. A lot of computers don't have serial
ports anymore, so one of those USB->serial port cables + software likey
will be used here. So, if the usb->serial cables work with the above
software, then I think you much home free here. Also, often manufactors
do include some sample dirvers + code for their devcies.


Well, you can build a form that has buttions beside each other accross
the top that will "look" like a toolbar. You can also choose to build a
custom ribbon. I have a sample ribbon class that makes chaning pictures
and text on ribbons very easy. You can find my sample here:

http://www.members.shaw.ca/AlbertKallal/Ribbon/ribbon.htm


In VB, this was often accomplished by using a activeX timer object. In
access, you can set a form's "timer" event to fire 4 times a section
(enter 250 in the Timer Interval).


Well, likey you want to adopt one libary that is serial code, and then
"hope" that the serial->usb will work for you. Or, you going to be
hunting for a USB libary of code, not those above serial libareys you
found...

I think if you need a *REAL* fancy graphical control type panel display,
then I not convinced that access would be the best tool to develop such
an interface. Certainly sending and receiving data, and making a nice
interface with options is well within the access development scope.

However, at the end of the day, access is a data centric product, and is
designed around being good at building screens to edit data, not building
a cool WinAmp or fancy radio control panel type of interface. If your
looking for a real rich graphical "control panel" type of display to
build, then access is somewhat weak in that regards. On the other hand,
you can go wild and purchase some activeX controls that have cool radio
sliders and dials.

So, I think as long as you keep the user interface scope within the
limits of access, you should be ok...

Thanks. I feel comfortable using the RS232 serial port, and I have built my
own virtual COM port devices using the Microchip USB PICs, so I have a lot
of flexibility on either end. I found a lot of good links about USB on Jan
Axelson's site: http://www.lvr.com/usb.htm

I usually develop applications with Borland Delphi Pro 4 which has some
database functionality but it does not seem to play well with MS Access. I
like Access because of the forms and reports which can be configured for or
by the end user. I'm sure I could use OLE Automation with Access so a
Delphi App could do the USB or COMM interface and then just spit the data
to the proper text boxes or field records in Access. I've written
automation applications that work with PCB and Schematic software, and I've
played with examples for Excel, so it must be available for Access as well.

The end user will mostly be filling out test forms, much of which will be
simply entered by keyboard, but there will be a few pieces of data that
will come from the test sets. I can see many ways to do this, but the
cleanest will be to use Access by itself. The custom ribbons are a new
concept for me, but that might be the way to go.

I appreciate the advice. I'll post again if and when I get something
working, and perhaps provide a link for the application.

Paul

 

[MikeB]

Thanks. I feel comfortable using the RS232 serial port, and I have built
my own virtual COM port devices using the Microchip USB PICs, so I have a
lot of flexibility on either end. I found a lot of good links about USB on
Jan Axelson's site: http://www.lvr.com/usb.htm

I usually develop applications with Borland Delphi Pro 4 which has some
database functionality but it does not seem to play well with MS Access. I

Have you used the ADODB library direct instead of Delphi's built-in
(whatever D4 is using).

The ADODB library is what AC2003 and up uses internally unless you specify
DAO.
I don't do a great deal with Access, but using D7 and ADODB natively, I
haven't experienced any difficulties yet.