MS Access for the Business Environment: MS Access as a Documentation Tool: Display Object Dependencies

Tuesday Sep 7th 2004 by William Pearson
Share:

Support documentation of object dependencies within an MS Office Access 2003 database. Join Bill Pearson in a hands-on introduction to the use of the View Object Dependencies functionality in meeting an illustrative business need.

About the Series ...

This article is a member of the series MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see the first article of our series, Create a Calculated Field with the Expression Builder.

Note: The majority of the procedures I demonstrate in the series are undertaken within MS Office Access 2003, but are applicable to earlier versions of MS Access. However, the concepts that we explore in this article will apply to MS Office Access 2003 and beyond only, as the functionality we will explore is new in MS Office Access 2003.

Overview

In the last article of this series, MS Access as a Documentation Tool: Database Diagramming, we began an examination of the use of various MS Access features as documentation tools. We focused on meeting the important need of diagramming our databases for several reasons, including the support of a data dictionary as well as various enterprise reporting and other development efforts.

In this article, we will examine a feature that is new in MS Office Access 2003, and which offers great potential in our documentation, maintenance, and general upkeep efforts, from the perspective of the organizations MS Access databases. In MS Office Access 2003, we can directly and easily view information on dependencies between database objects. The capability to view a list of a database's objects, particularly within the context of how those objects relate to each other, can help us to maintain a database over time. The capability is perhaps most useful in helping us to avoid errors that emerge with the uninformed removal of record sources or other databases objects, but numerous other potential uses exist, as well.

Many of us are familiar with a scenario within which we have inherited a database environment that contains duplicate, non-working, or other objects of questionable utility. The reasons for the "object graveyard" are legion, but we can well understand that, for successful DBAs and developers, organization is certainly a virtue. The need to purge objects that are useless, or of unknown origin / vintage, is, in these cases, second only to the need to ascertain that they are, indeed, truly useless. To that end, the need to verify that dependencies do not exist between the valid objects and the suspect object(s) is critical.

We often need to generate a quick view of the dependencies among objects when we undertake such a housecleaning. With this, and other scenarios, in mind, Microsoft incorporated the capability to view object dependencies in MS Office Access 2003. In this article, we will explore using this capability for documentation and maintenance support purposes. We will:

  • Discuss our objectives within the article, and present a scenario upon which we will base our practice example;
  • Overview the View Object Dependencies capabilities in MS Office Access 2003;
  • Discuss required preparation for using the functionality;
  • Overview limitations and other considerations involved;
  • Perform a hands-on exercise wherein we illustrate the use of the View Object Dependencies functionality in supporting client documentation functions.

View Object Dependencies

Objective and Business Scenario

In the following section, we will perform the steps required to view object dependencies in MS Office Access 2003. Along the way, we will examine settings we need to consider to realize this capability, as well as the navigation and general use of the associated feature.

For purposes of our hands-on practice example, we will assume that a new project team, whose mission is to plan the upgrade of several MS Access applications to MSSQL Server 2000, has been formed within a client business, the Northwind Trading Company. As free-lance data-architects / database practitioners, we have been called upon to examine the primary MS Access database, Northwind.mdb, which was developed by an in-house team of professionals whose positions have recently been off shored. The remaining employees, representatives of Management (apparently the only segment of the worker population that can protect itself from such measures), are, unfortunately, unable to determine the object inventory, much less the relationships between those objects, within the database for documentation purposes.

In addition to upsizing the MS Access database to MSSQL Server, management wishes to convert the reports contained therein to MSSQL Server Reporting Services, to which the current enterprise reports of other reporting applications, such as Business Objects and Crystal, are also slated for rapid conversion. (For more information on upsizing MS Access reports to Reporting Services, see my article "Upsize" MS Access Reports to MS Reporting Services.) Management was surprised to learn that the potential six-figure savings that this move alone entailed could likely have helped avoid the need for such dramatic off shoring, and the natural consequences that followed. (As is so often the case, the arbitrary staff cuts, combined with the departure of several (now distrustful) key worker bees amid an improving economy, has left the managers "high and dry" with regard to implementation / migration ambitions).

