Danny J. Lesandrini
Sometimes developers get excited about the simplest things. This month, Danny Lesandrini relates his excitement over a new donationware tool for navigating around Microsoft Access query dependencies and references.
The weather in Denver has been great. So great that I was having difficulty getting motivated to write my monthly column. What drew me back inside was a note from Peter Vogel, suggesting I look at Russell Sinclair's latest contribution to the Microsoft Access community—a query dependency and object reference analysis utility.
A query dependency tool lets you know what impact changing something in your database will have on the queries in your application. I know what you're thinking, "Do I need this?" or "Doesn't Access do that?" That's what I thought when I first heard about the tool, but I was intrigued by the claim that Russell's creation was integrated into the Access user interface. In other words, the tool doesn't appear as an Add-In (like most third-party tools), but becomes part of the native Access menus—making the tool very convenient to use.
The tool is relatively simple and doesn't have a lot of features. Or, to put it another way, it does one thing and does it well.
At the end of the article, you'll find instructions for downloading your free copy of the Query Dependencies tool. The download isn't trivial in size, weighing in at around 2.1MB. My version came as a Zip file that I extracted to a temporary folder. The setup wizard will walk you through the simple installation. If you have Access open, you'll have to close and reopen it to see the new menu options (see Figure 1).
Once the tool is installed, you'll notice two new menu options in a couple of places. Figure 1 shows the Dependencies and References menu options on the right-click context menu for a selected query. These right-click menu options are also available when selecting a table object, and they show up on the Query menu when viewing a query in design view.
As I mentioned, there's no Add-In file to manage or register. The tool's functionality is handled through a library component, QueryDependencies.dll. The tool's user interface is implemented through a set of multiple Visual Basic forms that are created as child forms of the Microsoft Access environment. As a result, the windows behavior is... quirky. I noticed, for instance, that the form's title bar is displayed correctly initially, but appears to become deactivated when the form gets the focus. Russell told me this is going to remain a known issue, as it's a limitation of the way that VB forms interact with other programs when you make them child forms of another MDI application. Fortunately, this is merely cosmetic and doesn't affect the product's usefulness.
Getting down to business
The utility displays two different sets of information:
• Dependencies shows the hierarchy of queries and tables that make up the currently selected query (see Figure 2).
• References shows all queries that reference the current table or query (see Figure 3).
Either or both of these windows may be open at any time. You can also have them open for as many different queries or tables as you'd like.
Figure 2 + Figure 3
The only restriction is that Table Objects don't display dependencies, since they have only one dependent object (themselves). Tables do, however, have a references collection, which are all those queries that reference them. This is the only criticism that I'd level against this utility. For tables, the dependency menu option should be disabled. If you can't view dependencies of a table (since there are none), you shouldn't be given the option to do so. Clicking it, only to have nothing happen, made me feel like there was something wrong with the program.
The toolbars for the Dependencies and References windows are nearly identical. The name of the selected object (query or table) shows in the title bar, along with the "Query Dependencies" or "Query References." Users have the option to select an item in the window and view it in either design or datasheet mode. Another toolbar button allows you to display the dependencies or references for any selected item—depending on which window you're in. This lets you navigate through a hierarchy of query dependencies, all the way back to the underlying tables. The last toolbar button copies the contents of the window as a text representation of the window's tree view control.
Why do you care?
Okay, so that's how it works, but why is this tool valuable? Consider this example: A client recently asked me, "How hard would it be to add two additional status values to our Project Status table? Currently, project statuses are 'In Planning,' 'Started,' and 'Finished.' We want to add 'Cancelled' and 'On Hold.'" Though this may sound simple, numerous report queries are hard-coded to filter for various combinations of ProjectStatusIDs. To be honest, I couldn't give an accurate answer on how long it would take, as I had no idea how many queries I was going to have to review and edit. With the Query Dependencies tool installed, all I had to do was right-click the Project Status table, select References, and count the results.
I quickly discovered that there were 23 queries that reference the Project Status table. Allowing five minutes to review and edit each query, I figured it would take about two hours to make the change—which ended up being a very accurate estimate. In addition, since the Query Dependencies window allowed me to launch any query in design mode, the Dependencies window served as a filtered list of queries, which I worked through from top to bottom. I found this method of working to be very, very convenient. I think you will too.
Getting your copy
Russell's company is offering the Questica Query Dependency tool as donationware. The tool is free, and people are encouraged to make donations if they like it and want to contribute to the development efforts. To get your free copy, go to the Questica, Inc., Web site at www.questica.com/services/QueryDependencies.asp.
As I mentioned earlier, the author of this splendid utility is someone readers of Smart Access will recognize—Russell Sinclair. After sending me the product, Russell asked me to play around with it and e-mail him with any questions or issues. I'm sure he'd love to get some feedback from the Access user community too, so give it a try and let the people at Questica know how you liked it.