3 4
The built-in add-ins on the Analyze and Database Utilities submenus of the Tools menu are part of the standard installation of Access and are available as soon as you install Access.
tip - View all wizards in Access 2002
Built-in Access add-ins basically help you do two things: The add-ins available through the Analyze submenu help you analyze and document database components, and those on the Database Utilities submenu have more substantial functionality—they perform housekeeping, repair, and conversion chores. The sections that follow look more closely at all these add-ins.
Three of the built-in Access add-ins help ensure that your tables are well-designed and well documented. You start the analysis add-ins by choosing Tools, Analyze and then selecting one of the following add-ins:
Troubleshooting - I can’t see the wizards I want to work with
If you’ve installed Office XP using the default selection of Office Components, the add-ins on the Analyze and Database Utilities submenus of the Tools menu should be installed. (They’re included in the Typical Wizards component, which is part of the standard Office XP installation.) However, the Add-In Manager, which is needed to install custom Access add-ins, is part of the Additional Wizards component and isn’t installed by default.
If you can’t see the wizards you want to work with, you can rerun Office XP setup and select either the Typical Wizards or the Additional Wizards component (I recommend selecting both) by following these steps:
When you next open Access, the extra wizards will be available.
The Table Analyzer Wizard helps you split a non-normalized table into separate tables, linked by key fields. After splitting the original table into two or more linked tables, the wizard creates a query based on the linked tables and gives it the same name as the table (the table itself is renamed), which ensures that if the table was used as a form, report, or data access page record source, the query will now be the record source.
note
The Table Analyzer Wizard is a great help for pinpointing data errors that often creep into non-normalized data tables, where information has been typed into the fields rather than being selected from a look-up table.
To use the Table Analyzer Wizard on your database, follow these steps:
Figure 15-1. The first page of the Table Analyzer Wizard gives an idea of why duplicate information can cause problems.
Why Worry About Duplicate and Inconsistent Data?
Duplicate information wastes space in a database. For instance, in the example shown on the first page of the Table Analyzer Wizard, when the Supplier ID field is present in the table, all the other Supplier information can (and should) be picked up via a link on Supplier ID to the Suppliers table, instead of being duplicated in the Products and Suppliers table. Inconsistent information (such as the misspelled Supplier name highlighted in blue) means that records belonging to a single supplier will be classified incorrectly as belonging to two different suppliers, at least if the supplier name is used for grouping rather than the Supplier ID.
Also, if you make a change to a supplier name in one table, the change won’t carry over to the other tables, leading to incorrect data in reports and forms. This type of error won’t occur in a normalized database.
Figure 15-2. Scroll through the Tables list, and select the table you want to analyze.
InsideOut
The Table Analyzer Wizard doesn’t always create an optimal set of linked tables—which isn’t surprising because the proper division of data depends so much on the nature of the data and what you’re going to do with it. You might find that the wizard breaks out addresses and ZIP codes into separate tables, for example, which is rarely appropriate. Another problem area: The wizard generally creates new unique ID fields for tables, even those that already have a suitable unique ID field.
Also, the wizard’s practice of renaming the query with the same name as the original table has two problems: It violates any naming convention you might be using (by giving a query the tag used for a table), and it assumes that a query based on linked tables is the most appropriate record source for a form or report. In many cases (particularly for forms), nested subforms, each based on a single table, are a more appropriate interface for displaying and editing data from linked tables.
It’s probably best to split a non-normalized table into separate tables and link them appropriately without using this wizard, because the wizard isn’t smart enough to do the job right. However, the wizard does a great job of tracking down data errors and inconsistencies, so it’s worth using just for that purpose.
After making the necessary changes, click Next.
Figure 15-3. The wizard displays the proposed new set of tables and their relationships.
For more information about using a naming convention in a database, see "Naming Conventions," in Chapter 20, "Customizing Your Database Using VBA Code."
note
Figure 15-4. The wizard gives you the opportunity to fix typographical errors.
Figure 15-5. The final wizard page asks whether you want to create a query based on the tables it has created.
Figure 15-6 shows the query produced by the Table Analyzer Wizard. (Notice that the query has a misleading tbl tag). You can then make any necessary changes to the query in Design view.
Figure 15-6. The query produced by the wizard links the various tables created from the original table.
Troubleshooting - Even after I use the Table Analyzer Wizard, my tables still need work
Although the Table Analyzer Wizard is a real help when it comes to finding errors and inconsistencies in your data, you’ll need to do some of the fine-tuning yourself. You might need to make changes such as these after you use the wizard:
You can’t change these field names in the wizard. If you want to change them (and you probably will), you must do so in the table, in Design view. The changes to the foreign key names will then be picked up in the Relationships window.
The Performance Analyzer evaluates the objects in your database and suggests how the performance of the database might be improved. This tool can make some changes for you automatically, if you choose, or you can make the changes yourself after you exit the tool.
To use the Performance Analyzer on your database, follow these steps:
Figure 15-7. The Performance Analyzer dialog box has a tab for each type of database object (except data access pages).
caution
Figure 15-8. The Performance Analyzer makes suggestions for improving database performance.
Even if you think your database is performing well, it’s still worthwhile to run the Performance Analyzer to see whether it finds anything you missed.
It’s a good idea to print the design attributes of the objects in your database because if disaster strikes and you need to re-create some or all of your database, a printout will provide a useful roadmap. A Documenter printout of field names is also useful when you’re writing VBA code that references table fields. The Documenter is an add-in that evaluates the objects in your database and produces a report of the design characteristics of each selected object, such as table fields and their properties. You can print the report or save it to a file, depending on your preference and needs.
To use the Documenter, follow these steps:
Figure 15-9. The Documenter dialog box has a tab for each type of database object (except data access pages).
Figure 15-10. The Documenter has several options for documenting tables.
Figure 15-11. The Documenter creates an Object Definition report.
The add-ins on the Database Utilities submenu of the Tools menu provide various utilities to manipulate your database. Figure 15-12 shows the selections on the Database Utilities submenu.
Figure 15-12. The Database Utilities submenu of the Tools menu offers a choice of add-ins.
The Convert Database utility was introduced in Access 2000. Before that version, there was no way to save a database in an earlier database format. Access 2000 let you save an Access 2000 format database in Access 97 format, and Access 2002 lets you convert an Access 2002 database to Access 97 or Access 2000 format or convert an Access 2000 database to Access 97 or Access 2002 format.
Converting from New to Old The following steps show a typical sequence of error messages and reparative actions you’d need to take (in Access 97) after opening an Access 97 database created by converting an Access 2002 database to Access 97 format:
Figure 15-13. This message results from a missing reference when a converted database is opened.
Figure 15-14. Two items are marked MISSING in the References dialog box.
In this example, the two references marked MISSING are the problem. You can simply clear the Microsoft Visual Basic For Applications item marked MISSING because there’s already a checked item for the current version of this library (the Visual Basic For Applications item at the top of the list). The Microsoft DAO 3.51 Object Library item marked MISSING should also be cleared, but in this case you need to select the appropriate item as well. To do so, close the dialog box, reopen it, and select Microsoft DAO 3.51 Object Library.
Converting from Old to New To convert an older Access database to Access 2002 or Access 2000 format (whichever one you’ve selected as the default file format on the Advanced tab of the Options dialog box, available from the Tools menu), you don’t use the Convert Database utility. To convert an older database to the current format, follow these steps:
Figure 15-15. This dialog box appear when you try to open an older Access database in Access 2002.
If compilation problems occur during the conversion, you’ll get a different message that reports compilation errors and offers a Help button to click for more information. Click OK to continue.
If conversion errors occur, you’ll get a conversion error message, again with a Help button. Click OK to continue, and you’ll get the message telling you that the new Access 2002 database can’t be shared with Access 97 or Access 2000 users.
Troubleshooting - I’m having conversion problems converting my database to an older format
There are several problems you might experience when you convert a database to an older format. Here are some of the problems and their solutions:
Solution: Make sure that the name you want to use isn’t already in use in the target folder. Rename, move, or delete any existing file with that name.
Solution: Compile the converted database. Generally, that’s all you need to do. In some databases, you might need to rewrite some code to take into account changes in the Access object model or other functionality.
Solution: PivotCharts are new to Access 2002; you’ll have to do without them or replace them with MS Graph charts.
Solution: A reference to an object library hasn’t been downgraded to the correct version. This often happens with references to the Word or Outlook object libraries. To fix this problem, open the References dialog box from the Tools menu in the Visual Basic Editor, clear the references to the Word 10.0 or Outlook 10.0 object library (or any library that’s marked MISSING), and select the reference for the appropriate version of the object library.
note
The Compact And Repair Database utility runs two add-ins (which were separate selections in earlier versions of Access), One add-in compacts a database by removing temporary objects (usually resulting in a considerable reduction in size), and the other repairs database errors. To start the utility, choose Tools, Database Utilities, and then click Compact And Repair Database.
You’ll see a progress bar in the database’s status bar, and if the compact and repair process is successful, you’ll then be returned to the database. If problems occur, you’ll get an error message letting you know what the problem is. Depending on the nature of the problem, you might be able to resolve it; if not, you might have to restore your last backup database.
The Linked Table Manager helps you fix broken links to back-end tables, which usually result from moving the back-end database to another folder. You open the Linked Table Manager by choosing Tools, Database Utilities and then clicking Linked Table Manager or by clicking Linked Table Manager on the shortcut menu of a linked table.
tip - View table links
Because Access lacks a constant that represents the current folder (the folder in which the front-end database is located), you still have to fix broken links even if you move both the front-end and back-end databases to the same folder in a new location.
To fix the broken links, follow these steps:
Figure 15-16. The Linked Table Manager dialog box lists incorrect paths for linked tables.
All links will be refreshed, and you’ll see a message confirming this. Click OK to close the message box. Close the Linked Table Manager dialog box, and the linked tables will have the correct links to the database you selected.
tip
If you don t have a database with linked tables to experiment with, create one with the Database Splitter, as described in the next section, and then move the back-end database to another folder. This will trigger a "Could not find file" error message when you try to work with data in a linked table in the database, which you can fix using the Linked Table Manager.
Troubleshooting - I can’t refresh the link to a certain table
The Linked Table Manager can’t refresh links to tables whose names were changed in the back-end database after they were linked (for example, to give them names using LNC tags). If you can’t refresh a link to a table, delete the link, and then re-create it by choosing File, Get External Data, and then selecting Link Tables.
The Database Splitter is a wizard that automates the process of splitting a database into a front end and a back end. This split makes it easier to make changes to the interface objects without affecting the data.
To start the Database Splitter, follow these steps:
Figure 15-17. The first page of the Database Splitter wizard offers general information about the wizard.
The original database now has links to the tables in the new back-end database, as indicated by arrows to the left of the linked table names in the Database window.
The Switchboard Manager is a wizard that creates a switchboard (main menu) for a database, with text buttons to run various commands. If you click the Switchboard Manager selection for a database that doesn’t already have a switchboard, you’ll get the message shown in Figure 15-18.
Figure 15-18. A message from the Switchboard Manager indicates that the database doesn’t have a switchboard.
You can click Yes to open a dialog box in which you can add new pages to the switchboard or edit existing ones. The switchboard created by the wizard has an old-fashioned appearance. (It hasn’t changed since early versions of Access.) It includes a set of buttons, each of which opens a form or a report or performs some other action. You can also create subsidiary menus—say, a Forms menu and a Reports menu, each with its own set of buttons.
The process of adding items to the switchboard is fairly tedious. After you click Yes in the initial message box, the Switchboard Manager dialog box will open, with one Main Switchboard page listed (as shown in Figure 15-19). (If a switchboard page is not listed, you can click New to create a new switchboard.)
Figure 15-19. The Switchboard Manager lets you set up the default Main Switchboard page.
To add buttons to the Main Switchboard form, follow these steps:
Figure 15-20. You create a button to open a form in the Edit Switchboard Item dialog box.
note
Figure 15-21. The new Main Switchboard form has three buttons for opening forms.
The switchboard form’s functionality is based on a Switchboard Items table created by the Switchboard Manager. This table contains information used in a function in the form’s class module.
tip - Display the switchboard automatically
The Upsizing Wizard automates the process of converting an Access database to a Microsoft SQL Server database, with an option to either create a new SQL Server database or use an existing one. You start the wizard by choosing Tools, Database Utilities and then clicking Upsizing Wizard.
For more information about using the Upsizing Wizard, see Appendix E, "Upsizing to SQL Server."
Saving a database as an MDE file compiles all its modules, removes editable source code, and compacts it. This protects the VBA code from being viewed or edited, while still allowing it to run. Saving a database to MDE is a quick way of securing its code, without the complexity of setting up a secured database. In an MDE file, users can’t do the following:
To save a file as an MDE file, follow these steps:
caution