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 ...
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
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.
last article of this series,
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
Discuss our objectives
within the article, and present a scenario upon which we will base our practice
Overview the View
Object Dependencies capabilities in MS Office Access 2003;
required preparation for using the functionality;
limitations and other considerations involved;
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
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
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
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.
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.
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:
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.
Click the icon
to start MS Access.
Access opens, and may display the initial dialog. If so, close it.
-> 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.)
greeted with the standard Security Warning dialog, depicted in Illustration
1, indicating that we are about to open a file containing potentially
Illustration 1: Security
Warning Dialog in MS Access 2003
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.
splash screen disappears, and is replaced by the Main Switchboard.
Click the Display
Database Window, shown circled in Illustration 2.
Illustration 2: Inside
Access, Northwind Main Switchboard
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
Illustration 3: The
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.
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.
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.
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.
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:
Modules within the Database
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
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.
check our settings, in preparation for viewing object dependencies, so we can
get started with helping our client meet the needs it has expressed.
--> Options from the main menu, as shown in Illustration
Illustration 4: Select
Tools --> Options ...
multi-tabbed Options dialog opens.
Click the General
tab, if necessary, to select it.
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.
check box to the immediate left of Track name AutoCorrect info,
to check the box, if it is unchecked.
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
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.
Click OK to
accept changes (if any were, indeed, made) and to close the Options dialog.
to the Database window. As we have ascertained that MS Access has Track
Name AutoCorrect enabled, we will proceed directly to viewing object
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.
Click the Products
table in the Database window to select it.
--> 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 ...
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 7: The
Object Dependencies Task Pane - Selected for the Product Table
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).
click the expand icon (+) to the immediate left of each object listed in
the pane to expose the associated dependency details.
Expand the Order
Details Extended query, listed in the Queries section in the Dependencies
pane, by clicking the "+" sign to its left.
Details Extended query listing expands, as depicted in Illustration 8.
Illustration 8: The Order
Details Query Object - Expanded View
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.
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.
Select the Objects
that I depend on option, by clicking the radio button to its left, atop the
Object Dependencies pane.
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
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.
Expand the Order
Details table, listed in the Tables section in the Dependencies
pane, by clicking the "+" sign to its left.
Expand the Orders
table, appearing underneath the newly expanded Order Details table, by
clicking the "+" sign to its left.
tables appear as depicted in Illustration 10.
Illustration 10: Expanding
a Couple of Levels in the Object Dependencies Pane
Click the Table:
Products entry beneath the Order Details table, to select it.
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
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.
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.
Select the Objects
that depend on me option, by clicking the radio button to its left,
atop the Object Dependencies
We return to the view of the
objects that are dependent upon the Products table.
bottom of the Dependencies pane, as partially depicted in Illustration
13, we notice that the following phrase appears:
Warning: Some objects were ignored
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
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.
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.
see a ready example of objects that are ignored due to the former reason, lack
of support, by taking the next step.
Expand the Ignored
Objects folder, which appears just above the Warning message, by
clicking the "+" sign to its left.
Expand the Unsupported
Objects folder that appears next, just below the Ignored Objects
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
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.
Close the Northwind
database when ready.
Exit MS Access
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.
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
See All Articles by Columnist William E. Pearson, III