MS Access for the Business Environment: MS Access as a Documentation Tool: Database Diagramming

Monday Aug 2nd 2004 by William Pearson

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 dictionary.

About the Series ...

This 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 Builder.

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 Server 2000.

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.


As 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."

In my 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.

The 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 ...).

I therefore 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.

In this article, we will explore using an MS-Access project for generating a database diagram. We will:

  • Establish a hypothetical business need for a database diagram;
  • Create an MS Access Project (.adp file) within which to perform the practice exercise;
  • Establish connectivity with an MSSQL Server database;
  • Create a database diagram, complete with table joins / relationship information;
  • Explain navigation and methods that we encounter throughout our practice exercise;
  • Explore distribution 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 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:


The File New Database dialog appears as shown in Illustration 2.

Illustration 2: Naming and Placing the .adp File

6.  Click Create.

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

4.  Click Test Connection to ascertain connectivity.

The Microsoft Data Link message box appears, indicating a successful connection, as shown in Illustration 4.

Illustration 4: The Data Link message box

5.  Click OK to close the message box.

6.  Click OK to accept the settings for, and to close, the Data Link Properties dialog.

The .adp file is created, and the Database window appears as shown in Illustration 5.

Illustration 5: The .adp File is Created ...

Having created our project file and established connectivity to the pubs database, we are now ready to begin our database diagram.

Create and Navigate a Database Diagram

While we 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.

1.  Click the Database Diagrams button under Objects, as depicted (circled) in Illustration 6.

Illustration 6: Begin by Clicking Database Diagrams ...

2.  Double-click Create Database Diagram in Designer, appearing in the right pane, as shown (circled) in Illustration 7.

Illustration 7: Double-click Create Database Diagram in Designer ...

The diagram design area opens, together with the Add Table dialog, as depicted in Illustration 8.

Illustration 8: Diagram Design Surface with Add Table Dialog

3.  Click the Authors table, and, holding the SHIFT key down, click the Titles table. This should select all tables.

4.  Click Add (we can also add tables individually, as the Add Tables dialog conveniently remains in place until we dismiss it).

5.  Click Close to close the Add Tables dialog.

The tables appear on the design surface, as shown in Illustration 9.

Illustration 9: Auto-Generated Diagram (Zoomed to 75%)

Let's navigate around a bit and explore some of the options we have for working with the diagram.

Field 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

6.  Click the Table Modes button to expand it, as depicted in Illustration 11.

Illustration 11: Table Modes Button - Expanded, Showing Options

The Table 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.

Let's select the highest level of detail, since our diagram is intended to support the creation of a data dictionary.

7.  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).

8.  Select Edit --> Select All from the main menu.

All tables become highlighted / selected.

9.  Click the Table Modes button.

10.  Click Column Properties from the expanded button.

The 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)

The Column 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

11.  Click the Sales table to select it.

12.  Drag the table to the right, well away from the other tables, to distinguish it.

13.  Click the Zoom Modes button to expand it.

14.  Select 50% within the menu that appears.

The table appears, in reduced mode, as shown in Illustration 14.

Illustration 14: The Expanded Zoom Modes Button

We can 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).

The Add 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

In the 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.)

We can 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 joins.

15.  Right-click the Titles table.

16.  Select Properties from the context menu that appears, as shown in Illustration 16.

Illustration 16: Right-Click and Select Properties for the Titles Table ...

The Properties dialog appears.

17.  Select the Relationships tab.

The Relationship information for our example appears, as depicted in Illustration 17.

Illustration 17: The Details behind the Join ...

It is 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.

Several 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.

18.  Close the Properties dialog when desired.

Now that we have a basic database diagram for our MSSQL Server database, let's explore ways to deliver / store the information it contains.

Consider 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 following steps.

1.  Select File --> Save As ... from the main menu.

The Save As ... dialog appears.

2.  Type the following into the ... To box:


The completed Save As ... dialog appears as shown in Illustration 18.

Illustration 18: The Completed Save As ... Dialog

3.  Click OK to save the diagram as specified.

Conveniently 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:

4.  "Lasso" 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.

5.  Select Edit --> Copy to Clipboard from the main menu.

6.  Open a new document in MS Word.

7.  Click the document at any point.

8.  Select Edit --> Paste from the main menu.

The database diagram appears in the MS Word document, ready for saving, e-mailing, further documenting the database, etc.

The 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.

9.  Close the MS Word document, saving it as desired, and return to the database diagram in MS Access.

10.  Select File --> Close from the main menu, saving the diagram if prompted.

We 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 20.

Illustration 20: Our New Diagram Appears in the Database Diagrams List

11.  Close the MS Access Project (.adp file) when desired.

Conclusion ...

In this 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.

Within 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 family.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved