3.2. Database Structure and Management Tools for Developers

As developers, we are always seeking ways to improve and leverage our skills. And, Access 2003 has many new tools that are specifically designed to help efficiently develop powerful applications.

3.2.1. Find Object Dependencies

As a database grows it can be difficult to keep track of object dependencies. For example, several forms could be relying on one query, or a subform could be associated with more than one form. After a while, a developer can become wary of changing or deleting objects, even if he or she is the only developer associated with that database. Things can be even worse if you inherit an application. Talk about proceeding with trepidation. You can now shelve those fears; Viewing Object Dependencies has come to the rescue.

Viewing object dependencies allows you to view what the object depends on as well as what objects depend on it. For example, by selecting a query, you could see what forms and reports are using it and you could also see if it was relying on a form or other queries. The tree view will show tables, queries, forms, and reports that are in the database. However, it will not show macros, VBA code, Data pages, SQL-specific queries, and Access projects. Figure 3-1 shows how to select a table to view the objects that depend on it. By selecting the other option, it will list the objects that it depends on. So, with a couple of clicks, you can look at both types of object dependencies.

The Object Dependencies feature only applies to MDB files.

The Object Dependencies feature employs the functionality of tree view. As shown in Figure 3.1, you can drill into the dependencies. Actually, this feature will allow navigating up to four levels. However, since the information is based on the name maps maintained by the Name AutoCorrect feature, the tracking function of Name AutoCorrect must be turned on; please note that this does not require Name AutoCorrect to be enabled, but merely turned on. Another nice thing is that if Name AutoCorrect was not turned on before requesting to see the dependencies, a dialog box will prompt you to turn it on. Then, if you choose to turn it on, Access will proceed to display the requested dependency information.

To view Object Dependencies, from the View menu item select Object Dependencies. At that point, if Name AutoCorrect was not turned on, there will be a prompt to turn it on so as to view the dependencies. To view Hidden Objects, the Show-Hidden Objects, will need to be selected on the View tab of the Options dialog box (under Tools | Options).

A word of caution here: Don't rely solely on the dependency lists when deciding to delete database objects. There are several objects that will not show up on the dependency view. Access Help "About Object Dependencies" provides detailed information about which dependencies are shown as well as what types of objects are not included.

3.2.2. Form/Report Error Checking

If you are an independent developer, you might think of this as your on-call quality assurance team. All developers know that a fresh set of eyes can be very effective at finding errors that are overlooked. And, it saves a lot of time to catch them early in the process. Well, this feature can flag common errors as they are made. You can't get more timely feedback than this. In addition to flagging errors, the process will even offer solutions.

Since multiple errors can simultaneously occur on the same object, the errors will be ranked from highest to lowest priority. By clicking the error indicator, you can correct or ignore each error. Figure 3.2 shows an example of multiple errors on one control.

Sometimes you just don't want to hear about everything that Access thinks is an error. Access provides the options to not only turn error checking on and off but to specify the settings. This allows you to limit the types of errors that are flagged. You can even select the color used to flag errors. To set these options, once again, choose Options from the Tools menu and then select Error Checking. Figure 3-3 shows how to specify which errors should be flagged.

The common error scenarios that are flagged include the following.

• Unassociated label and control—When a label and a control are selected but they are not associated with each other.

• New unassociated label—If a label is added to a form or control and it is not associated with any control.

• Invalid control source—The control source property is not a valid expression or field name, the expression specified does not start with "=", or the expression refers to the control itself—a circular reference.

• Duplicate Option values—The Option value property is not unique in the option group.

• Keyboard shortcut errors—Such as an unassociated label with a keyboard shortcut, a label or control that has a shortcut that is already associated with another label or control, and a space character as a shortcut key.

• Invalid sorting and grouping definitions for a report.

• Report width greater than page width.

3.2.3. Propagate Field Properties

Changes to field properties can quickly and easily cascade to corresponding properties of controls on forms and reports. And as always, there are a few caveats, such as, this is feature does not apply to Access projects and the control property will not be updated if:

• The control already has a different value specified in the property sheet

• The control is on a Data Access Page

• The field property is changed in a linked table

When an inheritable property is updated, the New Property Update Options button will appear, as shown in Figure 3-4.

Clicking the button opens the Updates Properties dialog box (Figure 3-5). This allows you to choose to update the corresponding controls that are bound to that field. You can conveniently see all of the places that the control can be updated and select/unselect all or specific locations to be updated. Again, the Access team has provided the information and given the developer the final decision.

3.2.4. Customize SQL Font

Finally, you can choose the font for SQL and Query Views. Of course, there is reason to be judicious when selecting a font. Not everyone would be happy looking at script in their queries. But, using special fonts can give a bit of personality to screen shots. Notice that the font appears in both the query grid and the SQL view. Thankfully, it does not appear that changing the font used in queries will affect the appearance of the database sheet views.

When giving a presentation, you could have fun by customizing the SQL font, and even changing the font to suit the audience.

