MS Access for the Business Environment: MS Access as a Documentation Tool: Database Diagramming - Page 2
August 2, 2004
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.
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.
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.
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.