Annoyances Alley is a tiny glimpse into the Annoyances series of Office 97 related books from Woody Leonhard, Lee Hudspeth, and T.J. Lee.
REVIEWER'S CORNER... "The Annoyances books are dense, only a bit over 300 pages each but packed with an enormous amount of useable information to help you get Microsoft Office do what you want it to do." -- Bill Blinn, Technology Editor, Newsradio 610 WTVN, reviewing the entire Office Annoyances book series
This is just one of the detailed tips related to optimization and customization that appears in "Office 97 Annoyances" published by O'Reilly & Associates (ISBN 1-56592-310-3) and reprinted here with permission.
"Query Wizards
Access has always had an easy-to-use query interface, letting
users make joins by dragging a field from one table to another,
and creating SQL statements in the graphical query grid. (A
"join" is where you associate a field in one table with a field
of the same (or compatible) data type in another table. This
determines how your data is related. A record with CustomerID 996
could be related to all purchase order records where the
CustomerID is 996.) And this method is still the most flexible
and the fastest once you're familiar with how queries work. (For
more on queries and those that are not supported by Access' drag-
and-drop query interface see "Access Database Design &
Programming" by Steven Roman, O'Reilly, ISBN 1-56592-297-2.)
http://www.amazon.com/exec/obidos/ASIN/
1565922972/tnpcnewsletter/
But those of us who don't think of SQL as their first language can use any of several Query Wizards which make it a snap to find things like unmatched or duplicate records in a table. There's a Wizard that will walk you through cross-tabulations and one that will help you create simple queries to extract subsets of data from tables.
Click the Query tab and click on New. The New Query dialog box appears, and you select the Wizard you want. See Figure 5-9.
From there you just make selections, and the query is created for you. In Figure 5-10, you see the Simple Query Wizard that allows you to select the table to query and the fields you want to use. The Wizards in Access are very well-bred and polished.
For the experienced database user, the process of working through the screens in a Wizard can take longer than doing things manually. But that is one of the most outstanding aspects of Access: there is plenty of power available to the database guru, and this power is carefully made accessible to the casual user.
Hyperlinks in Access
Since we're talking about Office 97, you know it's only a matter of time before you run into the wowie-pow-zowie Internet connectivity features again. All the Office applications sport new Internet features. Some of them are actually useful, some are just annoying. Hyperlinks are a great example of both.
Hyperlinks in Access 97 manifest themselves as a new data type called, simply enough, Hyperlink. You can store URLs, UNC paths, or paths to local files, as shown in Table 5-2.
Valid Hyperlink Examples
URL (Universal Resource Locator):
http://www.primeconsulting.com
UNC (Universal Naming Convention) path:
\\ripley\ripley_c_drive
Local path:
c:\data\books\oa\oa4\chapter4.doc
Table 5-2: A hyperlink by any other name
Click on a hyperlink and wonderful things can happen. If you click on a URL, your favorite browser is triggered and off Web surfing you go. Click a path to a local document and that document is fired up. Great.
The problem (and you knew one was coming, didn't you?) is this: Access is effectively brain-dead about what makes a good link. Okay, Excel is nearly as bad, but in Excel you pretty much have to enter a hyperlink using the Insert Hyperlink dialog box, which gives you a fair chance of browsing for the link or at least being awake enough to type carefully (see Figure 5-11).
In Access, though, even before you can use the Insert Hyperlink command, you have to set the field type to Hyperlink (see Figure 5-12). Once you do that, any text entered into that field (or text that is already there) is considered a valid link. URL, path, or street address -- it makes no difference.
Click on that field and Access tries to initiate a link. Needless to say, without a valid link your computer will waste cycles as it fires up your browser and tries to connect to a bogus reference. Be sure to enter the right information when you set up a Hyperlink field in Access.
Another major annoyance is that with Hyperlink fields containing URLs, Access opens a new instance of your browser every time you click a link. You can quickly wind up with multiple instances of Internet Explorer or Netscape running, which takes a toll on system resources.
Developers find the Hyperlink Column option on the Insert menu to be annoying (along with the Insert Column command to a somewhat lesser extent). You'd think that modifications to the structure of a database would not be allowed in anything other than Design View, but you can insert a Hyperlink Column into a table in Datasheet View. What's more, if you are in Design View and you add a column (field), your changes are automatically saved to the database. But make an addition to the field structure in Design View and then try to switch to Datasheet View and you are told you must first save the table (see Figure 5-13). Inconsistent, peculiar, and very annoying."
