Excel, Access, SQL, oh my!

As a professional who's full time job isn't creating software, you're likely faced with the question of whether to use Excel, Access, or something else for that project you're beginning or mulling over in your head. There is no simple answer, but the table below is intended to help compare and contrast your options.

Use	Spreadsheet (Excel)	Desktop Database (Access)	Server Database (SQL Server, MySQL) with Access/VB user interface
Validate basic user input (e.g., for a single field/cell	Yes	Yes	Yes (probably overkill by itself)
Complex user input validation or events (if field X = Y, and field Z is empty, perform some action)	Challenging to accomplish	Yes	Yes
Data arranged in a few columns without much repetition	Yes	Yes	Yes (probably overkill by itself)
More than 65,535 rows/records	No	Yes	Yes
Simple cross-references	Yes	Yes	Yes
Complex data analysis/queries	Limited	Yes	Yes
Events based on user actions (when user exits a field/cell do X, when they mouse over a field/cell do Y)	No	Yes	Yes
Multiple users, but generally accessing data at different times	Yes (but conflicts can occur)	Yes	Yes
Multiple users, accessing data at the same time	No	Yes (generally slow)	Yes
Frequent mass-update operations	Challenging to accomplish	Yes (can be slow if resource intensive)	Yes
Multiple user forms	No	Yes	Yes
Mail Merge with Word	Yes	Yes	Yes
Reporting	Challenging to accomplish	Yes	Yes
Automated backups	Challenging to accomplish	Challenging to accomplish	Yes
Record-level auditing	No	Challenging to accomplish	Yes
Ability to hide the inner workings of your project from the user	Challenging to accomplish	Yes (MDE files)	Yes
Level of Effort/Difficulty	Low	Moderate-to-High	High

User Level Access Menus

So after years of reading that people want Microsoft to provide user-level Switchboards, and seeing that Microsoft hasn't done so in 2007, my company decided to release one. This is really exciting news for anyone that has, or will have to, create a database that will be used by more than one user, and where those users have different needs in terms of forms and data access. It was a lot of fun to work on the project and we've gotten some great feedback so far from our customers (that's always the most rewarding part, isn't it!).

Free online tutorials for Access

Here is a site with some fantastic free tutorials on Microsoft Access. I'm a largely visual person, so these videos hit the spot when it comes to picking up techniques to make better Access applications.

http://www.datapigtechnologies.com/AccessMain.htm

Are you a candidate for outsourcing?

There comes a point where you may reach your limit in the time you're willing to learn. And whether you reached that point being self-taught or through courses, you may have to ask yourself whether it's time to get a professional to help. Now let me begin by saying I don't offer these sorts of services, so my viewpoint is "relatively" unbiased. I've done a little freelancing in the past, and the lessons I've learned from that experience, and generally being on the requesting end of development work, are hopefully informative. I'll assume you, the reader, may never have been involved in a "professional" development project except to provide user feedback. In my opinion there are a number of things to consider about outsourcing.

Consideration #1: Cost/Benefit
This is key for any undertaking at work, and here it is especially important to weight the costs of your options versus the net benefit you expect. A back of the napkin calculation should suffice:

Option A: Do-It-Yourself

Cost = Time to learn necessary skills plus time to do the actual work times your hourly rate

Benefit = Savings/Sales Increase from new bells and whistles in your database application + future anticipated savings/sales increases for other anticipated projects where you can apply your newfound skills.

Option B: Contract Development

Cost = Time to define project requirements, find a contractor, negotiate a fee, monitor project, clarify questions through project, test final product, and any reiterations to perfect it times your hourly rate.

Benefit = Savings/Sales Increase from new bells and whistles in your database application

There may be no clear winner, and it will also depend on how many great new gadgets you plan to add to your database app. If it's just a few simple features, Option A may do just fine. If you are planning to do some fairly comprehensive automation, make use of the Windows APIs in many ways, or even need to depart from Access given the complexity, B may be a better option. But at least you can make a rational decision with this being one of the data points.

Consideration #2: Commitment

This is a big one, whether you do it yourself or outsource. The question of commitment is, in many cases, an underlying indicator of the benefit you expect when the project is complete, but also speaks to how willing you are to go to bat for your project if things start to take longer or get more expensive than you expected. If you do choose to outsource the work, how committed will you be to hound the developer, if needed, to get it done right and on time? Unlike a do-it-yourself project where you can generally just walk away if things get too hectic or difficult, with a contractor, you're making a commitment to pay even if you get tired of the project. And believe me, no matter how simple the project seems, they generally never are, and it will take a good deal of guidance on your part to get the right end product, even if you can get an all-start contract developer (and there are sure to be some out there). So assess your level of commitment one way or another.

Consideration #3: Who Do You Call?

You have a lot of options out there, fortunately, when it comes to picking a contract developer for Microsoft Access. I won't recommend any since I don't keep abreast of that sort of thing. But you can easily do a search and find several sites that can take you through the process of creating your project scope, requesting bids, etc. There are many, many freelance contract developers out there, and their quality will run the spectrum from downright awful fly-by-night sort of operations to really world-class folks. Some of the better contract developer clearinghouse websites will help you evaluate prospective bids by letting you look at past comments and ratings of people who are bidding on your projects. Referrals should definitely be treated seriously, just like picking someone to paint your house or help with the birth of your child!

Recommendations

So what is a recommended process to acquire, manage, and complete a contract development project? Here is my recommendation, comments on other, better methods also welcome!

1) Make sure you've considered #1 through #3 above.

