Data Management optimize

10+ things you should do before building a custom Access database

Whether you take on freelance work or you support your organization's IT needs by developing custom database solutions, you must build an efficient, easy-to-use database if you plan to stay employed. See what you can do even before you build a database to make sure it fits the organization's purpose and goals.

Whether you take on freelance work or you support your organization's IT needs by developing custom database solutions, you must build an efficient, easy-to-use database if you plan to stay employed. Like most things, there's a right and a wrong way. You might not get credit for doing things right, but you'll certainly hear about it if you do things wrong.

The road to the right database starts well before you start building tables. There are a number of things you can do before you build a database to make sure that the development process goes smoothly and that your custom database fits the organization's purpose and goals. The following tips are aimed as Access users, but most of them apply to just about any custom database.

Note: This information is also available as a PDF download.

#1: Make nice

You'll get nowhere without the support and guidance of two specific groups of people:

  • Those who update the data. These people know what's needed to get the job done.
  • Those who use the information. These people know the goals for the database and the business at large.

In a small company, one person might fill both positions, although they have different needs. However, that person's experiences with the data are valid. It's your job to find solutions that satisfy everyone, within reason.

#2: Bend but don't break

Being just a developer won't get the job done. Sometimes, you must be a diplomat. I recommend that you practice the art of persuasion: "Let me show you something..." will serve you better than "That can't be done." This may require you to think fast on your feet. Of course, "Let me work up an example" can always buy a little time.

#3: Actually review their specs

Sometimes, you get lucky and someone in-house supplies a list of specifications. If that happens, it's information worth keeping, so don't be too eager to trash the list. Working with those original specs will save you some time and might keep you from stepping on someone's toes -- never a good idea if you can help it.

#4: Compare the specs to the working environment

Most Access databases have just a few users, but Access can handle numerous users. You probably won't build an interactive intranet database the same way you'd build the solution for a single user. Access seldom fails to meet the demands if you develop for multiple users from the beginning.

#5: How many keys are there to the front door?

Keeping hackers out of your intranet or Web-based database is much more complex than using Access' workgroup security. In fact, if you need this article and you've taken on a Web-based database project, you might have bitten off more than you can chew -- good luck! Access is certainly up to the challenge, but the truth is, most developers aren't. That's why IT professionals scoff at Access. The sad truth is, many developers don't understand the Web. If you're one of them, don't take on a Web project hoping to learn on the job. You and your client will pay too high a price.

#6: Do the work

Sit down with the people who do the work and learn the process:

  • Review all paper forms in the current process.
  • How much data -- both records and fields -- will the database store?
  • How much searching and sorting will the users require?
  • Where does the data come from? Will the system need to accommodate foreign data?
  • Will the system export data to foreign formats?
  • Review the current reports and analysis. Talk with the people who use them, for insight.

In short, follow the data from beginning to end. There's no substitute for knowing the data and the current motivations that push that data from collection to final form.

#7: Re-evaluate

Once you're familiar with the specs and environment, you might have to shoot yourself in the foot. Access just might not be the best solution for your client. A more powerful system, such as SQL Server 2005 Express Edition, might be a better choice. Or Access might be just part of the solution. For instance, InfoPath's XML-based forms or .NET forms might be more efficient than Access forms, especially if you're publishing data to an intranet or to the Internet. Certainly, Excel's analytical tools are superior to those Access provides. Don't try to stuff the entire works into an Access-or-bust solution.

#8: Recommend the best route -- not the easiest one

Don't be afraid to suggest a major overhaul if you're upgrading a legacy database. Neither the latest and greatest version of Access nor more expensive hardware will resolve performance issues that stem from bad design.

#9: Improve the process

Work with the end users to improve the manual process if there's room for improvement, and there usually is. It's a mistake to computerize the existing workflow until it's the best it can be. Software alone won't improve a bad routine -- it'll just change the problems.

#10: Define and redefine

Once you've gathered all your facts, compose a mission statement for the application. This might require one to several paragraphs. I'm not talking about a new set of specifications. Rather, give your client a realistic review of their needs versus reality. You're simply restating the database's purposes, but with the benefit of your insight into the workflow and organization's needs.

#11: How's that for quick response?

Once you believe you have a good feel for the client's needs and the database's purposes, create a series of mock-up forms to show the client. You'll get a few oohs and ahhhs, but listen to the souls brave enough to say, "But wait..." Their insights may be valid and could save you some trouble down the road. On the other hand, this is where #2 can come in handy. Sometimes, people just can't conceive of doing something any way but the way they know.

