The advice you'll get on design

Let me start by saying there are a lot of very smart experts out there on MS Access. But if there is one piece of advice that gets me going, it's the statement "newbies to Access like to start with what they can see (forms/reports) but this is the last thing you should consider." What I take issue with about statements like this is as follows: if you think like a developer, your application will be made for developers. That means your table structures, code modules, and queries will all be very efficient and (hopefully) architecturally sound. The flip side is, you aren't giving appropriate attention to the end product, and you're more likely to end up with a data-driven design. The end product isn't the tables, functions, and queries that make up your application. It's the menus, forms, reports, and other elements that will comprise the user experience. So what process would I advocate?

1) Determine your requirements. What is it that the end product needs to do to answer your particular problem or need? To help think through the process, you can either spend a lot of time pondering your requirements in your head, or you can whip up a few form/report prototypes and see if you've thought of everything you'll need to meet your needs.

2) Create your design. What is it that your application will need to do in the background to meet your requirements. If you've done a good enough job of thinking through what your requirements are to solve your problem, your design will show that. For example, if you know you'll need to be able to create an extensive number of reports based on dates or other data attributes, your design will be sure to take that into consideration. Similarly, if your requirement is that your application needs to be readily deployed to multiple users without a lot of time copying databases to each person's PC, you're probably going to design your application differently than if you just start creating tables and functions without a thought for the end product.

3) Start creating. Seems simple, but this is where you'll get the opportunity to test your commitment to some of the items you thought of in (1). That is, you may find that actually creating your solution to meet your requirements is harder than it seems, and you'd prefer to sacrifice a few of your requirements to make it easier to develop or maintain.

4) Test. Preferably use someone other than yourself, that may have familiarity with the problem space and desired solution, but hasn't been deeply involved in the development. Why? Because the second you started designing and creating your solution, you begin to think like a developer, not a user. You'll know intimate quirks and their workarounds, something an uninitiated user won't. Thorough testing, especially where your tester purposely tries to "break" things, will ensure you aren't inadvertently signed up to do a great deal of recurring training.

5) Repeat 1-4. The process never ends, does it!

Don’t rely on that Access wizard that creates a form based on your table…

It may be the easiest, neatest little way to get going quickly. Using the “AutoForm” wizard may seem like the best way to create a form, but it is generally going to lead to the worst user experience. Sure, you can take the resulting AutoForm and tweak it to be a little more usable, but it’s what we call “data driven design.” A philosophy that dominated (by default, really) user interface design for decades, and is only now departing the world. Data driven design means taking the data you want to store, and throwing an interface for user input over it. User driven design, which I hinted at in a previous post, is the way to create happy users and ensure people are more productive.

Rather than putting a form over a table, user driven design will help you to look at the world from a user-process perspective. You are more likely to discover you need to "merge" multiple data sources the user can view or update in a much more efficient manner. For example, while creating new orders (one table), perhaps you need to let the user quickly view key customer information (from a different table) on the same screen, and be able to make changes to your contact history with that customer (yet another table) from the same screen. All if this is missed if you just rely on the Form Wizard.

And while we’re on the topic, I’d suggest avoiding the Access Switchboard too. Use something a bit more friendly for users.

Microsoft Releases Free Access 2007 Runtime!

Wow! After all these years, the Access team releases the runtime version of Access for free. For those new to Access, why should you care? Well, most importantly, because the runtime version of Access lets you deploy your database application to other users in your business WITHOUT BUYING THE FULL VERSION OF ACCESS! That can mean huge savings to your organization, or mean your project can continue (where it might otherwise have halted because you couldn't deploy to other users given the license costs).
Here is the link:

http://www.microsoft.com/downloads/details.aspx?FamilyId=D9AE78D9-9DC6-4B38-9FA6-2C745A175AED&displaylang=en


A few notes:

• The runtime version of Access is terrific, but you need to be much more diligent in your error handling (at least that was the case in previous versions, I'm sure it's the same with 2007). If your application encounters an unhandled error, it will terminate.
  
• The database window will never appear to your users, so you have to be creative in how you expose reports, queries, and data to your users.
• The nice toolbars for sort, filter, etc aren't visible to the user. Again, I haven't had time to check out the 2007 version, but that was the case in earlier releases. So it means you have to create form buttons to allow your users to perform the same actions.
  

What Kind of App Are You?

Great humor, and also thought provoking when it comes to deciding what sort of user experience you want to offer:

http://headrush.typepad.com/creating_passionate_users/2007/03/is_your_app_an_.html

Kathy Sierra's blog (I'd suggest looking at all of it!) is a fantastic place to consider your user experience. It's a tragedy that she's ceased posting since she received some vicious threatening messages.

