Diagram Your MS Access Database
Objective and Business Scenario
In the following
section, we will perform the steps required to diagram an MSSQL Server database
using an MS Access project. Along the way, we will examine the various
connections and settings that we need to establish to bring this about, as well
as the navigation and general use of the diagramming interface that MS Access
provides within a Project. Finally, we will discuss possible delivery options
for the diagram once we have completed it.
For purposes of our
practice procedure, we will assume that the information consumers in our
business, a book publishing concern, have asked that we assist in planning a
business intelligence engagement, which will include both relational and OLAP
reporting. As part of the planning evolution, we have advised that the team assemble
several references for the report authors, who are due to arrive on site in a
couple of days. One of the primary references we have listed is a data
dictionary for the organization's main OLTP database, pubs.
Our experience has
been that a database diagram is a great start for a data dictionary, in
that it displays all the objects in a way that helps us to easily see the
tables, their member columns, and the relationships between those tables. This
is particularly useful to a report author (something, again, I am astounded to
find is not offered as a standard assumption in many of the reporting
engagements I have observed in action at various client sites). As we do not
have MS Visio, or any other of the tools that are often used specifically for
this purpose, available at present, we reject someone's suggestion that we "draw
it out," and propose the use of MS Access, part of the existing Office
2003 applications we find on the PC's where we are working. We ask for access
to the client database, simply to "read" it briefly to generate our
diagram.
Considerations and Comments
For purposes of this
exercise, we will be using MS Access in conjunction with MSSQL Server
2000, focusing upon the pubs database in MSSQL Server. Few
practitioners with any exposure to MSSQL Server will be unfamiliar with the pubs
database, which contains data modeled on a hypothetical book publishing
company; Pubs is a very basic sample database that is available with all
versions of MSSQL Server. If you do not see pubs listed in Enterprise
Manager, or for some reason know it to have been removed from your PC, it is
available from the original MSSQL Server installation disk and elsewhere.
We will be
accessing pubs in MSSQL Server using an MS Access project (.adp
file). An MS Access project is a data file that provides efficient,
native-mode access to a Microsoft SQL Server database via OLE DB, used
typically to develop both traditional and Web-based client / server
applications, among other things. We are using it in meeting the hypothetical
client business requirement because it provides easy access (to a local SQL
Server database, a remote SQL Server database, or a local installation of SQL
Server 2000 Desktop engine), within a fairly ubiquitous application (MS Access)
that comes equipped with a reliable diagramming tool we can master quickly, and
for which we have flexible output options.
While our interaction
with MSSQL Server will be quite minimal for purposes of this article, we will
need the authority, access and privileges to access the pubs database,
and to establish connectivity between it and MS Access.
Hands-On Procedure
An MS Access
project (.adp), as we have stated, is a file that connects to an MSSQL
Server database, providing an excellent platform from which to design client /
server applications. It works well in an environment where some or all
development itself is taking place on a client, with the MSSQL Server
database under consideration located on a server to which we can establish
connectivity. An MS Access project differs from an MS Access database,
because it contains database objects that are code- or HTML- based. We typically
use the project objects to create an application; it does not store data or
data definition based objects, such as tables, views, and so forth. In our
scenario, the "real" database objects will exist in MSSQL Server,
where we are really only reading them enough to generate a diagram of the
database.
Create
an MS Access Project
Let's
first create an MS Access project from which to follow the procedures of the
practice exercise.
1.
Open MS
Access.
2.
Select File
--> New
3.
Click Project using existing data
... in the New File task pane, as depicted in Illustration 1.
Illustration 1: Creating
a Project Using Existing Data
The File
New Database dialog appears.
4.
Navigate to an
acceptable location to house the new .adp file.
5.
Type the
following into the File name box:
DBDocumentation
The File
New Database dialog appears as shown in Illustration 2.
Illustration 2: Naming
and Placing the .adp File
The Data
Link Properties dialog appears, defaulted to the Connection tab.
Establish
Connectivity to the MSSQL Server Database
We
will establish connectivity with the pubs database in the following
steps:
1.
Select / enter
the server name.
2.
Choose
appropriate logon credentials settings (I chose Windows NT Integrated
Security, for purposes of this lesson).
3.
Select the pubs
database on the server.
The Data
Link Properties dialog appears as depicted in Illustration 3.
Illustration 3: The
Completed Data Link Properties Dialog
» 
Comment and Contribute