You can use graphics software to draw and print the forms or use Access -- it's a great rapid applications development (RAD) tool. And you can really impress your clients by actually using their data. Sometimes, a quick run at normalizing the data can help the mock-up process. It's not strictly necessary, but it may show you some holes you might otherwise miss.


About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

12 comments
mgenovese
mgenovese

All very well - and I agree totally with it all, but what could I do when the client's manager (who couldn't!) totally refused me any access to the end users! His reasoning? He disagreed with all their suggestions, wanting it done HIS WAY! Needless to say when the first version was done to his satisfaction, and I was allowed to get a couple of end users to try it out, it was a complete disaster! Data entry didn't follow the correct and beautifully logical method THEY had asked for! Myself and the data entry team supervisor tried everything to persuade the "dictator" to re-jig it ti work the way they knew was best, but he simply wouldn't listen. At that stage I decided that I really didn't want to put my name to this debacle and instructed my agency to pull me out. Found out about a year later that three other contractors had followed me, all had the same problems, and the senior management suddenly realised they had a "Peter" managing that department and took the appropriate action. I'd add #11 to your list "Never be afraid to refuse to continue when the client is an stubborn idiot!"

Tony Hopkinson
Tony Hopkinson

It's rapid, may be. Question 7 Should be question 1 How much data How many users How amny users accessing the same data at the same time. How fast should it be. How complex is it. Why is it never going to be more than desktop application? Normalisation is strictly necessary at least to 2NF, I always go to 3rd (and then denormalise if necessary). If only because then the developer will learn the difference between a database and bunch of columns. Which far too many access 'developers' struggle with badly.

nwoodson
nwoodson

These are good practices for any project that has an end-user implementation. It?s even the basic template for construction project development in the Army and many of the same issues exist. The most common issues that I've encountered are the 'We want....X' that has little to do with a rational project requirement and ?This is the way we?ve always done it?, the tribal knowledge argument. These behaviors generally tend to arise with the same folks who say "I don't know anything about computers except how to turn them on." The exception that I take with those types of people is that they tend to be resistant to learning things that are outside of their scope of work including the thought process required to determine their own needs. This situation tends to result in the need to pull the proverbial hen's teeth to determine the difference between needs and wants. Once I see the "real" scope and (more importantly) the procedure of the existing operation, I question the immediate need for previously non-existent. Typical management MO comes into play??This is how we want to do it?. Often, the best technical planning is ruined by non-technical managers who believe they know better. What I?m honestly waiting for is someone who has a good method for dealing with that problem besides trying to sell what's provably correct and reasonable. My experience is that much time and many resources get wasted trying to educate peripheral managers on things that primarily involve end-users, their supervisors and managers.

ssharkins
ssharkins

I think we've all probably run into that sort of manager. Mine was a very nice man who'd been using a dBase application where he had to "reuse" deleted records. He would find a record that he could delete and then re-enter new information over the existing data. I tried my best to explain that was unnecessary and I refused to write it that way. He refused to pay for the work I'd done -- lesson learned.

ssharkins
ssharkins

1.) I disagree with your title -- I do think Access is a great RAD tool. It's Okay if you don't. Won't mention it again. :) 2.) You're free to rearrange the items in any way that works for you. :) 3.) I didn't say don't normalize -- I mentioned that sometimes a quick normalization pass helps with the mock up process. Remember, at this point, the developer hasn't actually started developing. I've talked with developers who do it both ways. Once you're in the development process, of course you must normalize your data.

ssharkins
ssharkins

I like that phrase. :) There isn't any magic cure for the problem and I think the more experience you have dealing with resistant people, the better you get -- but there's no magic bullet. I think the key for the developer is to show resistant folks a better way, perhaps without them really know it. If you go into it saying, "Now, I've done this differently..." they're going to zone out and it won't make a bit of difference -- they will refuse to see your better way. A more neutral approach is probably best, but can't recommend a specific scenario. Years and years ago, one of my first jobs was for a data processing company, but I wasn't processing -- just working in the office. I took over their purchase order system from a woman who'd been pushing those papers around for 20 years. After I was familiar with the system, I cut out several steps and --- now I am serious about this following statistic -- reduced my time about 60%! She was fit to be tied and never forgave me. That woman made it her life mission to get rid of me, and she eventually did. About 10 years ago, I took on a small Access project for a realty company. They'd been using a dBase system that was truly bad -- they had to delete pk values and reuse them manually -- groan -- well, I tried to rework that for them, but the owner just could not get his head around it. He "needed" to continue that process and I refused to go along. I never finished the project and he never paid for what I'd done -- burned. :( I realize that telling those stories won't help anyone, but the problem is very real.

