Graham Jones (Surrey, British Columbia, IT Pro)
Finding myself unemployed in my early 50’s I now had to decide what I wanted to do at a difficult time in life. My preferred choices were to continue in IT Management or go into IT PM. As it turned out I discovered that I could do neither. All of my years of computer experience and successful PM wasn’t going to get me what I wanted! Lacking formal qualifications/certifications in IT and PM was my undoing. Take particular note! Since I had mouths to feed I joined an engineering company as the Manager of Projects (ie. monitor and guide the execution of all projects). In an earlier article I described different kinds of engineering companies. I had worked for a technology company bidding fixed price contracts. This new company was selling engineering services (hours at $X/hr based upon the service). This represented a big adjustment in thinking for me.
I couldn’t stay away from computers for long (never could) and I soon realized that my best contribution was in automating some of their business processes, not just in engineering or PM but also in construction and sales/marketing which was new to me. I never could resist getting involved if I saw something that could be improved, especially if I could use computers. Mind you, poking your nose in doesn’t always make you the most popular with some people! Most of the opportunities for improvement involved DB applications. A few involved Excel applications using VBA. At this point I was only familiar with Access for DB work and, like so many others, thought that I could make it do everything that was necessary. Now I am sure that way smarter people than I have made Access “sing and dance” but I have come across many situations since where it was clear that it wasn’t the best choice. Access’s main strength is also its main weakness. It is easy to use by the “amateur” application developer. I am using the word “amateur” to distinguish from the “professional”, who has DB design and programming training/experience, and not to put anyone down. In fact if you look at the original goal for Access it is quite remarkable that it has made it this far and in Access 2007 received a lot of attention from Microsoft. Access was first released in November 1992 and was originally intended to be a single user, desktop DB. However, it was “too easy to use”. People demanded more and more, and started to use it in a multi-user environment with larger and larger data sets. It was not well suited to such demands in a multi-user environment because it was a client-server app, ie. the data DB on the server and the client app on the desktop. Back then people didn’t appreciate the implications for such things as network traffic and query execution.
Unlike a DB such as SQL Server (or the now free SQL Server Express version which would make a good Access backend for lower demand situations) Access queries were executed on the client and not the server. The server was simply a central place to store the data. So the execution of a complex query required masses of data transfer across the network (only 10 MBit at that time) resulting in a slowdown for everyone and occasionally crashing the Jet DB engine due to what we eventually discovered were network timing issues. We used to get one of those “really helpful” Microsoft error messages but nobody could tell us what it meant, not even the “techies” on the Access listservers. I eventually tracked down the designer of the Jet engine via email and asked him for an explanation. To my surprise he did actually reply with the following, “very interesting; I have never seen that error before; Let me know if you find out what caused it”. We finally found out by doing network load tests and I did let him know.
Some of the queries used for report generation might run for several hours, which left you wondering if it is was still working or was hung somewhere. The obvious answer was to run these overnight. Unfortunately that wasn’t always possible. There were other problems with the early versions of Access. For example, there was a 64K character limit allowed for the SQL statement describing a query. Believe it or not it, with complex nested queries, it wasn’t that difficult to hit that limit, resulting in the need to create temporary tables on the server to pass data from one query to the next. Also, there wasn’t record level locking. Data was handled in 2K blocks which meant that you might lock out several people for an extended period if someone was slow in doing an update. People resorted to deliberately making records just over 1K in size to avoid the problem thereby artificially inflating the size of the DB file and further exacerbating the network data transfer woes. Access has come a very long way since then and its popularity and success has clearly forced Microsoft to keep it firmly in the picture.
My successes with Access made me the “go to guy”, which was very nice in some ways but sometimes I spent more time helping others than doing my “real” job. Come to think of it, that has been a trend throughout my entire career, sometimes to my personal detriment. However, I have absolutely no regrets because I have derived immense pleasure, satisfaction and learning from it. Ultimately I spent a large part of my time writing a series of Access apps which only served to create a demand for more. I started out with limited VBA and DB app design knowledge but learned a lot more and gained a real appreciation for the complexities involved. The excellent Access books by Litwin, Getz and Gilbert and VBA books by Getz and Gilbert were nightly reading, and the Access listservers were a great place to post questions. Ken Getz is a current Microsoft MVP.
I kept asking myself, “If I am enjoying this so much and my Access and Excel app dev work seems to be appreciated, what am I doing here?”. In part 14 you will see what I decided to do about it.