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.