Chapter 22
In This Chapter
Converting a flat file to relational tables with Table Analyzer
Documenting your database
Fine-tuning your database with Performance Analyzer
The Access Analyzer tools promise to help you set up, document, and fine-tune the performance of your database. Sound too good to be true? Well, like most software tools that promise the blessings of automation, the Analyzer tools do some things well — and others not so well.
Here is what the Analyze tools promise to do:
So which Analyze tool makes good on its promise? That would have to be the Database Documenter. To do manually what the Database Documenter does would take the average person hours, if not days. If you’re using the Analyze tools for the other two tasks, they don’t quite deliver on their promises — but they do have merit, so I cover them as well.
Doesn’t this sound great? The Table Analyzer promises to take a messy flat-file table (such as an imported spreadsheet) — with all its repetitive data — and convert it to an efficient set of relational tables. But, as the saying goes, promises made are promises broken. Unless your flat file follows some strict rules, the Table Analyzer won’t quite get it right. I would love to tell you what those rules are, but only Microsoft knows the mysterious rules of the Table Analyzer.
Sometimes you get a perfect set of relational tables, and sometimes the Table Analyzer doesn’t suggest a new table when it should or suggests a new table when it shouldn’t. My advice to you is to give it a try and see what happens. Best case, it works right and you’ve just saved yourself a boatload of time. Worst case, it doesn’t work right and you wasted a few minutes of your time. Most of the time, you’ll get something of value that may need tweaking. At least you saved a partial boatload of time!
With that thought in mind, here’s how to invoke the Table Analyzer Wizard:
Click the Database Tools tab.
The Analyze group of buttons appears on the Ribbon as shown in Figure 22-1.
Click the Analyze Table button from the Analyze group.
The Table Analyzer Wizard dialog box appears (see Figure 22-2).
Read the first two screens if you want (they’re strictly educational); click Next after each one.
Another Table Analyzer Wizard screen appears, as shown in Figure 22-3.
Click Next.
In the dialog box that appears, the wizard asks whether you want to just let the wizard do its thing (the wizard will decide how the flat-file table should be arranged into multiple tables) or if you want to decide which fields go to what tables.
Click the Yes option (if it’s not already selected) to give the wizard full power in deciding the fate of your table, and then click Next.
If the wizard recommends that you not split your table, click the Cancel button and pat yourself on the back for a job well done. This message means that the wizard thinks your table is fine just as it is.
If the wizard does split your table, it will analyze your table and show you its findings. The results look like those shown in Figure 22-4.
When you’re finished arranging and naming your tables, click Next.
The wizard automatically selects a key field for each table that it thinks needs a key field. Should the wizard select a field incorrectly as a key field, you can correct the error.
Click Next for the final step in the process.
The wizard offers to create a query that looks and acts like your original table. If you have reports and forms that work with the flat file, they’ll work with the new query.
At this point in the process, you may not see the last screen of the wizard as described in Step 12; the wizard may tell you that some of your data might be incorrect and that you should correct it. For example, if you have a Country field in your table and there are two entries such as USA
and UAS
, the wizard will ask you for — or suggest — a correction for the error. You definitely should correct this type of error. Unfortunately, I’ve found that the wizard is often not very good at identifying such errors. (It might suggest that you change Belgium
to Brazil
, for example.) My advice is to take a quick peek at the data for actual errors and move on — because the suggested “corrections” are often incorrect!
Choose Yes to have the wizard create the query or No to skip query creation.
Choosing Yes creates a query that runs against the new tables. The query looks and acts like the original table. The original table is renamed with an _OLD
slapped on the end, and any reports and forms use the query (rather than the original table) automatically. Choosing No generates the new tables but leaves the original table with its original name.
Click Finish to exit the wizard.
The wizard completes the process of splitting the flat-file table into a set of relational tables.
In the world of database development, the last thing on the to-do list — if it’s done at all — is database documentation. You’re probably asking yourself, “Why do I need to create a mountain of pages to tell me about my database?” Well, if you’re the developer of that database and something were to happen to you — say, you’re promoted or you leave the company — someone else would take over responsibility for the database, and be up a creek without a paddle. A well-documented database is easier to maintain than one that is not documented.
Why is this important step rarely done? Because it takes time and money — both of which are in short supply for most businesses. Enter the Database Documenter. It browses through everything in your database and records the minutest of details about each item, be it a table, field, form, query, or report. The Database Documenter collects information so obscure that the programmers might not know what some of it means.
The Database Documenter is fast and easy. All you have to do is turn it on, and it does the rest. Before you know it, the timely and costly job is done!
Here’s how to document your database:
Open your database and select the Database Tools tab.
The Analyze group appears on the Ribbon.
Click the Database Documenter button in the Analyze button group.
The Documenter dialog box appears.
Click the Select All button to document every object in your database, and then click OK to start the process.
The Documenter begins by examining all the objects in your database, starting with the tables and moving on to the queries, forms, reports, and so on.
The process can take a while, depending on the size and complexity of the database you’re documenting. So you might want to run the Documenter before a coffee or lunch break. There’s nothing more satisfying than “working” on a break. When the Documenter finishes, it creates a lengthy report about your database, as shown in Figure 22-6.
Click the Print button on the Ribbon to get a paper copy.
Access doesn't care a whit about trees or how much paper costs, so it generates hundreds of documentation pages about your database. For a small to mid-size database, this can be 500 to 1,000 pages of information. If you really want everything documented, and don't want a couple of reams of paper describing your database, consider saving the report and then referring back to it later. To store the report electronically, right-click the report while it is in Print Preview and choose Export ⇒ PDF or XPS. Select the destination folder and type a filename; then click Publish. The resulting exported file can be opened in a PDF viewer such as Adobe Acrobat, when needed.
From the Database Tools tab, click the Database Documenter button in the Analyze button group.
The Documenter window opens.
Click the Select All button.
Steps 2 and 3 tell the Documenter that you want to document all objects in the database.
Click the Options button in the Documenter window.
The Print Definition dialog box for the object selected will appear. (Figure 22-7 shows the Print Table Definition dialog box.)
Uncheck all the check boxes in the Include for <object type> section.
In the Print Table Definition dialog box, for example, this section will be labeled Include for Table.
In the remaining Include For sections, select the second radio button — the one below Nothing (as illustrated in Figure 22-7).
In the Print Table Definition dialog box, for example, these sections are labeled Include for Fields and Include for Indexes.
The Performance Analyzer is an Access tool that reviews each database object you designate and makes suggestions on how to improve the object’s performance. It might, for example, tell you to break up a complex form that loads slowly onscreen into several smaller forms that will load faster. Use the Performance Analyzer to (a) locate problem objects that are affecting database performance and (b) improve those objects. This procedure, in turn, improves performance.
Here’s how to use the Performance Analyzer:
Click the Database Tools tab on the Ribbon.
The Analyze button group appears.
Click the Analyze Performance button from the Analyze button group.
The Performance Analyzer dialog box appears.
Choose the database objects (such as forms and reports) that you want to analyze.
I recommend clicking the All Object Types tab and then clicking the Select All button. The Performance Analyzer dialog box is similar to the Documenter dialog box (refer to Figure 22-5).
Click OK to run the Performance Analyzer.
You’ll see a dialog box flash onscreen, listing each database object as it’s analyzed. Eventually, the results of the analysis appear in a dialog box. If the Performance Analyzer has no suggestions, you can skip Steps 6 and 7, congratulate yourself for your superior database design skills, and then move directly to Step 8.
Select each result (as shown in Figure 22-8) and review the comments.
If Access can make the changes for you, the Optimize button is enabled. Otherwise, use a pencil and paper and jot down any good thoughts that Access may offer. Sadly, Access does not provide a way to print the Performance Analyzer’s results — hence, the pencil and paper.
To implement a task from the results list, select it and click the Optimize button.
The Optimize button won’t be available for all recommendations — only for those tasks that Access can implement.
After Access performs the task, a blue check mark appears next to the task on the list.