THE NEED FOR A PLAN
Access became a sensation in the 1990s when it transformed the desktop into a system with power that paralleled big mainframe computers without the exorbitant cost to use. One of the Access applications I built, intended for multiple users on a local area network, eliminated a $32,000 monthly mainframe expense. With a little practice, users could create their own queries and reports, enabling them to get answers quickly and avoid the need to ask specialists in another department to pull that information together.
Access is still the most ubiquitous database application development tool for desktop computing. Because of this, one of its strengths—the ease and simplicity in beginning to use the software—can also be a weakness. Lots of people import data from Excel or some other program, and away they go! Yet when users dive in, certain areas of development and organization might be skipped.
Because the purpose of a database is to organize information logically, it makes sense to also organize the information related to the database itself. I talked with two expert Access developers to get their advice and recommendations on how to keep a project organized. Dale Fye owns Developing Solutions, a custom software company specializing in Microsoft Access and SQL Server databases, and is host of the ET (Eastern Time) chapter of AccessUserGroups.org. (See his website at dev-soln.com.) Arvin Meyer, of Data Strategies, is a legendary developer in the Access community who builds custom database solutions and shares code and tools on his website (datastrat.com). Here are some suggestions when beginning your own Access database.
PROJECT FOLDER
Create a folder where you’ll store the Access database itself. Add subfolders to store other information so the main project directory remains uncluttered.
Sometimes it makes sense to store actual files in those folders. Other times, you’ll create shortcuts to additional files and folders, such as for documents that are actively being changed by others. Figure 1 shows a project folder containing subfolders that categorize the various types of information and files that will be used:
Backup. “The most important thing to me is to create a backup copy of your front-end,” Fye said, adding, this applies to “every time you start to edit it.”
By “front-end,” Fye is referring to the file that contains the Access user interface (UI) objects such as queries, forms, and reports, and is linked to tables in another file or files. For databases that will have multiple users, the UI objects are kept separate (each user has their own copy) from the shared data that everyone is linked to. If you’re just getting started with Access, however, you’re likely the only user, and everything (data and UI) is in one file. In that case, “front-end” also means the Access database file (ACCDB or MDB) containing the tables, too.
Because a backup should be made before you open the database each time, name the backup files logically and keep them in a separate folder. Most experienced developers send the database to a zip (compressed) file and add a form of the date (in a format such as “yymmdd”) to either the backup file names or the folder name they’re organized under.
I name the backup folder “bak.” Meyer uses “Archive” so it appears at the top when folders are sorted alphabetically. Figure 2 shows files in a backup folder.
Documentation. As the database grows, so will its complexity. Keep a sense of its structure and the various components within. For example, you might want to save a screenshot of the Relationships diagram since that’s a great way to get a bird’s-eye view of what’s in a database. To document forms and reports for users, you might create a Word document with screenshots and instructions.
A valuable tool to help you document your database is the Analyzer (bit.ly/2STpi4x). The Analyzer contains more than 50 reports that can be generated about your database and saved as PDF files—including a list of the tables showing how many records are in each as well as a data dictionary with detailed information about the fields in each table.
Images. Access can render images that are stored in external files such as JPG, PNG, and BMP. It is more efficient to store the images externally rather than within the database file. This also makes it easier to track images better and more easily modify them.
Notes. As you make progress on developing your Access database application, you’ll have notes or reminders. Here you can also put screenshots, feedback from others, meeting comments, and anything else you want to keep track of that pertains to the application.
Source. This folder is for data to import into your database as well as other applications that you might reference, such as forms or code you’re importing.
DATA SECURITY
If you’ll be working with sensitive information, it’s important to keep the data secure. “When working on files that I need to have secure, I disconnect from the internet and decrypt the folder,” Meyer said. He uses an A/B RJ45 metal rotary manual switch between the computer and the internet, which costs about $15. “A” connects to the internet, and “B” connects to nothing. After working, he encrypts the folder with a password before connecting to the internet again.
SF SAYS
When developing a database, devise a plan and logic for organizing your project files.
March 2020
