Chapter 24
Ten Uncommon Tips
In This Chapter
Documenting everything
Cutting the excess from your fields
Storing numbers as numbers
Validating your data
Naming your tables well
Dodging deletion distress
Thinking about the best, planning for the worst
Keeping your data ducks in a row
Sending out an SOS
Technical experts — the geeks/gurus who really know Access — might be annoying (perhaps simply because they exist and know way more about databases than a human should), but they’re important. They’re important to average Access users because they provide invaluable advice, and they’re important to Access itself because they drive the way Microsoft continuously improves its products. These gurus are the people who test Office products before new versions are released to the public, and they’re the ones who write books (like this one and more advanced books for more advanced users) to help users of all levels make the most of the software.
So the people who develop databases for a living are an essential resource to the average user, to the “power user,” and to the software manufacturer as well. This chapter is a compilation of some of the best advice gathered from a long list of Access experts. Knowing they were offering suggestions for new users, they offered advice to fit your needs and help ensure that, after reading this book, you really can use Access confidently and effectively. By the time you finish, you’ll have given your efforts the right amount of planning and organizing — and you’ll have solid plans for moving forward with your development and use of the databases you build with Access.
So here’s the sage advice — in ten quick bites.
Document Everything as Though One Day You’ll be Questioned by the FBI
Don’t skimp on the time spent documenting your database. Why? Because you’ll be glad later on that you didn’t skimp. You’ll have all your plans, your general information, and all your ideas — those you acted on and those that remained on the drawing board — ready the next time you need to build a database. You’ll also have them to refer to when or if something goes wrong with your current database. You accidentally deleted a saved query? No problem. Refer to your documentation. Forgot how your tables were related? Check the documentation and rebuild the relationships. Need to explain to someone why or how you set something up? Refer to your notes and wow them with your forethought and careful consideration.
So what should this glorious documentation include? Well, everything. But here’s a list to get you started:
General information about the database:
• File locations (with specific network paths, not just drive letters)
• Explanation of what the database does
• Information on how it works
Table layouts:
• Include field names, sizes, contents, and sample contents.
• If some of the data comes from esoteric or temporary sources (say, the shipping report that you always shred right after data entry), note that fact in the documentation.
Summary of reports:
• Report names
• An explanation of the information on the report
• A list of who gets a copy of the report when it’s printed
Jot down the job title and department in the documentation as well as the current person in the position.
If you need to run some queries before creating a report, document the process. (Better yet, get a friendly nerd to help you automate the work.)
Queries and logic: For every query, provide a detailed explanation of how the query works, especially if it involves multiple tables or data sources outside Access (such as SQL tables or other big-time information-storage areas).
Answer the question “Why?”: As you document your database, focus on why your design works the way it works. Why do the queries use those particular tables? Why do the reports go to those people? Granted, if you work in a corporate environment, you may not know why the system works the way it does, but it never hurts to inquire.
Disaster-recovery details:
• The backup process and schedule
• Where backup tapes or discs are located (you are making backups, right?)
• What to do if the computer isn’t working
If your database runs an important business function — such as accounting, inventory, point-of-sale, or order entry — make sure that a manual process is in place to keep the business going if the computer breaks down — and remember to document the process!
If you need help with any of these items, ask someone! Whether you borrow someone from your Information Systems department or rent a computer geek, get the help you need. Treat your documentation like insurance — no business should run without it.
Keep Your Fields as Small as Possible
As you build tables, make your text fields the appropriate sizes for the data you keep in them. By default, Access sets up text (now known as Short Text) fields to hold 255 characters — a pretty generous setting, particularly if the field holds measly two-letter state abbreviations.
A hundred or more extra spaces — that go unused in most text fields — wouldn’t seem like anything to lose sleep over, but multiply that space across a table with 100,000 customer addresses in it, and you get many megabytes of storage space that are very busy holding nothing.
Use Number Fields for Real Numbers
Use number fields for numbers used in calculations, not for text pretending to be a number. Computers perceive a huge difference between the postal code 47999 and the number 47,999. The computer views a postal code as a series of characters that all happen to be digits, but the number is treated as an actual number that you can use for math and all kinds of other fun numeric stuff.
If you’ll calculate with the field, use a number type.
If you won’t calculate with the field, store the field as text.
Validate Your Data
Validations can help prevent bad data from getting close to your tables. Validations are easy to make, quick to set up, and ever-vigilant (even when you’re so tired you can’t see straight). If you aren’t using validations to protect the integrity of your database, you really should start. Flip back to Chapter 6 and have a look at the topic.
Use Understandable Names to Keep Things Simple
When building a table or creating a database, think about the database file, field, and table names you use:
Will you remember what the names mean three months from now? Six months from now?
Are the names intuitive enough that someone else can look at the table and figure out what it does, long after your knowledge of Access puts your career on the fast track?
Windows allows long filenames. Use them. You don’t need to get carried away, but now you have no excuse for a file called 12Q1bdg5. Using 2012 Q1 Budget Rev 5 makes much more sense to everyone involved.
This becomes even more important as you start using the tools for putting your Access tables and databases online and sharing them via SharePoint — potentially you’re bringing millions of users “to the table.” Also, with the Application Parts feature, the components of your databases can be recycled to help speed the creation of a new database. You don’t want mysterious names for the parts of your database to spread to a new database — to create nightmarish mysteries there too — right?
Delete with Great Caution
Whenever you’re deleting field values from a table, make sure you’re killing the values in the right record — check again, and then only when you’re sure, delete the original. Even then, you can still do a quick Ctrl+Z and recover the pestiferous thing.
Backup, Backup, Backup
Did I make that clear enough? Always keep a backup of your work! There’s no substitute for a current backup of your data — particularly if the data is vital to your personal or professional life. Effective strategies often include maintaining backup copies at another location in case a disaster destroys your office, be it a home office or an office at your employer’s location.
If you’re thinking that you’ve never needed a backup before so why bother, think about floods. Think about newscasters saying that an area currently underwater has never flooded before. Picture people’s lives floating down the street. Whether you’re faced with a real disaster of hurricane proportions, a fire, or your computer’s hard drive deciding to die (and that does happen — even if it has never happened to you before), you’ll be much happier if you have a backup of your database.
Think, Think, and Think Again
You know the carpenter’s slogan, “Measure twice, cut once”? The same can be said for thinking when it comes to your database. Don’t just think about something, come to a quick conclusion, and then dive in. Wait, think it through again, and then maybe think about it a third time. Then draw a conclusion and begin acting upon it. With all the power Access gives you, coupled with the capability to store thousands of records in your database, a relatively simple mistake can be quite costly because of the potential ramifications in terms of data loss or an “un-undoable” action taken in error.
Get Organized and Stay Organized
Although the suggestions to get organized and to keep it simple may seem to be at odds, these two pieces of advice are really companions. Keeping things simple can often be a way to avoid the need for a lot of organization after the fact. While you probably got tired of hearing your parents remind you that “there’s a place for everything, and everything in its place” (or if they were less poetic, “Clean your room!!!”), they were right.
If you keep your database organized, you’ll save yourself time and grief. A well-planned, well-organized table will be easier to query, report on, and include in a form. It’ll also sort and filter like lightning.
There’s No Shame in Asking for Help
If you’re having trouble with something, swallow your ego and ask for help. Saying “I don’t know” — and then trying to find out about what you don’t yet know — holds no shame. This rule is especially important when you’re riding herd on thousands of records in a database. Small missteps quickly magnify and multiply a small problem into a huge crisis. Ask for help before the situation becomes dire.