Decision #2: How much do you invest?

When I say “invest,” of course I mean time and energy. The first answer is, as much as you feel like. The second answer is, as much as makes sense. This advice holds true for any business professional, but is particularly useful if you are engaged in a project like creating a useful database application that can save time: be sure the cost/benefit works out in the right direction. If you are making $60K per year as a program manager, and you begin to focus more on creating a database to help your team save time, make sure you’re still giving your company $60K worth of effort. If you can justify the solution you are creating into the $60K, and many times you can, then proceed, my friend. But if you find yourself spending more and more time learning VB or SQL, and less time successfully delivering on Job Requirements A through Z, no one will benefit.

I’d recommend you do the following, really for any professional project you undertake, but especially one that doesn’t fit your “core responsibilities”:

1. Get your manager’s buy-in

2. Realistically evaluate what it may take to get to the end product. As a beginning, this will be hard to do without a solid amount of experience, so figure in between two to six months to be pessimistically generous.

3. Realistically evaluate what benefit you and your organization will get from the end product. If (3) is less than (2), you’d best do the project on your own time.

4. Sell the value upwards. Make sure what you create, including a return on investment figure, is publicized to your manager. Don’t assume your manager will see the value without some help, smart as they may be.

The upshot is, make sure you focus on your core responsibilities, and don’t let a project take on a life of it’s own to the detriment of your career. Do you have experiences related to this to share?

Design…on the fly, or up front?

Whether you’re starting afresh, you’ve already started and have a good functioning solution, or you’re contemplating your next project, don’t sacrifice the wisdom of good design in order to start having fun creating forms and reports. Top 3 reasons why:

1) Good Form. If you’ve ever played piano, golf, or even used a pencil, you know it’s important to practice good form…it reduces future physical or mental pain.

2) Minimize re-work. If you start a project without enough consideration for design, you’re likely to pay for it later in re-work or lack of functionality.

3) Create happy users, avoid extensive training. If your application will be used by anyone other than yourself, the last thing you want to do is spend a great deal of time training each new users on the idiosyncrasies of your solution. From making sure the field names are well labeled, creating intuitive forms with field arrangements that map to how a user thinks, to capturing all the data needed for someone to carry out their duties, doing a bit of design up front can save time, energy, and frustration in the long-term.

So what is the secret to good design? Well, I won’t pretend to be the expert, there are hundreds of books written on the topic. But for me, it is the same as creating a good presentation or letter…think like your audience. In this case, not you as the user, but someone who will wasn’t involved in the thought process for the design, but needs to understand how to use your application nonetheless.

How much time you actually spend designing is very subjective and dependent on how complex your project is going to be. But do put the horse before the cart when it comes to design.

You're Not A Professional Programmer

Don't let the fact you're not a professional programmer dissuade you from learning to do even light coding. Once you master things like macros, queries, designing simple forms, and creating tables, you're ready to begin creating truly rich user experiences. You know, validating what the user entered, launching a hyperlink when they double click on a field, filtering subforms based a button selected, etc. To do so, you need to learn about Visual Basic for Applications (VBA).

Think of learning VBA in Access as the equivalent to learning a new spoken language. Sure, you can't order a sandwich and bottled water from a sidewalk cafe with VBA, but it is a great way to challenge yourself and keep your mind nimble. And don't give in to elitism when it comes to programming. Some might call what you create "scripts" or "macros," but make no mistake, when you learn to code in Access, you're doing more than just recording macros. You'll find that a good majority of Access is exposed for you to command from VBA, and with some extra learning, you can find out how to command Windows interfaces to create even more comprehensive solutions.

Like I mentioned in my last post, one of the easiest ways to learn is by example. When you start, it will look very scary. Spend some time taking baby steps (yes, the dreaded "hello world" examples). Try recording macros and then look at the code to see what it took in VBA to automate what you did using the keyboard and mouse. Then move on to learn about the general framework for how things get done using VBA (the object model in tech-speak). Curiosity and a few hours messing around can get you quite far if you are patient with yourself. The same would hold true if you had a few hours to sit with someone that speaks another language...

Learn By Example

