Create a database diagram of an MSSQL
Server 2000 database within an MS Access project. In this article, we create a
database diagram to meet a documentation requirement, the support of a data
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 Expression
Note: The majority of the procedures I
demonstrate in the series will be undertaken within MS Access 2003,
although the concepts that we explore in this article will apply to MS
Access 2002, and beyond. Along with MS Access, additional application
considerations apply for this tutorial, because it focuses upon activities that
are performed, within an MS Access Project file, in conjunction with MSSQL
For those joining the
series with this article, it is assumed that MSSQL Server 2000 is
accessible to / installed on your PC, with the appropriate access rights to the
MSSQL Server 2000 environment to parallel the steps of the article. Service
Pack 3 / 3a is also assumed. If this is the first time MSSQL Server 2000 is
being accessed from your machine, you may need to consult the MSSQL Server 2000
online documentation for installation and configuration instructions.
virtually any developer of a database knows, documentation of the structure
designed to contain data is a critical tool in many recurring scenarios, such
as operation and general maintenance. Users of databases, particularly those
who seek to extract data from them in an effective and efficient way, are often
distracted and frustrated from completing their objectives because they cannot
obtain even basic documentation, such as useful data dictionaries or current
database diagrams. In my work with some of the largest companies in the world,
who develop / purchase and operate databases that are often boggling to the
imagination in size and complexity, I am no longer surprised to find that one
of the most common issues in any business intelligence, OLAP, or other
development effort I undertake, is incomplete documentation. In many cases, I
find myself having to generate my own diagram, and, as crazy as it sounds, usually
have to explain to several onlookers why I need to undertake this exercise
instead of "just getting down to business."
opinion, one of the primary strengths of the MS Access project (.adp)
is its portable, user friendly documentation capabilities, as I will overview
in this and subsequent articles. This capability is useful whether one uses MS
Access as an RDBMS or not.
lion's share of my involvement with MS Access is comprised of migrating Access
databases to larger RDMS', "upsizing" the core components to MSSQL
Server and other platforms, where I often graft existing table designs into
new, more robust schemas. In addition to database upsizing, I have recently
begun "upsizing" MS Access reports to MSSQL Server Reporting Services
(see my Database Journal article "Upsize"
MS Access Reports to MS Reporting Services in this series) in various client
or training scenarios. Whatever the objective of the engagement, I find myself
quite often in those scenarios, to which I have already alluded, where no one
can produce a data dictionary (in some cases, it is "missing"; in
others, the individual with whom I am discussing it is not even aware of what
it is ...).
often need to generate a quick view of the database to facilitate development
or maintenance tasks, building a query / dataset within Reporting Services or
another enterprise reporting tool, performing a data-quality examination, and
for other activities. In times like these, and in other scenarios where a
quick database diagram can be efficient and useful, the MS Access project
(.adp) can be a great documentation tool.
article, we will explore using an MS-Access project for generating a database
diagram. We will:
hypothetical business need for a database diagram;
Create an MS
Access Project (.adp file) within which to perform the practice exercise;
connectivity with an MSSQL Server database;
database diagram, complete with table joins / relationship information;
navigation and methods that we encounter throughout our practice exercise;
of the diagram through MS Word and other options.
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
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.
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
an MS Access Project
first create an MS Access project from which to follow the procedures of the
Click Project using existing data
... in the New File task pane, as depicted in Illustration 1.
Illustration 1: Creating
a Project Using Existing Data
New Database dialog appears.
Navigate to an
acceptable location to house the new .adp file.
following into the File name box:
New Database dialog appears as shown in Illustration 2.
Illustration 2: Naming
and Placing the .adp File
Link Properties dialog appears, defaulted to the Connection tab.
Connectivity to the MSSQL Server Database
will establish connectivity with the pubs database in the following
Select / enter
the server name.
appropriate logon credentials settings (I chose Windows NT Integrated
Security, for purposes of this lesson).
Select the pubs
database on the server.
Link Properties dialog appears as depicted in Illustration 3.
Illustration 3: The
Completed Data Link Properties Dialog
Connection to ascertain connectivity.
Data Link message box appears, indicating a successful connection, as shown
in Illustration 4.
Illustration 4: The Data Link message box
to close the message box.
to accept the settings for, and to close, the Data Link Properties
.adp file is created, and the Database window appears as shown in Illustration
Illustration 5: The .adp
File is Created ...
created our project file and established connectivity to the pubs
database, we are now ready to begin our database diagram.
and Navigate a Database Diagram
are presently interested, for purposes of the hypothetical business need of our
practice example, in creating a diagram of an entire database, we have the
option at any time of generating such a diagram for any portion of a
database, as well.
Click the Database
Diagrams button under Objects, as depicted (circled) in Illustration
Illustration 6: Begin by
Clicking Database Diagrams ...
Database Diagram in Designer, appearing in the right pane, as shown
(circled) in Illustration 7.
Double-click Create Database Diagram in Designer ...
diagram design area opens, together with the Add Table dialog, as
depicted in Illustration 8.
Illustration 8: Diagram
Design Surface with Add Table Dialog
Click the Authors table,
and, holding the SHIFT key down, click the Titles table. This
should select all tables.
(we can also add tables individually, as the Add Tables dialog
conveniently remains in place until we dismiss it).
to close the Add Tables dialog.
tables appear on the design surface, as shown in Illustration 9.
Auto-Generated Diagram (Zoomed to 75%)
navigate around a bit and explore some of the options we have for working with
lists for each of the tables appear, with keys indicated by a small key icon. The
view of the tables we see is the Column Names view, which we can change
for any table / tables we wish to view differently, via the Table Modes
button on the toolbar, shown in Illustration 10.
Illustration 10: Table
Modes Button, in the Toolbar
Click the Table
Modes button to expand it, as depicted in Illustration 11.
Illustration 11: Table
Modes Button - Expanded, Showing Options
Modes button offers us several presentation options. We can choose a
minimal level of detail using the Name Only option, or successively add
more detail until reaching the Column Properties view, which presents
names of the tables selected, plus field names and properties, as well.
select the highest level of detail, since our diagram is intended to support
the creation of a data dictionary.
Ensure that no
one table is already selected (you can simple click any point in the "white
space" of the diagram design surface, to deselect any tables).
--> Select All from the main menu.
tables become highlighted / selected.
Click the Table
Properties from the expanded button.
tables in the diagram shift to the most detailed of the view options, showing table
name, and field names and properties, as partially depicted
in Illustration 12.
Illustration 12: We
Shift to Column Properties Mode (Partial View)
Property mode makes clear the need to be able to rearrange the tables for
the sake of clarity. All we need do is click on a given table and drag it into
position so that its joins are clear. We can use the Zoom feature (the
expanded button appears in Illustration 13) as a tool in this process,
as well as from the perspective of the final presentation.
Illustration 13: The Expanded
Zoom Modes Button
Click the Sales
table to select it.
Drag the table
to the right, well away from the other tables, to distinguish it.
Click the Zoom
Modes button to expand it.
within the menu that appears.
table appears, in reduced mode, as shown in Illustration 14.
Illustration 14: The Expanded
Zoom Modes Button
use the standard key combinations to select multiple tables (CTRL-click
and SHIFT-click), as well as "lassoing" our selections with
the mouse, much as we can do in many Windows-based graphical applications. We
can add further tables (not already in the diagram) by clicking the Add
Table button, which resurrects the Add Table dialog that we have
already encountered. We can also hide tables through the use of the Hide
Table button. Hide Table allows us to remove a given table from the
diagram without actually deleting it from the database (altering the database
from the diagram is possible, assuming the appropriate user privileges - always
exercise care with regard to unintentional modification).
Table and Hide Table buttons appear as depicted, left to right,
respectively, in Illustration 15.
Illustration 15: Add
Table and Hide Table Buttons, Left to Right, Respectively
case of the simple pubs database, the database diagram has
already managed the joins for us. Let's take a look behind the scenes to see
how joins are put in place, for those times down the road when the diagram will
not be able to conclude that a join is appropriate (say we have two fields that
should be joined, but which have different names entirely.)
see readily that the Titles table joins several other tables, and so we
will use it as an example in our exploration of the information behind the
the Titles table.
Properties from the context menu that appears, as shown in Illustration
Illustration 16: Right-Click
and Select Properties for the Titles Table ...
Select the Relationships
information for our example appears, as depicted in Illustration 17.
Illustration 17: The
Details behind the Join ...
here that we create, modify and remove joins. The relationship is given a default
name, which we can change; we need only define the tables involved, together
with the "direction" of the join, using the Primary key and Foreign
key fields in the middle section of the tab.
other settings are maintained here, as well, allowing us to dictate the join
behaviors that are appropriate to our business needs in the database. The "Enforce
relationship" checkboxes, both checked in our example, as shown above,
enforce referential integrity for the relationships under consideration.
Close the Properties
dialog when desired.
that we have a basic database diagram for our MSSQL Server database, let's
explore ways to deliver / store the information it contains.
Distribution Options for the Diagram
In addition to the
capability to print the diagram we have created (both via icon or menu item, as
in most Windows applications), we can save it using the Save As ... menu
item or icon to save our work with a new name. Let's save our diagram with the
--> Save As ... from the main menu.
As ... dialog appears.
following into the ... To box:
Save As ... dialog appears as shown in Illustration 18.
Illustration 18: The
Completed Save As ... Dialog
to save the diagram as specified.
enough, the diagram can be embedded in various MS Office applications' files,
as well, including Visio, Word, Excel, Outlook, FrontPage,
and others. We will demonstrate this with the following steps:
the diagram using the mouse, as shown (reduced) in Illustration 19.
Illustration 19: Select
the Database ...
NOTE: Selecting Edit --> Select All from the main menu will
accomplish the selection of the complete diagram, as well.
--> Copy to Clipboard from the main
Open a new
document in MS Word.
document at any point.
--> Paste from the main menu.
diagram appears in the MS Word document, ready for saving, e-mailing,
further documenting the database, etc.
capability to embed our new database diagram into other office documents
widens our distribution and publication capabilities immensely. Documentation
becomes a breeze with the availability of these features in an integrated MS
Office documentation effort.
Close the MS
Word document, saving it as desired, and return to the database diagram
in MS Access.
--> Close from the main menu, saving the
diagram if prompted.
return to the Database Diagrams list, where we see our new diagram
listed, where we can readily access it at a future date, as depicted in Illustration
Illustration 20: Our New
Diagram Appears in the Database Diagrams List
Close the MS
Access Project (.adp file) when desired.
article, we discussed potential uses for database diagrams in general,
and then established a hypothetical business need for such a diagram. We
established connectivity with our targeted MSSQL Server database, once we had
created an MS Access Project (.adp file) within which to perform
our practice exercise. We then began our creation of a database diagram.
our practice example, we noted the "automatic" nature of much of the
process, particularly in the case of a simple database like our target, the pubs
sample. We discussed join / relationship creation and maintenance, even
though the joins were created as part of the basic diagram generation within
our practice exercise. We then discussed navigation and various aspects of
managing our diagram, finally concluding with comments and practice surrounding
the distribution of our database diagrams within the MS Office application
See All Articles by Columnist William E. Pearson, III