Keeping Clarity Alive / Adventures with Early Microsoft Access
 |
| Original Clarity Software |
Here are some of the more memorable technical challenges that we faced:
Backend Database
Very early on in the development of Clarity we started using the multi-user “split database” approach with MS Access: data in a MS Access file on a shared drive, front-end forms and code in separate MS Access “front-end” files containing links to data on the shared drive. But we had a major challenge: in these early versions of MS Access (1.0, 2.0) linked tables to Access files were highly sensitive to network “hiccups”. If the network went down, even momentarily, data files would get corrupted and need to be repaired (I still have nightmares about the resulting error #3049s that customers would call us about!). In rare instances, the files would be corrupted beyond repair in which case we would need to manually reconstruct data files to the best of our ability.
The way to solve this would have been to switch out the back end to be a more heavy-duty transactional database, like SQL Server; however, unlike today where these databases are available at a nominal cost, switching to SQL Server would have added several thousands of dollars to our customers’ costs, and would have been prohibitive.
Therefore, we came up with techniques to work around this issue. Instead of using bound forms (fields on forms that have a “live” connection with the database), we started using unbound forms. As a result, a power-outage with a customer or inventory record displayed on the screen would no longer corrupt the data. Using code (Access Basic, later renamed Visual Basic for Applications or “VBA”) only when a user clicked the SAVE button did, we take data on the unbound form and save to the database.
We also incorporated a technique where each implementation had both Primary and Secondary data files, that we kept in sync with each other via code. If the primary data file became corrupted, we were able to resolve this by simply copying the secondary database into the primary.
Barcode Tag Printing and Scanning
The ability to print tags, with barcodes and scan these tags for point of sale and inventory management, were fundamental for our clients; however, these were the days before widely available specialized printer drivers and APIs. As a result, we had to write code that communicated with the tag printers directly. Many of our jewelry clients were heavily invested in expensive tag printers that they did not want to replace. Therefore, we had to find ways to support each of these individual tag printers. For example, many of our clients had printers made by a company called Zebra, specializing in printing specific small dumbbell-shaped tags frequently used in jewelry displays. Zebra printers use their own proprietary language “ZPL”. So, we needed to have Clarity (1) have a configuration area where user could select tag sizes and layout options (2) having Clarity’s code configure a ZPL statement to build the proper command to print tags using these options and (3) send that command to the appropriate COM or Parallel port. This approach was applied to several prominent brands of tag printers in the jewelry industry, each with their own requirements, often utilizing their own proprietary languages.
Of course, if your software prints barcoded tags, it also needs to be able to read these tags via scanners. One challenge was that we did not want to require our users to click in a specific field before scanning the barcode. We ended up taking an approach like this: (1) pre-programming the “prefix” character F12 into the physical scanner’s configuration (2) having a tiny hard-to-see field on each of our MS Access forms whose hot-key trigger was F12 and (3) having a function called from the on-exit property of that field that handled the scanner input collection and buffering and (4) calling the function to do whatever necessary with the scanned input.
Upgrading MS Access
When our business took off, Clarity was utilizing Microsoft Access version 2.0, which was still a very early 16-bit Windows application environment. In the late 90s, we attempted to convert Clarity to be based on Access for Windows 95, a 32-bit environment using Microsoft’s conversion tool. Unfortunately, this conversion did not work and ended with an error – most likely as a result of our utilizing MS Access 2.0 in ways more sophisticated than the conversion program was able to handle.
We continued to keep Clarity alive using version 2.0, but it was getting more and more difficult to work around limitations and sustain our business. As Windows continued to grow and improve through the years, and modern applications followed suit, we were still telling our clients things like needing to name files with 8 characters or less. The situation was becoming desperate.
In 2015, we took another look at converting, this time to Access 2013. Unfortunately, at this point there was no conversion tool available to go from Access 2.0, now a twenty-year-old product, to the current MS Access.
In order to save our business, I took it upon myself to figure out a way to make this conversion. I started by taking a very close look at MS Access 2.0, looking for some way to convert all the objects (forms, queries, reports, linked tables) to Access 2013. I noticed that if you right-clicked on an object in the database window, there was a ‘print definition’ feature. When using this feature, you could generate a report that listed all the objects’ properties; however, the report was formatted like an MS Access report and was displayed in print or print preview view (like a pdf), with each object’s report averaging dozens or even hundreds of pages.
It was while staring at this report on my monitor that I noticed a small, easy to miss option to save this report to Excel. Eureka! With object definitions and properties saved to Excel, we could create a “roadmap” of sorts to rebuild the objects.
Which is what I ultimately ended up doing: for each Clarity object still in Access 2.0, I created a print-definition report, and saved that report to Excel. Then, using Access 2013, I imported each of these object definition specifications. Next, using the VBA development environment in Access 2013, I wrote code to recreate new objects by walking through these object specifications. Given some inconsistencies between what was supported in Access 2.0 and Access 2013, there were gaps that I needed to handle and rebuild manually on a case-by-case basis; however, for approximately 90% of the objects, code and functionality, this approach WORKED. As my conversion program ran, I could see the old Clarity Access 2.0 objects rebuild themselves and come back to life – what a thrill! Using this approach, we were able to successfully port Clarity to Access 2013, and brought our clients into the 21st century.Please read the next chapter of my blog: Job Search 2015
Comments
Post a Comment