David B. Hill's blog

The Versatility of Microsoft Access

David B. Hill — Fri, 13 May 2016 14:08:09 +0000

It's a fact; Microsoft Access is often underrated as a serious software development tool.

Because Access is so easy to use, and because it comes bundled with other popular Microsoft Office applications like Word and Excel, there are quite a few Access databases out there written by non-I.T. people who lack some of the basic training and experience that software developers should have. These databases generally do the job they were originally intended to do, but when they become popular in the office and the original designer leaves the organization, or someone tries to extend the capability of the original design, they can create as many problems as they solve.

For an experienced software developer, however, Access can be a joy to work with. Designing tables and relationships is fast and easy, the visual query tool is simple to use but capable of addressing complex problems, creating user-friendly forms to use those tables and queries is fast and easy, and with Visual Basic for Applications (VBA) you can design applications that will handle almost anything you can think of.

Here's an example of what you can do with Access in concert with other Office applications. I worked for a non-profit that created and administered incentive programs designed to encourage people to use alternate commute methods. They wanted to try a new program aimed specifically at bicycle commuters. The program was approved on a trial basis, so they didn't want to put a lot of money into it at first. The program was structured differently from any of the other programs we administered, so we could not use the existing web application; some development was necessary.

Using Access along with Outlook and Excel, I was able to design, develop, and test a complete system in just a few weeks. The Communications Department (shout out to Brian Carr!) had a website built where users could register to participate in the program. Periodically those registrations were sent as formatted emails to an address monitored by Outlook. Using Access we read the emails, sorted them into three categories, and moved the emails to new Outlook folders based on the category. Access then imported the email registrations and either created new records or updated existing records in the main SQL database and generated records specific to the program in the Access database.

Once the records were generated, the Access database sent emails to the participants and monitored their commute logging history in the main SQL database. On a weekly basis we would use an Access application to conduct random drawings for each region within the geographic area the program was focused on and award prizes to the randomly selected participants. Access also calculated scores for individuals and teams based on their logging history, as well as other items that the program staff entered manually in the database such as Facebook photo contest winners, attendees of cycling classes, and so on. The scores were used to determine overall individual and team winners of the four week program.

For reporting, we used Access to extract information, export it to an Excel spreadsheet, and format the spreadsheet for better readability.

If we had chosen to do the initial development by going through the software vendor who designed and supported our main web application, it would have taken months to implement the program instead of the three weeks it took with Access.

That is just one example of the versatility of Microsoft Access.

IBM i (AS/400 and iSeries) How I Miss Thee!

David B. Hill — Fri, 13 Feb 2015 19:33:38 +0000

My entry to the world of Information Technology was via IBM Mid-Range Systems, starting with the IBM System 34 (S/34). A relative asked me to help him "computerize" his import/export business. A friend of his had recommended the S/34, so that's what I ended up working with. I picked up RPG pretty quickly, and when that project was done I landed a programming job with the software vendor we have chosen.

That job exposed me to the System 36 (S/36), which was essentially a hardware update of the S/34. The next job also gave me exposure to the System 38 (S/38), which was a completely different beast. I didn't really get to know the S/38 very well, since the project I worked on was a proprietary menu system that worked across all three midrange platforms. That meant even though I was technically programming on the S/38 in RPGIII, as much as possible the code had to be compatible with the S/34, meaning it was really "dumbed down" code.

It was during my first business venture as an IBM Business Partner (in the MAP, or Marketing Assistance Program) that I got my first real exposure the S/38. Rumors were abounding about IBM's Silverlake Project, the new midrange system that was to somehow merge the S/36 and S/38. IBM started dropping strong hints that my partner and should get S/38 training, which we did. When the Advanced System 400 (AS/400) was announced the reasoning behind their suggestions was immediately clear; the AS/400 was what the S/38 was intended to be all along, and the merging of the S/36 and S/38 was accomplished by providing an environment on the AS/400 that would run S/36 code.

Being IBM Business Partners, we were among the first companies in Atlanta to take delivery of an AS/400, a lowly "B" model about the size of a two drawer file cabinet. It was slow, as is typical when new operating systems first come out; OS/400 was an extremely powerful OS, and the hardware had some catching up to do.

