Here are a few tips that will help you design and build good Microsoft Access databases.
Tip #1 – Table design and relationship design are important
Microsoft Access provides for rapid application development but that doesn’t mean that good relational design should be sacrificed just because the developer doesn’t think things through up front. Making a solution with proper table and realtionship design is very important.
Tip #2 – Always create a front-end and back-end database
Unless you are just making a quick database for personal use, you should always split a Microsoft Access solution into two databases. This article demonstrates best practices and code you can use to help with linking tables: Microsoft Access database splitting.
Tip #3 – Backup work often while in the development process
In general backing up files often is a good tip for all types of development. Microsoft Access databases can become corrupt under various circumstances and numerous changes of code sometimes require you to look back at previous versions. Backup at a minimum of once per day.
Tip #4 – Provide a calendar for date selection
For all access date fields that the user is entering you should supply a good calendar form for easy date selection. I prefer this code Microsoft Access calendar, however there are many versions of homemade calendars forms available on the web.
Tip #5 – Do proper error handling in all routines
Every function or subroutine should contain error handling. Without it, a user may be left viewing the faulty code in a full version of Access and a run-time version of Access will just crash. Thanks again to Allen Browne for another good link: Microsoft Access error handling
Tip #6 – Use the newsgroups to ask questions
The Microsoft Access newsgroup can be a very useful place to ask general questions and get help.
Tip #7 – Find example code that’s as close as possible to your proposed solution
Never start a development project without looking for code sample or examples that could help you build your solution faster. Why do work when someone else has already tackled the problem for you? Our links page has some places to look as well as our database templates are a great way to start a project and shave a month of the project for just a small investment.