Here's an important tip, especially if you are starting out like I did, with absolutely no knowledge of SQL, Visual Basic, macros, etc. Learn by example. Find any examples you can, dissect them, find out how they tick, and then apply that to your own project. Microsoft put out some terrific example databases for Access. The great thing about them is that they demonstrate a specific feature you might want to add to your own solution, like sorting a report dynamically, or closing a report automatically if it has no data.

Report sample database

Query sample database

Northwind Traders sample database

Decision #1 - When NOT to D-I-Y

In most cases, this decision will be easy for you. Surely creating your own database is better than what you're starting with, right? In the vast majority of cases, probably very true. But there are some very good reasons to contemplate other solutions. A few things to consider below:

1. What will the database be used for?
If you're thinking of creating your own database to store orders, key customer information, or any other sort of "transactional" or business-critical information, think about it carefully. Now this will largely depend on the size of your organization, but if you have any sort of development or IT resources, or adequate money, consider leveraging your development staff to create the solution you need, or buy one off the shelf. If you work in a small company, creating your own database is probably the best, and appropriate, option you have. If your database is going to be used to store data for what I'll call "secondary use," like analysis or data crunching, creating your own solution is a great way to go. You don't need to worry about multiple simultaneous users, audit trails, etc.

2. Who will be using it?
I'm reminded of a project I undertook years ago when I was in sales. Our customer relationship management (CRM) implementation was a disaster, and my VP wanted something that our group of eight customer-facing staff could use to track activities and opportunities. Simple enough, I had created plenty of databases at that point. I failed to realize just how slow a desktop database application like Access performs when it's sitting on a shared file server. When more than one user was in it, it was abysmal. My point is this, if it's just you and a few others working in the database infrequently, Access is a good choice. If it's you and ten+ colleagues looking to maintain important information on a daily basis, you're probably better suited for a more robust solution. That might come in the form of purchased software, your own development group creating a more scalable application, or putting in a SQL Server or MySQL database with an Access front-end.

3. Where do you want to go?
After years commuting sixty miles, I took a job with one of the largest software companies in the world, much closer to home. One of the things we tell customers when they are evaluating our software versus their own in-house solution is this...do you want to continue focusing your energy and time developing software that is already available and evolving over time? To be clear, this blog has nothing to do with my primary employer or what they sell, but I'll pepper my comments with experience from there. My point is this, if what you're doing is considered to be fairly "mainstream" in terms of enterprise software, it's important to think about whether ten hours here and ten hours there may add up to a whole lot of time just to create something that already exists (time-value of money). In many cases, you're probably creating something very specialized for your business needs that just doesn't exist in any pre-packaged software. But be sure to do the research. If something already exists, is it light-years ahead of what you're doing and in your budget ballpark? You'd be amazed at what is out there. If something does exist, but it's out of your price point or doesn't quite fit your needs, D-I-Y on with confidence.

4. Will it get you praise
If you happen to report to someone (i.e., you're not your own boss), test the waters with what you're planning to do. In most cases, you're no where near a software developer much less in a development/IT role. So if your manager isn't keen on you becoming one on the side, find out early. You may be able to bring them around ("we can save X hours a week if you let me"), or get them on your side to push for the "right" people to do it. Either way, taking on projects like this can get your promoted, or stall your career if your management thinks you're not focusing on what's really important.

There Must Be A Better Way

About You
You're a professional out in the wide world who one day discovered their set of Excel spreadsheets was getting out of hand. You were curious about this item on your start menu, Microsoft Access, and decided to see what it was all about. Once you got past the deceptive key icon, which hints that Access is some sort of security application, you started to see potential to take that spreadsheet with ten tabs and thousands of rows into something a little more useful for you and your office mates. A few months later, and a few hours of self-learning under your belt, and you've got a fairly slick database that saves you time and money. Now you're wondering....what else could I do to make it better...should we migrate to SQL server...how can I make it easier to change forms...how do I handle multiple users...do I turn this over to our development group...do I buy off the shelf software...and so on.

About Me
I'm you, well, not exactly. But I can say I've been there myself, faced many of the same decision paths, asked many of the same questions, and found it incredibly fulfilling to easily create my own applications with an interface that is just the way it should be for my business. I started out trying to reduce an awful, ugly 28 step manual process of taking machine data, putting it into Excel, changing it, and then dumping it back into a machine readable format or pretty fax report. Interestingly enough, that ugly manual process was used to manage about 20% of all 9-1-1 emergency address records on the Eastern seaboard until 2000. So when they hired me fresh out of college, I sat through the training on this nasty little error-prone process and thought....there must be a better way.