This was still when manuals were all printed, and we must have had twenty or thirty 3 and 4 inch binders for the AS/400. The binders actually occupied more space then the machine itself! But what fun I had. I spent nights reading manuals and days trying what I had learned. One of the more fun things was the newest implementation of RPG. While not a Structured Programming Language (in the parlance of the day), the new operating codes enabled it convincingly imitate a Structured Programming Language. I banned the "Goto" op code from our shop, which led one employee to accuse me of forcing him to write Structured COBOL disguised as RPG.

I lived, breathed, and yes, even dreamed in RPG (I would have dreams where I was trying to "debug" my life, which was in the form of a huge RPG program). And IBM kept extending and improving OS/400; it seemed there was no end to what it would eventually be able to do. The Integrated Language Environment (ILE) let you write programs in any AS/400 supported language that would integrate seamlessly into one application. Multiple operating systems could run on the AS/400 and use a common database (if you know the difference between ASCII and EBCDIC you know what a feat that was).

Sadly for me, I was between contracts when the 9/11 terror attacks occurred, and the ensuing economic downturn combined with family health issues took me out of the AS/400 world for a few years. By the time I was in a position to pursue AS/400 jobs again, I had no "current" experience, and with every job posting drawing hundreds if not thousands of applicants, I was not able to find AS/400 RPG work. I moved on to other worlds, which means my recent experience is primarily Microsoft Access, VBA, and SQL work.

In Depth: Technical Expertise - More than meets the eye

David B. Hill — Fri, 23 Aug 2013 18:03:25 +0000

As a professional, I am reluctant to list something in the Technical Expertise section of my resume unless I am completely confident that I can demonstrate that expertise, or at least demonstrate prior experience. However, even if I don't claim expertise, I have experience with many more specific technology tools and environments than those listed.

This website is a perfect example. I do not claim expertise with Drupal implementation, but I have in fact dabbled with Drupal enough to put together a reasonably professional looking site. As with every other aspect of my I.T. experience, I am always learning and trying to stretch my abilities beyond what is required.

I have for years hosted websites for friends and non-profit groups I'm associated with. On occasion I have put together websites also, usually pretty simple sites constructed with very straightforward HTML. In the past, simple HTML was a benefit because a lot of people were still using dial-up modems with very little bandwidth, and simple HTML with minimal graphics would load quickly. These days, however, most people have access to high bandwidth Internet connections, and most people are exposed to fairly sophisticated websites, so I decided I needed to improve my website design and development capabilities.

After exploring the various tools for website development, I decided that Content Management Systems would provide the quickest way for me to develop professional looking sites with a minimal learning curve. Looking at the variety of choices, I narrowed it down to three: WordPress, Joomla, and Drupal. Each of those systems provides a framework for sites that can be complex or simple, depending upon the effort you want to expend. After several months of research and testing, I finally concluded that any of the three would serve my purpose. Each had positive and negative aspects, so it really came down to just making a choice.

Drupal seemed to be slightly more "scalable", with a great deal of support in the Open Source world, so I chose Drupal. I read a book or two, browsed Drupal forums, and then put together a couple of sites for friends and family, learning as I went. I don't claim Drupal expertise, but I am comfortable enough with it to present myself to the web through this Drupal based site.

This demonstrates a kind of expertise that is difficult to quantify, the ability to pick up new tools quickly and become productive with them. I have done that for myself, and I have done that for employers. I expect I will always do this.

In Depth: Education - An ongoing activity

David B. Hill — Fri, 23 Aug 2013 17:33:40 +0000

For the sake of brevity, the education sections on resumes tend to be short and sweet, a "bullet list" of institutions or organizations where formal classes and recognition occurred. However, anyone who works in any field of Information Technology understands that education is a continuous process, that technology changes and grows so rapidly that if you are not always studying the latest and greatest advances, you are falling behind.

In my earlier days one way I accomplished that was trade magazines, especially those that focused on the IBM AS/400 and RPG programming. These days, with the Internet, knowledge is only a web search away. Not a day goes by that I am not presented with a new problem or that I have a new idea, and I search the web for solutions for those problems and implementation techniques for those ideas.

In my current position, we recently upgraded from Microsoft Office 2003 to Microsoft Office 2010. At home I subscribed to Office 365 which means I'm using the 2013 version of MS Office. If you have used these products you understand what a leap it is going from Office 2003 to 2010 or 2013. With a quick search, however, it is easy to find essays and tutorials that, for each Office product (Word, Excel, Access, etc) discuss the differences. This is a great way to move quickly past that steep learning curve and become productive quickly with the new versions.