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.