To change the font, click Tools on the menu bar, and then click Options. This will open the Options dialog window. The query design font is on the lower left of the Table/Queries tab. The font type and size can be quickly changed via the Options menu. The Query Design Font is on the lower left of the Tables/Queries tab. Figure 3-6 shows how changing the font can affect the readability of the SQL view. The selected font will also be used in the query grid. Figure 3-7 shows what Chiller font looks like in the query grid. So when you are preparing a presentation, keep in mind that changing the font to make it larger, easier to read, or even just more striking is certainly something worth considering.

Did you know that you can also change the font used by the Zoom Wizard?

You can also modify the font when using the Zoom feature. To do this, right-click in a query criteria field and then select Zoom. In the lower right, click the font and you'll see the familiar dialog box for selecting a custom font. This cool feature was added by Michael Kaplan. Figure 3-7 shows how to change the zoom font as well as the effect of selecting a font such as Kristen. And, as expected with a really good tool, you get to see what the font will look like before it is applied. Now that demonstrates the benefit of having a developer add features. They are one of us so they know what will help.

3.2.5. Get Context-Sensitive SQL Help

When you are working in Access (not the VBE) and need help with a SQL statement, the natural instinct may be to click Help. In the past, this typically led to a futile search, followed by opening the VBE window and starting the search all over again. With Access 2003, you can get help with your code without opening the VBE. There are several ways to get SQL help from within Access.

• The most convenient is typically to hit F1: From the SQL view put the cursor on the keyword that you want help with and then hit F1. Voila, the answer to the question!

• The next most obvious way is to open Access Help and type in the SQL Keywords.

• Alternatively, open Access Help and type in Aggregate Functions, VBA Functions, or Access Functions.

• And, there is also Access Help's Table of Contents. This is impressively extensive, especially if it includes online help. You can just keep drilling. There are at least six layers. Now that's organized.

To get help from the Table of Contents, open Access Help and click Table of Contents. Under the Creating and Working with Databases and Objects folder, click Database objects, and then click Queries to display the Microsoft Jet SQL Reference folder. Keep going because there are still at least two more levels. It would take more than a day to study all the material just in this folder alone. Figure 3-8 shows the Table of Contents opened to display Help with Access functions.

It is important to note that the Microsoft Jet SQL Reference folder does not include the links to Data Access Object (DAO) reference topics and examples. It would be a little too confusing to have DAO and ActiveX Data Object (ADO) in the same folder. However, help with DAO is provided in a couple of other folders that are at the bottom of the Table of Contents. Obviously, it would be in the folder Microsoft DAO 3.60. And, it is also in the Microsoft Jet SQL Reference folder that is just below the DAO 3.60 folder.

3.2.6. Backup Database/Project

Finally, a consistent, fast, and easy way to save your work. We can't tell you how many times we've either closed and zipped a database, or copied and renamed a database object to test some changes. Pretty soon the database window is filled with nonfunctional objects. And heaven forbid that someone forgets if Name AutoCorrect was on, which can result in the code referring to the wrong object.

Again, the Access team felt the developer's pain and provided an awesome solution. You don't even have to worry about specifying where to save the file or giving the file a unique name. The backup command will automatically (1) default to the folder that contains the database and (2) give the file a unique name, based on the current file name with a suffix of the current date. And, if a backup already exists for that date, an incrementally numbered extension will be added to the date. Figure 3-9 shows the Save Backup As dialog window.

It only takes three mouse clicks to create a backup. Click File on the main menu and then click Backup Database. In the Save Backup window, click Save. You can also expend the energy for four mouse clicks if you take the path of Tools, Database Utilities, and then click Backup Database. And for the hot key fanatics, just use Alt+F+K+S. This feature is going to save so much time and reduce database bloat by eliminating unneeded objects. Similarly, using a wizard can save time.

3.2.7. Sorting Option on Lookup Wizard

In the past, many developers found the Combo BoxWizard to be a great tool for starting a combo box, but then needed to modify the SQL to accommodate additional fields, to include criteria or for any number of other reasons. Now the combo box, list box, and Lookup Wizards allow you to specify the sort order on up to four fields. Figure 3-10 shows the Combo BoxWizard sort order dialog box. Combo Box Wizard not only provides about as many options as the Report Wizard, but it also allows you to preview the data and drag the columns to their desired widths.

Figure 3-11 shows how easy it is to set the column widths.

Speaking of combo boxes and list boxes, in Access 2002 AddItem and RemoveItem methods were added. Now these work more like they do on Microsoft Visual Basic and Visual Basic for Applications (VBA) forms. The methods are available only when the RowSourceType property is set to Value List.

3.2.8. Copy and Paste Linked Table as Local Table

Most developers work with a lot of linked tables. Occasionally, it would be nice to also work with the table locally. Sometimes it is handy to use the local copy as a temporary worktable, to quickly crunch and manipulate data without putting the "real" data at risk and without being concerned about network connections.

