Supporting serial communication devices in Microsoft Access without the use of ActiveX controls.

Jim Dettman (EE MVE)Volunteer
CERTIFIED EXPERT
Independent consultant specializing in the writing of custom packages for businesses.
Published:
Updated:
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.

Level: Intermediate


The problem:


  As time marches on, every once in a while you stumble over an application that simply refuses to die because it does its job so well.


  This happened to me recently when a question was posted on Experts-Exchange asking about references in an Access app and how to bring the app forward to a later version. IT was looking to upgrade Office but could not because of this app. The asker was stuck because of the way it had been written and with the original developer long gone, the path forward was not clear.


  In this particular case, it was an app that had been used as part of a process to talk to a weighbridge (a scale) via an RS232 serial connection.


  After a lot of back and forth, we realized there was a little more to it; a VB 6.0 program was actually doing the RS232 part and talking to the scale, then controlling Access via OLE automation to interact with a form in order to display the weight from the scale.The Access portion of the app was only being used to log and track the weights.


  The user would start with the Access app, and at the point of needing a weight, the VB6 .EXE would get fired off by Access, which would then grab a reference to Access, talk to the scale, post the weight directly on a form, and then quit.


  After looking at the problem a few different ways, it seemed like the best path forward was to remove the VB6 app entirely as it was just complicating things. Access can talk to RS232 devices, but this has always been achieved by using the MSCOMM32 Active-X control on a form (see https://msdn.microsoft.com/en-us/library/aa231237(v=vs.60).aspx for info on this control). Fortunately, this is one of the Active-X controls that does work well in Access (most don’t), but there’s a rub; that control was distributed with VB 6.0 and is only licensed for use with that product.


  In the past when you needed to do RS232 in Access or Excel, developers would (ahem) “borrow” the MSCOMM32 control from VB 6.0. I have never heard of an issue related to this in regards to Microsoft, and it was fairly common knowledge that this was done routinely. Strictly speaking though, it violates the licensing agreement (and this is probably why the application was written the way it was). So the goal was to find a way to do it all in Access without using the Active-X control.



The solution:


  But were there any other ways to do it? Obviously, the MSCOMM32 control itself managed to do RS232 in some fashion, so there must be a way. After some digging, it turns out you can do serial communications through the Windows API, which only makes sense when you stop to think about it. I don’t think many in the Access world are aware of this though as all the RS232 solutions I’ve seen have relied on the use of MSCOMM32.

  Under Windows, doing serial I/O is no different really than working with a file. The operations are all basically the same. Out on the net, I found a few examples of VB 6.0 code where this was demoed as a proof of concept type thing giving a general outline of how it was done. There was not a lot of documentation on the code though, leaving some questions unanswered.


  It took some time to dig for documentation on the overall technique as I found very little out there. But in the end, I discovered one MSKB (Microsoft Knowledge Base) article (https://msdn.microsoft.com/en-us/library/ff802693.aspx) that covered serial communications under Windows and helped tremendously in understanding what I was seeing in the code I had found.



Tip: A sad thing that has occurred in recent years is that articles like the one above have continued to disappear out of the MSKB. Many of these contain general and fundamental knowledge on the way Access, other Office products, and Windows works. But because they are often tagged as version-specific articles, they disappear when the product goes out of support. The above article was published in Dec of 1995 as part of the VB 6.0 doc set, and yet it is still relevant and useful to this very day.


  Microsoft to their credit has acknowledged that this is a real problem and in the Access area, thanks to the efforts of some very dedicated people, they are trying to save as much of this content as possible from disappearing.  But the tip here is; don’t count on it being there tomorrow!  If you see an article that helps you and it is more than a few years old, grab a copy of it!



  It took an hour or so to re-work the code, but in the end, I had a small test DB which would replace the original

VB 6.0 app and did not rely on the Active-X control.


  Since VB 6.0 is so close to VBA and code is a drop-in in most cases, and that the MSCOMM32 control does work in Access, I also created a small test DB using it as well. This was mainly to serve as a double check that we were talking to the scale OK with Access alone, but it also served as a fallback solution in case we could not get the API version working right off. It wasn’t easy arranging the time to test as the scale was in production use, and the asker was 100km away from the site where the scale was, so it was important to have something that could fill the gap if needed.



Trying it out:


  We finally managed to arrange some time to test things and it didn’t take long to get the Active-X version working (which is what we expected). The Win API version though took a few hours to get working. That was mainly due to two things:


  1.   We actually didn’t know the baud rate, parity, speed, or the number of stop bits of the scale. While the original VB 6.0 source code had these values, we were not 100% sure the .EXE in use had been compiled from that source. Ultimately we determined that indeed the parameters were off. What confused us for a while is that the Active-X version appeared to work even with the wrong parameters, and I’m still scratching my head over that one. But with the Win API version, it didn’t work at all with the wrong parameters in place. After playing around a bit, we were able to determine the correct ones.

  2.   Timing related to analyzing the data coming in. I had set a form timer for five milliseconds to check for data, and this proved to be too fast. The scale was sending weigh data in blocks of fifteen characters and five milliseconds was not long enough to transmit the full block (which I should have known based on the baud rate). After slowing down the timer, we ended up with multiple blocks and always had at least one full block of data to work with.



Sidebar: I should also note that part of the time it took to get everything working was due to the fact that while I was in the United States, the PC and scale were in Australia! I was using an older version of Team Viewer for remote control, and at times it was a bit slow, but overall it worked far better than I expected.


  This was my first time doing remote work outside the US, and I was more than pleasantly surprised at how well it actually worked. I even used the VOIP (Voice Over IP) feature built into Team Viewer so the asker and I could chat while we debugged. Given that we were a half a world apart, it worked exceptionally well. Moral of the story; don’t be afraid to take on overseas projects thinking communications will be a problem. You may be very surprised (as I was) on how well you can do.




The result:


  In the end, we ended up using the API version, so now only Access is involved in the process, which greatly simplified things. Originally in Access 2000, this app can now be brought forward into any of the more recent versions easily without issue.


  Both test DB’s we used are attached in a zip file along with this article so you can see what was done. I tried to keep the code similar as possible in each so it is easy to compare the two methods. While it still could use some clean-up, it is workable in its current form and provides enough of an outline so that you can do RS232 in your own apps. I used a single form along with a single VBA code module that can be dropped into any application that needs RS232 communications capability. Note that I have not included the Active-X control in the zip file, so the Active-X version is non-working unless you have VB 6.0 installed on your PC (you may need to adjust the references in VBA).


  I would also be remiss in not acknowledging and giving thanks to David M. Hitchner, whose VB6 code I found out on the net and used as the basis for this solution. With that code, I actually had to do very little in putting this together.


Enjoy!

Jim Dettman


WeighScaleRS232.zip

abcdefghijklmnopqrstuvwxyz

3
3,079 Views
Jim Dettman (EE MVE)Volunteer
CERTIFIED EXPERT
Independent consultant specializing in the writing of custom packages for businesses.

Comments (2)

Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010

Commented:
good article, Jim.
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Author

Commented:
Thanks Dale.

Jim.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.