What's So Good?

Microsoft Access
It's been around since the early 90's. My, how it's grown!

As a part of Microsoft Office, it is admittedly the strange sibling. With Word or Excel, you can jump right in and start typing. Not so fast with Access. The latest version, Access 2007 will change some of that. In some ways, that's good. In other ways, it seems to be dumbed down and doesn't take into account all the development that has been done in previous versions. We shall see.

I have used Access versions 1 and 2 back before Windows 95 was released. I missed out on Access 95, which I hear was quite bad. I did some work with Access 97 then moved to Access 2000 about 5 years ago. Almost immediately I jumped to Access 2003. There have been trade-offs. As you go to later and later versions, you get a prettier face, but possibly less reliability.

Access 2003 is quite temperamental in the development environment, but -- in my experience -- works splendidly in a production environment. Over time I am mapping out the mine field and know to expect development crashes ("Microsoft is sorry but you lost your work.") I've also written auxiliary software to help me repair a project that is starting to get infected, and to backup each precious new increment of development. This gives me a safety net so I can forge ahead, knowing that there are traps out there and to play it safe.

Access lets you design tables to hold data, forms to let users enter/observe data on their screen, reports, and Visual Basic for Applications (VBA*) modules to hold very powerful program code to tie everything together. With a few hours of training you can write simple applications. If you use the built-in wizards, you will have some of the worst-written VBA code possible. Wizard-generated forms will have time bombs built in. But, if you are lucky enough to have a first-rate user group and selection of Access books, you will learn the best practices. These practices are quite elegant, so they are more of a pleasure to use than a pain.

In the spring of 2004, I gave a 2-hour talk at the PAUG (Portland Access User Group) Developer's Conference entitled: "Working Outside The Box: Extending Access". I talked about all the wonderful ways of adding power to an already-powerful system.

The following year I gave another similar talk that included the basics of the first talk but included some new example applications and discussed "lessons learned".

Last year I gave a talk about the dark side of Access: with its wrong-headed defaults. It included how to right the wrongs.

The conclusion of these talks are this: Access by itself is good to great. Screen design tools are fair -- could be much better. Report design tools are excellent. Table design is OK but could be better. Visual Basic modules are excellent. Default settings and wizard designs are pretty bad but predictable. My talks cataloged what you need to change. Extending Access? How? You can, using the power of VBA, Win32API, and external libraries, extend Access in a very powerful way. You can run other programs from Access, including sending data via remote control to Word or Excel. You can show a live web page from within one of your screens. You email reports with no intervention. You can extend the excellent command set of VBA by loading the File System Object.

Because Access is a heaven and hell product, you can learn to deal with the hellish parts and build elegant relational database solutions. The solution can start out as a single-user database and be unscaled to a robust multi-user system (that can use M.S. Jet, M.S. SQL Server, MySQL, or Oracle backends). Flexible!

Knowing where the mines are in the minefield gives me a competitive advantage over someone who doesn't have the time or the network of developers to move up the learning curve.

All things considered, I can't complain.


*VBA = Visual Basic for Applications, which is nearly identical to the Visual Studio component Visual Basic 6, introduced 9 years ago. By the turn of the decade, century, millennium, it was the most popular programming language in the known universe. It reads like English and is, I think, quite adequate to solve many problems.

SQL - SQL is a database language that Access uses. Other database products use variations of this language. I find it quite powerful in letting you select, sort, group, total, and modify data.

PAUG = Portland Access User Group. http://www.paug.com