We have been engaged to assist in the planning of the upsizing and conversion efforts, which we will likely be selected to accomplish after submitting our plan. Our work, in the meantime, will begin with supporting the documentation effort for the Northwind database, for which, we are told, no documentation is available. We set out to first create an inventory of the objects contained within the database prior to upsizing it to MSSQL Server, as part of preparation and general cleanup. In this article, we will focus on the next step, determining the dependencies among the object collection members, so that we can add this to our documentation efforts.

Considerations and Comments

For purposes of this exercise, we will be using MS Office Access 2003, in which the capability to view object dependencies first appears. We will focus upon the Northwind sample database, which is available for installation with MS Office Access 2003, as well as earlier versions of MS Access.

Few practitioners with any exposure to MS Access will be unfamiliar with the Northwind sample database. This database contains the sales data for a fictitious company called Northwind Traders, which imports and exports specialty foods from around the world. Northwind ships as a sample database with both MS Access and MSSQL Server. If you cannot find the Northwind.mdb, or know it to have been removed from your PC, for some reason, it is available from the original MS Office Access 2003 installation CD and elsewhere.

We will be accessing Northwind primarily to read, although a simple setting change may be necessary within the local database for readers to follow along in our practice example. We will discuss this at length in the Preparation section that follows, but it will be necessary for anyone who needs to make the setting change to have the authority / privileges associated with doing so.

Hands-On Procedure

Having been tasked with a need representative of many that occur in the business world, we will leverage the new MS Office Access 2003 option to view object dependencies. Within this capability, we will find that we can see an inventory list of the objects, consisting of tables, queries, reports and forms that exist within the database. Within the list, we can select a given object, and then have MS Access display a list of all objects that depend upon the object we have selected. In addition, we can produce a similar list that displays the objects upon which our selected object itself depends.

First, we will access the database and discuss the setting I mentioned earlier, in preparation for our hands-on practice with using the valuable new capability to view object dependencies in MS Access.

Preparation

Let's access the Northwind database and make any preparation necessary to satisfy the business need we have outlined above. We will begin by starting MS Access 2003 and taking the following steps:

1.  Go to the Start button on the PC, and then navigate to the Microsoft Access icon, as we did in Create a Calculated Field with the Expression Builder, the first article in my MS Access series.

2.  Click the icon to start MS Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> Open from the top menu, and navigate to the Northwind sample database. (The file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window.)

4.  Select Northwind.mdb.

We are greeted with the standard Security Warning dialog, depicted in Illustration 1, indicating that we are about to open a file containing potentially malicious code.


Illustration 1: Security Warning Dialog in MS Access 2003

5.  Click Open.

The splash screen may appear.

NOTE: As we have noted earlier in the series, we can preclude the appearance of the splash screen each time we enter the sample Northwind database by checking the "Don't show this screen again." checkbox. For now, we will leave it unchecked.

6.  Click OK.

The splash screen disappears, and is replaced by the Main Switchboard.

7.  Click the Display Database Window, shown circled in Illustration 2.


Illustration 2: Inside Access, Northwind Main Switchboard

We arrive at the Database Window, which appears as depicted in Illustration 3. Here we can view dependency information for many MS Access objects (we will discuss exclusions shortly) that are open, or that we select in the Database window.


Illustration 3: The Database Window

Before going further, we will need to ascertain the appropriate setting for a specific feature in MS Access that must be turned on before we can view object dependencies. That feature, used for tracking object name changes, is Name AutoCorrect, a feature added in MS Access 2000. The Name AutoCorrect feature must have at least its tracking component enabled before we can view object dependencies.

The primary function of Name AutoCorrect is to "monitor" changes made in the names of objects or fields, and to optionally make the associated change in any queries, reports or forms where the associated object appears. Name AutoCorrect does not perform this process with macros or procedures that reference the changed name; these must be modified manually anytime a referenced database object undergoes a name change.

When Name AutoCorrect tracking is enabled, MS Access generates and maintains a name map listing the objects that it monitors. The name map is updated anytime a change is made to the name of an underlying object. These updates trigger subsequent attempts to update all related references to the effected name. With a few limitations, the feature is generally effective in keeping the objects it monitors in sync.

We may not need or want MS Access to do more than track the objects in a given database, and so the option exists to leave the Perform Name Correct (the automatic attempt by MS Access to make changes to the references it determines to be misaligned based upon a change in the name of a corresponding object) disabled. In this case, MS Access will restrict its actions to updating the name map, without attempting to rename the related references that exist within the database.