2) Define your requirements. Focus on exactly what you expect the final product to do from a functional perspective. That means "what the application should do." If you start talking about how you need a VB function to get data from a new table XYZ and put that in a query to produce a report, you're focusing on the wrong level. Document (definitely document!) your requirements thoroughly, and focus on what you expect to be able to do when the project is complete. I'll be posting an example or two in the near future. The more complete and clear your requirements are, the less time you'll spend during the project taking advil as you go back and forth on what something should do or what something means. For those of you that are Seinfield fans, remember the episode where Jerry gets his kitchen cabinets replaced and the contractor asks him a question every five seconds? And when Jerry says "you figure it out!" he ends up with a completely different result than what he wants. Don't let your contractor be that guy, and don't be Jerry.

3) Get multiple bids. Check references. Confirm the scope of the project, the billing rate or fixed fee, and sign a contract! Make sure the contract includes the design, development, testing, progress updates, and some period of post-release support or warranty.

4) Monitor the contractor's work closely, if you don't include it in the contract in the beginning, ask for regular progress updates and samples of what their doing. It ensures you don't fall victim to "throw-it-over-the-fence" contract work where the end product is vastly different than what you wanted because you assumed your contract knew what you meant, and your contract assumed the end product is what you wanted.

5) Avoid scope creep...on both sides. Don't start asking the contractor to add X,Y, and Z if they weren't in your original scope/requirements, unless you're willing to pay for the extra work. And don't let the contractor expand scope without verifying it's all right with you, and agreed upon a fee (if any). You don't want to be on one of those long cab rides from the airport where you're sure there is a shorter way.

6) Make sure the end product is what you asked for, and what you and the contract agreed to during the course of the project. If not, many contract clearinghouses have ways for you to dispute billing.

7) Recommend your contractor, if they were good. Otherwise, let other's know you weren't satisfied, and why.

If you choose to use a contractor, you can often get a better, cleaner end product than what you might have created with the "learn-as-you-go" method. But it takes more effort than just finding the contractor and paying them for the final software! In any case, good luck!

The Importance of Auditing

In many cases, the Access databases we create a innocuous tools to make life easier by automating menial tasks or creating better reports from large data sets. But in cases where your application is going to be processing important information, and by important I mean anything that might impact your customers, revenue, or have a potential for litigation, I can't stress this enough: audit your data.

If you don't have the money to buy an off-the-shelf product that contains the right tools to ensure user activity and application processing is tracked and timestamped, take the effort to incorporate similar functionality into your own applications. A little story for your entertainment:

About eight years ago I took on a position at a local business, a temporary position until I went to grad school (well that never happened, but that's another story). My first full day was a training on how to process service order records that were sent on to telephone carriers on the Eastern seaboard. My primary role would be to spend eight hours a day performing this laborious twenty-five plus step process over and over again to take raw data, put it into Excel, then output it into other formats for processing. I spent some time with Microsoft Access and got that process down to a two click activity, relying on user input only where it was critical and couldn't be readily automated. These records, by the way, effected the way 9-1-1 calls were routed, and emergency services were dispatched. So you can be sure I had every single step audited: timestamp, user, machine, what was happening to the data, before/after snapshots, etc. To my knowledge, that little database is still processing about 10-20% of the 9-1-1 records on the Eastern seaboard. Scary, but I'm confident it's completely error free.

My point to you, the reader, is that whether it's life critical or business critical data, be sure you've covered your bases, and your behind, when you decide to create Access databases that will do more than just store addresses and names. I won't go into great depth on how to do it, but some general guidance:

1) Make sure you get the handy functions available from Dev Ashish's MVP site: API to retrieve a user's network username, API to retrieve the local machine name,API to retrieve the user's operating system name.

2) Create a function that you can call from forms and other VB functions that will handle the logging events for you. I typically write to either a local table, linked table, or file depending on whether it's a multi-user Access application or sitting on someone's PC. Alternatively, you can use a logging API like what comes in UI Builder for Access, instead of creating your own.

3) Insert the logging function wherever you need to track user behaviour, errors, or other system events. This could be as granular as when the user logs in, changes a record, etc. Or it can be just for errors.

No only is it a great way to protect yourself from potential liability, help debug your apps, it also ensures your solution has a greater level of legitimacy and value to your organization.