You no longer have to go through the steps of file, import, and then browse to where ever the data is stored. Now, it is as easy as right-click the linked table, select copy, and then paste with or without the data. Figure 3-12 shows the Paste Table As window that you'll see when you are pasting a linked table. It is just copying and pasting a local table; it will not show the references to linked and local.

By right clicking a linked table, several more actions are listed, such as going directly to the Linked Table Manager or exporting the table. The shortcut menus put a lot of options at your fingertips. That covers several of the major new tools for working with tables and data. Given space and time constraints, the following section provides briefer descriptions of several other new features that deserve checking out. Again, these are only highlights and this is not intended to be an exhaustive list.

3.2.9. Notable Mention

There are countless other improvements that you will quickly notice. In fact, there are too many to mention. However, this section will quickly group and summarize some more noteworthy enhancements. This is heavily swayed toward features added in 2003. Since development starts with the table structure, it will be discussed first. The new file format was introduced in Access 2002. This file format has been maintained in Access 2003 and it is now called Access 2002–2003 file format. Access 2002 defaulted to save in the Access 2000 file format. And, it did not require files to be converted to 2002 except to make an MDE. As always, the file format has to match the application version to make an MDE, which means that you have to use the 2002–2003 file format to make an MDE using Access 2003 or Access 2002. Since one version of Access can open multiple file formations, the caption on the database window conveniently and prominently displays the current file format. Figure 3-13 also shows the caption for a 2003 database in the 2003 file format. Reviewing the options provided on the datasheet tab gives you a good idea about how much control you now have over the look and feel of the Access environment.

Forms and reports have such an extensive list of enhancements that all of them cannot be covered here. Obviously, PivotTable and PivotChart views are new. And it is important to know that the report format now allows levels to be expanded or collapsed. Plus, you can save forms and reports as Data Access Page (DAP). Saving to DAPs was added in 2002 and it was greatly improved with the enhanced XML features of 2003. Suffice it to say that working with Web pages is getting easier with each release. This is only fitting since the Internet is critical to our clients and therefore to developers. There are several other new properties and events for both forms and reports. Developers will love the multiple levels of undo and redo. In fact, there are so many new events for both forms and reports that you really just need to see them all in Appendix E, "The Access Object Model." You can also find out more about forms in Chapter10and about reports in Chapter 11. These chapters emphasize and demonstrate some of the really cool features, controls, tools, and events that are now available in forms and reports.

The Printer object and Printers collection make it easier to programmatically control printing. You can now use the Printer object and Printers collection to specify a specific printer, paper size, tray, and special features without having to open the report, select the printer, and save the report. This is covered in more detail in Appendix E.

Access Projects provide a way of connecting to SQL Server. There have been extensive upgrades and enhancements with regards to ADP and working with SQL Server or the Microsoft Development Environment. This list is indeed too long to enumerate, but let us pique your curiosity with updatable views, batch update as an Access form property, using disconnected recordsets for ADP objects, and even the ease of using the linked table manager to link to SQL Server; Chapter 17, "Understanding Client-Server Development with VBA," is devoted to working effectively with SQL Server.

Compact and repair has been combined into one command and it works better than ever at recovering broken forms and reports. Not only that, but it can even reduce a file size by as much as 70 percent. Talking about reducing file size is a reminder that the regular use of Compile and Save can go a long way toward preventing problems. So, when working in the VBE, make it a habit to click Debug, Compile at least hourly.

Missing references–Speaking of code, if references are missing, the new message will be more informative and actually tell you what file (reference) it cannot find. So, when you move or open a new database, it is easy to find out if it is necessary to update or correct references. It is so much nicer to fix references before trying to use the application. A lot of developers were frustrated with having to peck through references to figure out what was missing or to get them in the right priority. Thankfully, Access pretty much handles the priority issues. So, when a database is converted from an earlier version of Access, it does a pretty good job of automatically updating references to Microsoft components. There is more about this in Appendix B, "References for Projects."

Speech recognition was introduced in Access 2002 and was improved in 2003. It can be used for both commands and data input. It is not, however, available in Design View. But, who would be brave enough to talk the computer through placing controls on a form. Thinking back to school exercises when we had to tell someone how to draw something, it never came out looking like the original drawing. So, you can only imagine how a dictated form would look.

Multilingual support is provided for both text and graphics. We definitely have a global economy, so our applications need to support the international needs of our clients. Now, you can display multilingual text in tables, forms, and reports if the required fonts are installed. And the multilingual text can be preserved when the data is output to RTF, HTML, text, XML, and Excel file formats. Third party ad-ins are available to assist with extensive conversions into other languages.

In fact, special languages and fonts don't have to be installed for you to start benefiting from the multilingual features in Access. There are already several language dictionaries to choose from on the Spelling tab of the Options menu. And, the International tab allows changing the default cursor direction from moving left-to-right to moving right-to-left. It's pretty easy to see the International and Spelling tabs of the Option window.

Now, it is time to move on to learn about some of the tools that Access provides to do the work for you—specifically, the wizards, builders, and managers. Developers should not be taking these for granted.