What is the difference between a Microsoft Access (hereinafter referred to as Access) database and a database application? The answer is “ease of use” and automation of previously repetitive navigation and access to your reports, calculations, and data look-up. Experienced Access users know that as a database grows and is used repetitively, ease of operation quickly becomes an issue as new queries produce specialized reports from previous models or as issues of consistent data entry begin to affect the quality of the growing amount of information that Access can manage for you.
This article is for experienced Access end-users who already know about database objects (tables, forms, queries, reports, macros) and who know how to format fields, design forms, and produce compiled reports from their database. I will assume that the reader is ready to migrate into the “application” phase of database management and just needs to learn the steps.
To make your database into an easier-to-use application you only need to do two things: 1) Create what is known as a “Switchboard” form. (A switchboard has “buttons” to navigate and open your forms, run your queries or reports, etc.); and 2) Make that switchboard form your default opening window.
Let’s do some reverse engineering to get on the fast track to understanding the concept of the switchboard. You must have MS Office Professional installed on your computer or a separate copy of MS Access. You need to open MS Access to a null screen and click on “New From Template” option on the right Task Pane. Select “General Templates” and click on the “Databases” tab in the dialog box that opens. Double click any of the templates and follow the instructions and fill in the data entry options as necessary.
You will end up with the opening switchboard of the new database you created from the template. I selected the “Service Call Management” template, and Access created a ready-to-use database with a switchboard that makes the database easier to operate and navigate. To explore the structure of the switchboard, click on the Tools menu/Database Utilities/Switchboard Manager.
The Switchboard Manger option will open a dialog box that will allow you to explore what’s going on simply by clicking on the “Edit” button in the Switchboard Edit page. Experienced users will recognize that the switchboard pages are groups of related buttons (a maximum of 8 per page). Once you get the hang of programming the switchboard, you’ll discover that the switchboard is a convenient, efficient way to run your database.
Leaving the template now, let’s see how we can program our own switchboard. Let’s say that you have a large customer contact, sales record, and inventory database that has multiple forms, sales records, and monthly reports you need for taxes and inventory. If you’ve been using the database for awhile, it is probably somewhat unwieldy and could use streamlining. Also, you may be ready to train an assistant in data and reports production and don’t have the time to teach the assistant Access database techniques through the object menus. Having a switchboard allows you to simply show your assistant what buttons to press.
From the outset, it is important to note that every table, form, query, macro, and report you’re going to run through the switchboard must be ready and working properly. Next, although not absolutely necessary, you should consider designing your switchboard on paper. What pages do you need? (A “page” is a group of buttons. For each new page, you have to click the “new” button on the dialog box.) Should you have a separate page for record entry? Do you have a specialized group of Macros you typically run to purge or update your database? Maybe you only need one page, in which case you can program form entry, query access, or report printing on one page. If you change your mind, you can always add more pages and buttons by repeating the process I will describe below.
After you have decided on the structure of your switchboard and know that everything is in working order, then you can make your switchboard. Here’s how:
Open your data base. At the opening window click on Tools>Database Utilities > Switchboard manager. You will be prompted to proceed and will be presented with a blank switchboard page. For your first group of commands, select “edit.” Say, you want your switchboard to open a data entry form so you can enter a new record. Next click on “New”
In the small “Edit Switchboard Item” dialog box that pops up, enter the name of the new button in the first line; select from the dropdown list of preprogrammed commands and then choose the appropriate option in the third line. If the option you need is not in the switchboard choices, you’ll need to write a macro and then select the appropriate macro. Once you make your first switchboard form or make any changes to it, you have to close the form down and reopen it to view the new form changes.
Programming your switchboard, after you get the hang of it, is much like programming command buttons on forms. Actually, the switchboard is a form, and the you’ll see a new switchboard table after you create the switchboard form.
The last thing you’ll need to do is to make your switchboard your default view when you open your database. You can do that either by creating a macro called “autoexec” that opens the switchboard form, or, easier still, activating the Tools > Startup menu and selecting the Switchboard form from the dropdown box in the Display Form/Page option in the upper right of the dialog box. The next time you open your database the switchboard form will appear.
That’s it. You can make an unlimited number of switchboard pages for your database. Don’t forget to include a button on each page that returns you to some logical point (usually the main switchboard page). Also, activate the templates included in Access for fresh ideas on programming your switchboard and making your database more useful.