It is because of its reliance upon the Name AutoCorrect tracking mechanism that viewing object dependencies cannot be accomplished without the tracking feature. This relationship also explains why we cannot view dependency information for objects that are not supported by the Name AutoCorrect feature, including the following object types:

  • MS Access Projects
  • Macros and Modules within the Database
  • Action Queries
  • SQL-Specific Queries
  • Union Queries
  • Data Definition Queries
  • Pass-through Queries
  • Subqueries

Other potential limitations of the capability to view object dependencies include the fact that hidden objects will not be listed unless the Hidden Objects option on the View tab in the Options dialog box is selected, as might be expected. In addition, if the user of the functionality does not have permissions to access the Design view of a given object, then dependency information surrounding that object will be, predictably, unavailable to that user.

Nested queries are partially supported, in that the capability to view object dependencies extends to the outermost query, and not beyond. Finally, according to the online documentation, the source tables and queries of a query's subdatasheet and lookup fields do not appear in the dependency view as objects upon which the query depends.

Let's check our settings, in preparation for viewing object dependencies, so we can get started with helping our client meet the needs it has expressed.

1.  Select Tools --> Options from the main menu, as shown in Illustration 4.


Illustration 4: Select Tools --> Options ...

The multi-tabbed Options dialog opens.

2.  Click the General tab, if necessary, to select it.

Here we can see the status of the Track Name AutoCorrect info setting. While it is likely that the feature is enabled in the sample Northwind database, it might not be in a database that we actually inherit from past developers / users in our organization. Before making this step in the real world, a little thought should be given to other considerations, perhaps, but, in general, simply tracking (versus performing auto-fixes of) AutoCorrect information is not likely to mean undue risk.

3.  Click the check box to the immediate left of Track name AutoCorrect info, to check the box, if it is unchecked.

The Options dialog - General tab, with our setting (and, in this case, the Perform name AutoCorrect setting as it comes "out of the box," for the Northwind sample database), appears as shown in Illustration 5.


Illustration 5: Options Dialog - General Tab with Settings

If the checkmark is already in place (as it likely is), the name map we discussed earlier already exists. In a case where we are enabling the feature from an "unchecked" status, MS Access will build the name map as soon as we click OK.

4.  Click OK to accept changes (if any were, indeed, made) and to close the Options dialog.

We return to the Database window. As we have ascertained that MS Access has Track Name AutoCorrect enabled, we will proceed directly to viewing object dependencies.

Procedure

Our mission is to determine the dependencies of the objects in the database, assuming we have assembled an inventory of those objects already. As we have discussed already, MS Office Access 2003, with the Name AutoCorrect feature enabled, allows us to see a list of objects that depend upon a selected object, as well as objects upon which the selectee itself depends.

5.  Click the Products table in the Database window to select it.

6.  Select View --> Object Dependencies from the main menu, as depicted in Illustration 6.

NOTE: We can also right-click an object and select Object Dependencies from the context menu that appears to achieve the same objective.


Illustration 6: Select View --> Object Dependencies ...

The Object Dependencies task pane appears, as shown in Illustration 7. Atop the pane is the name of the object under examination (circled in red in the illustration).


Illustration 7: The Object Dependencies Task Pane - Selected for the Product Table

The Object Dependencies pane initially shows the list of objects that rely upon the Product table. (The radio button to the left of Objects that depend on me is selected at first, atop the pane).

We can click the expand icon (+) to the immediate left of each object listed in the pane to expose the associated dependency details.

7.  Expand the Order Details Extended query, listed in the Queries section in the Dependencies pane, by clicking the "+" sign to its left.

The Order Details Extended query listing expands, as depicted in Illustration 8.


Illustration 8: The Order Details Query Object - Expanded View

We can see that, within the Order Details Extended query, the Sales by Category query, together with the Sales by Category report and subreport, are identified as dependent upon the Products table.

Once we have collected the information surrounding the objects that are dependent upon the Products table, we can easily switch to a view of objects upon which the Products table is, in turn, dependent, by clicking the radio button to the left of Objects that I depend on at the top of the pane.

