Site Navigation
As I went back on the notes that I have on this project ... It struck me that it was an interesting slice of computing history. Taking in SCO, Microsoft, Linux etc, etc.
It all started in early 1998, when I was asked for some advice about an aging Job Ticketing and invoicing system running on a SCO Xenix box.
The owners of the business were in the process of upgrading their office computing to use Windows 98 clients and a Novell LAN. They wanted to know what to do with the SCO system. And as I had a reputation for being a Unix "expert", they asked me to make a recommendation about upgrading the system.
In the initial investigation, I discovered that the system used an RDBMS called UNIFY. I had never heard of UNIFY, or the Integrated Development System called ACCELL (which had been used to develop it). However, after a little time examining the system, I managed to find some (primitive) SQL extensions and was able to work out how to get data from the system with SQL queries.
I tried contacting the Sydney based Australian representative of the company but I did not get much help. I think they had decided to hunker down and just try to hang on to their existing customer base.
I quickly came to the conclusion that the best course of action would be replacement of the entire system.
I could imagine the following possibilities:
- An xBASE system. I was already familiar with these and had written several applications using Nantucket's Clipper. At the time, the product had just been purchased by Computer Associates. There were also equivalent systems like FoxPro, Visual dBASE 7 (at the time owned by Borland) and CodeBase (actually a library of xBASE routines that could be called by C++), etc, etc.
- Borland's Delphi. Similar to the xBASE products. And it seemed to be very popular at the time.
- Microsoft Access. The new kid on the DBMS block, and growing rapidly. I had done some preliminary testing of the product and had been quite impressed with some of the features. Especially the forms building, which was fast and powerful.
After evaluating these different choices, I decided that MS Access was the best option for the customer, because of the office environment (all Windows Clients using MS Office). This would fit in most easily with the existing applications.
There were a few things that worried me about MS Access. Like many Microsoft products it had a billion dollar presentation layer and programming layer that was very ordinary. The forms builder and the report writer were state of the art and as slick and easy to use as any I have encountered. The programming support for the backend was crap. But I surmised that if an organisation was willing to model itself on the fiction "Northwind" company, then it would be fairly easy to implement something.
After reading the MS help files (taking note of the part about separating database layers and application layers), I set up a pilot model of the system and stress tested it.
In order to get the data out of the SCO Xenix box, I installed Slackware Linux on a machine with a spare drive and connected it to the SCO Xenix server by a serial connection. I then ran a series of SQL queries to pull the data off the Xenix box to the Linux machine.
The UNIFY server was a 286 running the old 286 version of SCO Xenix -- not at all suited to data crunching. The Slackware Linux machine was a brand new Pentium, with the full suite of GNU utilities (including the mighty gawk) and some serious processing power under the hood. It sucked up the data and transformed it into tab-delimited text files for the MS access import. And it did it quicker than any compiled program could have done on a Microsoft machine.
Working mainly at night, and on the weekends, I got the pilot up to the stage of testing. Then developed a cutover plan. And we did preliminary migration and cutover on a single weekend.
There were several things I did not know about at the time:
- I still had not tried perl. Nor had I tried CGI/HTML/Apache.
- I did not know about postgres (which was in its' infancy at the time)
- There were a few little niggles I had about Access. These were
- Almost no support for SQL embedded in VB, apart from the RunSQL method, which required the kludge of building a string in VB.
- No embedded host variables.
- I had trouble getting the results of any query into a VB program. The VB equivalent of cursors (loop) were dense, obtuse, difficult to understand, poorly documented and cumbersome to use.
- There was very little documentation for the MS catalog tables.
- MS access did some very weird things with dates (MS smart dates).
- All of those little niggles turned out to be fairly big problems.
- Like so many small businesses, this particular firm was using Windows 95, which I had not worked with. Up until then, I had worked with (the insular sanitised corporate landscape of) Windows NT. As I embarked on this project with a small business, I was to discover that Windows 95 was one of the world's truly awful operating systems. I would dearly like to see Mr Gates disembowelled and strangled in his own entrails as punishment for the sin of inflicting such an abomination upon us.
- I believed the MS propaganda. At the time, I thought they would stand by their customers. I also thought that, in their own interests, they would support their own products and offer reasonable and cost effective migration paths.
Of course I am now older and wiser. Still I enjoyed working with the people in this particular small business, and I only wish I had many other customers like them. I now have to find a way out of the apparent dead-end that a working Access 97 database presents to someone contemplating upgrading their equipment.
It is still difficult to find documentation online that discusses how to use (the equivalent of) MS cursors or MS Access catalog (or data dictionary). Fortunately there seems to be many more programming examples around, then when I first started. I have placed one of my own in the PGTS Feedback column. The following links also give some information about MS Access:
- New Life for Old Microsoft Access Data. Marcel Gagné describes how he kicked the Microsoft habit, without purchasing any additional Microsoft licenses. A very good article which contains a link to ...
- The MDB Tools project Home Page. This Open Source project is a work in progress. Currently gives read-only access to various flavours of MS Access. It can be used for converting MS Access databases to Open Source databases such as MySQL or postgres.
- System Tables. Trudy Pelzer presents us with a summary of the tables that describe a database. These can be referred to as "System Tables", "The Catalog" or "Data Dictionary". There is not much information on the MS Access catalog. However, it makes a good intro for anyone not very familiar with the topic.
- Win32::ODBC - A Tutorial. It is possible to get Access data with perl. This is a good intro to the Win32::ODBC package.