Title: Members' Experiences

I suspect that there aren't many people in the ACCU in my position; that is, members who do less than a week's C, C++ or Java programming a year. When I joined, I was still at University, and I then hoped to go into a job using my C++ skills (I had an excellent teacher in Chris Simons, who may or may not still be a member of ACCU. It was he who suggested that I join.)

However, life has a way of taking over when you are making other plans, and I ended up in Data Analysis, before moving to my current position. When I first joined my current company some four years ago, my first project was in C, though it was only module testing. "Bah!" I thought, but looked forward to getting my hands dirty in development. But that path closed, and I went on to work in assembler and thence to Visual BASIC(!), where I have remained pretty much without a break. I have done a small amount of C work, but no C++ and have all but forgotten the C++ I learnt. The company I work for is committed to MS-based projects, mostly database work, for which VB and ACCESS are (supposedly) well suited as interfaces to SQL Server, the preferred choice for Enterprise systems. (Though as I write we have just tendered for a major Level A aerospace project to be written in C, which would be nice.)

The project referred to in the title of this article consisted of some minor updates to an existing Customer Query tracking system, and upsizing it from an ACCESS version available to about 20 people at one site on a LAN, to a worldwide system linking to a number of replicated SQL Server data-bases using VPN (Virtual Private Networks). Quite a jump.

The first thing to look at was the system and to see what, if anything, could be salvaged. The engineer who originally wrote it had departed for foreign climes, so with the help of the documentation (Requirements Specification, High Level Design and Acceptance Test) as well as quite knowledgeable clients, I was able to deduce what was going on.

I was notionally project leader but this was relatively meaningless, since there was only one other engineer involved, and in practice all this meant was that I would be mainly involved in client liaison. The other engineer had worked on a couple of SQL Server projects, so on his advice we began by using the ACCESS upsizing wizard to recreate the SQL Server tables based on the existing ACCESS tables.

If I had the time, I could write a book, or at least, a pamphlet, about why this was a thoroughly bad idea. Anyone who has ever opened a Word document, typed "Dear Mr ..." only to be interrupted by an impertinent paper clip, will know that Microsoft's attempts at nannying their users are often as dangerous and irritating as they are useful. In the end (some weeks in) we redesigned the tables from scratch.

The old system used the JET database engine which links the Visual Basic for Applications (VBA) code to the database and which is native to ACCESS/VB, but we wanted to use the speed and efficiency of server-side processing, so we went through the code, translating all embedded JET SQL into its pass-through equivalent, and all the stored JET queries into their pass-through equivalents. Along the way, we had the opportunity to visit some truly horrible code (one 450 line function with no comments haunts me still, since I had to rewrite and factor it myself) and to find out how little the previous engineer had understood about SQL.

However, it was the interface between ACCESS and SQL Server that caused the most trouble. There are a number of "features" that are not exactly flagged up, which can trip the unwary. For instance, the SQL Server "View" (equivalent to a stored SELECT query) can be linked to the ACCESS database and appears in the ACCESS database table view as just another table. But unless you are either lucky or very clued up, it is read-only. What typically happens is that you (the naïve user) base your form on a View, and add the code to edit the data, only to find a tiny message in the status bar saying "Recordset is not updatable" (and MS can't even spell updateable!). You have no idea why not. Hours and hours later, after trawling through TechNET and MSDN, you discover that the Primary Key to a View is lost when you lose the connection to the back-end database, and ACCESS is too stupid to remember it. So, if you physically link to the View and then run the form, the data may be updateable (for that session only, lulling you into a false sense of security), but as soon as you quit the application, the primary key is lost. You have to put a line in your start-up code to recreate the primary key on any Views you might want to update. And then, lo and behold, there is another problem. Some Views are still read-only. A frantic posting to comp.databases.ms-sqlserver results in some kind soul telling you that if your View includes multiple tables in the FROM clause, the View is not updateable, because the original table from which the updated field comes cannot be resolved. Poppycock. I could understand this if fields (columns) were ambiguously named (I was careful to alias any of these) but that was not so in this case. An identical JET query resolves the tables perfectly well. And there was I, thinking that SQL Server was a full-featured system (actually, I am informed that this is fixed in SQL Server 2k).

Another unhappy misalignment was that the upsizing wizard translated the existing ACCESS "Yes/No" types into SQL Server bit types and when we redesigned the tables we saw no reason at the time to change this. My understanding (I became too frustrated by the mechanics of using the product to look that closely at the implementation detail) is that a record which contains a number of True/False fields will set different bits of the same byte for each field. That is fair enough, as SQL Server doesn't have a Boolean type built in. But ACCESS stores False as 0 and True as -1, whereas SQL Server (not unnaturally) stores them as 0 and 1. So ACCESS doesn't know what to do with SQL Server's 1 and, time and again, fields on forms were empty for this reason. But then we discovered that you cannot index a bit field in SQL Server, so we decided to change them to Tinyint (1 byte). These, however, are unsigned and could not deal with ACCESS's True = -1, so we changed them again into Smallint (2 bytes), explicitly set up global consts as True = -1, False = 0 in our code, and read and wrote these from and to the database. In effect, we had to roll our own Boolean data type, but it was worth it to get rid of the jangling headache.

However, the feature that caused us the greatest loss of time and hair was the date truncation problem. We had a requirement to store the date that a Customer Query was received (i.e. created in the database). Because our client is in avionics and some of the queries involved aircraft sitting idle on the ground (something their clients are generally rather prickly about), we also needed to store the time that a query was received. We opted for SQL Server's SmallDateTime field (a 4 byte field, accurate up to 1 minute, rollover in June 2079.) But when you display a record with this field on a form, ACCESS truncates it (transparently, because the data appear identical in both ACCESS' and SQL Server's table views.) When you attempt to close the form, you get an error message saying that another user appears to have changed the data, and your changes cannot be saved (even if you haven't made any changes.) What happens is that ACCESS compares its locally held record (where it has truncated the date field) with the original - "oops", says ACCESS, "it appears that the date fields don't match" (having forgotten that it truncated them in the first place) - "so there is a record update conflict - better let the other person win". That other person is never you. There is a workaround - there usually is, but Microsoft doesn't exactly broadcast it - which is to add a SQL Server Timestamp field to any table with a date component. ACCESS then ignores every other field except the Timestamp (which, if you are lucky, will be the same for your displayed record as the one in the database) and will therefore allow you to change the record.

It is fashionable to denigrate Microsoft for their shonky, bug-ridden products, and my experience would confirm this. But the problems that we encountered were in their front-line Enterprise-level database engine and one of the primary tools for building a front end to that database. And we weren't exactly pushing the envelope, either. Why aren't businesses up in arms about this pathetic level of quality? A perfectly straightforward MIS with minimal logic became an exercise in kludge and fudge. In the end, (pace Francis) we have delivered a product of which I am not proud, that just about does the job but is pretty flaky. The real trouble is that Microsoft feels the need to "enhance" their products all the time, so that instead of creating and honing their tools until they are just about as perfect as can be, they tinker and mess around so that nothing interfaces with anything else, there are constant requirements for patches (two patches we had to download for the JET engine were dated only a few days apart!) and products that should be compatible rarely are. I could go on about what I dislike about ACCESS and VB in general (that there are so many ways of doing the same thing, but practically no built-in advice about the best way to do it in the given circumstances; that VB is like the Apple Mac - far too much going on under an hermetically sealed hood) but that would be another article and probably for another journal.