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. Here 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 to help ensure that 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/data locations (with specific network paths or Internet URLs)
- 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 credit card data that you download monthly online), note that fact in the documentation.
- Summary of reports:
- Report names
- An explanation of the information on the report
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? 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 ask.
- Disaster-recovery details:
- The backup process and schedule
- Where backups are located (you are making backups, right?) and how to restore a backed-up file
- What to do if the database stops working
If your database runs an important organizational function — such as accounting, inventory, contact management, or order entry — make sure that a manual process is in place to keep the organization going if the database malfunctions — and remember to document the process!
If you need help with any of these items, ask someone! Whether you borrow someone from your Information Technology department or rent a computer geek, get the help you need. Treat your documentation like insurance — no organization should run without it.Every 6 to 12 months, review your documentation to see whether updates are needed. Documentation is useful only if it’s up to date and if someone other than you can understand it. Likewise, make sure you (or your counterparts in the office) know where the documentation is located. If you have an electronic version, keep it backed up and have a printout handy — something you’ll be glad you did if you or someone else attempts to recycle your database parts using the Application Parts feature.
Keep your Access database 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 (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 end up with lots of megabytes of storage space that are very busy holding nothing.
Adjust the field size with the Field Size setting on the General tab in Design view.
Use number fields for real numbers in Access databases
Use number fields for numbers used in calculations, not for text pretending to be a number. Software applications perceive a huge difference between the postal code 47999 and the number 47,999. The application 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. The other reason a zip code isn’t a Number field? If you’re in the U.S. and your zip code starts with zero, the application cuts off the leading zero and stores just the non-zero digits in the field – 01234 becomes 1234. Not good!When choosing the type for a new field with numbers in it, ask yourself a simple question: Are you ever going to make a calculation or do anything math-related with that field?
- If you’ll calculate with the field, use a Number type.
- If you won’t calculate with the field, store the field as Short Text.
Validate your Access 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.Use understandable names in Access 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 you’ve moved on to bigger and better things?
Delete Access field values 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 offending item provided you undo right after catching the mistake.Why all the checking and double-checking? Because after you delete a field value and do anything else in the table, Access completely forgets about your old value. It’s gone, just as though it never existed. If you delete a record from a table, the record is really gone — because there is no Undo available for an entire record. If that record happened to be important and you didn’t have a current backup file when the record went away, you’re out of luck. Sorry!
Backup, backup, backup your Access databases
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 at a different office or in the cloud.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 before taking action in Access
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 on it. With all the power Access gives you, coupled with the capability to store thousands upon 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 when working in Access
Although the suggestions to get organized and to keep it simple may seem to be at odds, these two pieces of advice are really companionable. Keeping things simple can often be a way to avoid the need for a lot of organization after the fact. Whereas 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.
Yes, you can get too organized. In fact, over-organizing is altogether too easy. Temper your desire to organize by cultivating another passion: working with as few steps as possible. Limit the number of folders and subfolders you use — a maximum of five levels of folders is more than enough for just about anybody. If you go much beyond five levels, your organization starts bumping into your productivity (and nobody likes a productivity loss, least of all the people who come up with those silly little slogans for corporate feel-good posters).