You are here

The Versatility of Microsoft Access

Random Drawing Control Panel

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.