RayJeff
RayJeff

article. You know, I don't have a problem with a manger who has the knowledge and experience with developing applications. But, when they have never developed at all, much less have proven time and time again to not be IT-literate to begin with...then there is the issue. But, because they feel they are the BIG CHEESE just because their title says so, well..we know the inevitable... What we say is all wrong and what they say is the gospel.

Tony Hopkinson
Tony Hopkinson

right at the start. Or an object hierarchy or a start at a top down. A piece of A4 with some squares and lines it. Build it up as you go along, especially for RAD. You don't have to flesh it out and some (hopefully smaller) pieces will end up appearing as if by magic and entire lumps might get dropped. The key thing to get a handle on is the scope. The better questions you can teaze out of the user/owner earlier the more likely you are to keep a handle on things, especially exceptions. I've seen the nebulous oh we'll think about that later manouvre. It does not work, you end up with QAD instead of RAD, and you are marketing a prototype as the new super 'improved' version, because that's all you have. After a certain point on investment and a set of promises / hopes you don't get the option to can it, or start again and 9/10 you end with what we decided in a previous thread was flakey crap. Don't get me wrong access has it's uses, but the number of times people end up bumping into its limitations, which they didn't even know existed is scary. If there's one phrase guaranteed to make me go paler in IT, it's Access Enterprise Application.

RayJeff
RayJeff

"My experience is that much time and many resources get wasted trying to educate peripheral managers on things that primarily involve end-users, their supervisors and managers" I dealt with that situation. I was developing a database for the program in the division of a college. During the process, I had to work with the college MIS director. I had been working on the database for over a year. We would meet twice a week to go over things. I showed the director what I did and explained why I developed it the way that I did. Well, how about he took everything I did and created a totally "new" database. It would've been new if it wasn't a blatant rip-off of what I did. So, when we discussed the "new" database, we had a disagreement on several of the tables with how they were different in the new database vs the original. The problem was that the director had not ONCE took part in any of the meetings for information gathering; had not worked with my supervisor...nobody, whereas I work directly in the division and in the program; I had direct contact to all stakeholders. So, after a year of having a working model, then they step in because obviously they could took everything I did and say that "now, I can create a database". Needless to say, their version was a piece of crap. How do I know this? Unbeknownst to the director, I was having their database and my database peer reviewed by database professionals. And not only that, I was already consulting with database professionals at colleges and universities who developed databases in that particular situation. So, the work I did was on the money considering the tools I had to work with. Back to the disagreement, when I tried to explain why their way would not work for the user requirements, the director get heated with me. Oh, did I forget to say that it was in the presence of several students in the computer lab we would meet in? So, at the end, the question was put to me should we go with my database or theirs. As much as I didn't want to, I "took one for the team" and said that we would continue with using the director's version. The reason why was that it the database was one major part of a campus-wide effort for what the database was a part of; so, I had to play "the team player". Of course I was very upset that it was so convenient for the director to be a team player now after I have done all the work for them and still was doing the work, because I still had to create the "new" database as well. All and all, this was one very being learning experience for me on so many levels. You are right, Susan. This is a very real problem. But, it's a problem IT professionals need to be aware of.

ssharkins
ssharkins

I'd like to say no one else sells my work, but as an author... you'd know I was lying.

Tony Hopkinson
Tony Hopkinson

mock up, which they've just sold 150 copies of on the basis that it does another ten things it can't, oh and could I have it ready by yesterday. I combat this by making all my mockups look really crap. :p

ssharkins
ssharkins

I love Access. I love Access. I love Access. I'm in the zone... don't bother me. Okay... seriously -- I don't take on enterprise level development -- I don't want to work that hard. :) I'm serious. I like to go for long walks, birdwatch, take care of grandbabies, and have sex occasionally. An enterprise-level project would be the end of life as I know it, and I like life as I know it. Regarding RAD: I'm not suggesting anyone offer a prototype db. I said they could use Access to quickly produce form mock-ups. I stand by that statement. :) I do it all the time. Now, I really must return to the zone. I love Access. I love Access. I love Access.