8.  Select the Objects that I depend on option, by clicking the radio button to its left, atop the Object Dependencies pane.

The Object Dependencies pane shifts to present the objects upon which the Products table is dependent, as shown in Illustration 9.


Illustration 9: Selecting Objects that I Depend On Atop the Object Dependencies Pane



Within either view of dependency, we can take advantage of the expanded details we noted above. In addition, we can "drill" to the granular specifics of the dependencies in many cases (up to four levels deep, where applicable), as we shall see in our next step.



9.  Expand the Order Details table, listed in the Tables section in the Dependencies pane, by clicking the "+" sign to its left.



10.  Expand the Orders table, appearing underneath the newly expanded Order Details table, by clicking the "+" sign to its left.



The expanded tables appear as depicted in Illustration 10.


Illustration 10: Expanding a Couple of Levels in the Object Dependencies Pane

11.  Click the Table: Products entry beneath the Order Details table, to select it.

The table appears, in Design view, to the left of the pane, as shown in Illustration 11 (just as it would if selected from the Database window).


Illustration 11: The Dependent Table Appears, Providing More Details for Documentation

The view indicates the field in the table that underlies the respective dependency listing, providing yet another level of detail for our documentation of the object dependencies within the database.

Using the Objects that depend on me and the Objects that I depend on selections within the Dependencies pane, we are able to make a note, in our objects inventory list, of the dependencies surrounding the Products table. Furthermore, we can easily identify objects with no dependencies as "candidates for extinction" before upgrading the database to MSSQL Server; the responsible client team will decide the appropriate course of action based upon the input that we are thus able to provide.

12.  Select the Objects that depend on me option, by clicking the radio button to its left, atop the Object Dependencies pane.

We return to the view of the objects that are dependent upon the Products table.

At the bottom of the Dependencies pane, as partially depicted in Illustration 13, we notice that the following phrase appears:

Warning:  Some objects were ignored

In addition, a folder titled Ignored Objects appears in the tree in the Dependency pane, just above the Warning message, as depicted in Illustration 12.


Illustration 12: Returning to the Objects That Depend on Me View Option

As we intimated earlier, MS Office Access 2003 ignores objects for a couple of reasons. First, if the objects under consideration are outside the scope of supported items, such as SQL-specific queries (and other objects we discussed above); MS Access will exclude them from the Dependency view. These objects do not support Name Autocorrect, as we saw earlier, and therefore do not update the name map that underlies our capability to view object dependencies.

Moreover, we mentioned earlier that a user attempting to view object dependencies needs to have permission to open the object under consideration in Design view. The reason for this is that MS Office Access 2003 must be able to open, close and save a given object in order to generate dependency information surrounding that object.

We can see a ready example of objects that are ignored due to the former reason, lack of support, by taking the next step.

13.  Expand the Ignored Objects folder, which appears just above the Warning message, by clicking the "+" sign to its left.

14.  Expand the Unsupported Objects folder that appears next, just below the Ignored Objects folder.

The folder expands, and we see a couple of unsupported objects listed, as shown in Illustration 13 (just as it would if selected from the Database window).


Illustration 13: Example Unsupported Objects, Appearing in the Ignored Objects Folder

Ignored Objects are grouped, by reason for their classification as "ignored." The Ignored Objects folder above contains only ignored objects of the unsupported class, as we have universal permissions in the sample database.

15.  Close the Northwind database when ready.

16.  Exit MS Access when desired.

Conclusion...

In this article, we discussed another feature, new in MS Office Access 2003, which assists us in supporting database documentation. We established a scenario within which we are engaged to assist a client in meeting a hypothetical business need to document the dependencies of the objects in a database. The database has been inherited, without documentation, from departed designers within the organization, and management now seeks to know more about the objects within the database. We discussed illustrative uses for object dependency information, both within the scope of our example scenario and in general, and then introduced the View Object Dependencies capability that MS Office Access 2003 makes available.

We discussed required settings that must be enacted for the View Object Dependencies functionality to be available, commenting on the reasons that the settings are mandated as they are. We then used the features within the View Object Dependencies functionality, examining navigation, options, and limitations in a manner to activate the concepts we had discussed in a memorable way.

» See All Articles by Columnist William E. Pearson, III

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved