Data Modeling with Access and Visio

  • Article

This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Scott McManus

The latest version of Visio—Visio 2002 for Access Database Developers—provides a data modeling and documentation tool for Access database developers. The most exciting features of Visio 2002 handle some of the most mundane (and perhaps neglected) parts of Access database development: documentation and design. Scott McManus is your guide.

Access developers who model data, or document their data structure, often have a two-step process:

  1. Model and document, using one set of software tools.
  2. Re-create the resulting data structure, using another tool (in our case, Microsoft Access).

Originally, Visio worked this way; data modeling in Visio was purely "drawing only." You could draw whatever you wanted, but those drawings had no direct connection to your development tools. But now connectivity between Visio models and Access databases makes Visio more than just a "dumb" drawing tool.

Working with a modeling tool

A useful modeling tool would allow you to generate your database from the model that you create. Many modeling tools do this by supporting, at the very least, the DDL (Database Design Language) portions of SQL. When you're finished drawing your data model, you can generate a DDL script that can be exported to a text file. If you want, you can then open that file and enhance it by manipulating its contents to create a mixture of SQL, DAO, or ADO code that will create a new database that reflects the design in your data model. In addition, your model provides excellent documentation about the structure of your database—at the time that it was created.

This is great when you're creating a database, but, unfortunately, that's not the end of the story. When business needs or data requirements change (and they will!), the database will need to be remodeled, re-documented, and re-created. If your data modeling tool doesn't support this, you're forced to begin again at Step 1: Model and document. If you avoid that step by bypassing the model, the resulting manual updates can lead to inconsistencies between the design, the documentation, and the database. These differences may only be slight (say, the names of attributes and entities), but when someone notices those discrepancies it looks untidy and unprofessional.

The latest version of Visio provides a data modeling and documentation tool for Access database developers. Visio seamlessly interfaces with Microsoft Access, removing the double and triple handling during the modeling, documenting, and implementation phases. Visio can not only generate a database from a data model, but it can also create a data model from an existing database. As a result, Visio increases your efficiency when updates are required.

When to use Visio

Before going any further, let me be clear about when you should consider using Visio. Visio will make your life considerably easier if any one of these conditions is true:

  • The client requires that all contracts that they enter into be fully specified. If you have a contract to provide a database for these kinds of clients, then you'll need to provide a data model and documentation in the "job quote" or as part of the contract documentation. This documentation provides a reference point to ensure that both parties are satisfied when contractual goals have been satisfied (as well as providing a set of billable milestones).
  • The client has a requirement to fully document all facets of their business to facilitate transparency and allow smooth transition of new employees. A data model that visually shows the database, coupled with documentation listing the entities and attributes with their data types, accompanied by a process flow model would meet that need.
  • The client, designer, and implementer are all separate parties, and communication of requirements needs to be clear, concise, and understandable by all.
  • The solution requires taking an existing Data Structure, possibly in a different Database Management System (DBMS), remodeling it, and then creating a new/modified data structure in Microsoft Access.

What can Visio do for me?

Basically, Visio supports two activities:

  • Reverse engineering an existing database to create a model.
  • Creating a new database based on a model.

When first starting a Visio project, you should decide which Visio Database stencil/template to use (see Figure 1). In Visio Professional you'll have only three of these templates, in addition to the Database Model Diagram:

  • ER Source Model
  • ORM Source Model
  • Express-G templates

The ER Source Model allows you to create a new model or reverse engineer an existing model. The ER model itself does not allow you to export to a new or existing database. To export a model, you'll need to use the Database Model template. With this template you can then update or export to an existing database as well as refresh an underlying ER Source Model.

Whether you're starting from scratch or reverse engineering, you should take time to set up some options to display the model in the format you require. So, after selecting the template to use, it's worth visiting the options available under the Database menu (see Figure 2). Probably the most important option available is the "Relationships" option. Your choice here will depend on your background and how you choose to model. If you leave the default setting, the relationship is shown with an arrow pointing to the table referred to by the relationship. My preference is for "Crow's feet" and relationships as shown in Figure 3.

In these figures I've used the ubiquitous Northwind.mdb to demonstrate Visio's relationship modeling conventions. The foreign key in the Orders table is displayed with the arrow pointing towards the "Shippers" entity. Figure 3 also shows the default options for displaying relationships, and primary and foreign key indexes.

Reverse engineering

When reverse engineering a database, you need to select the database driver for the database you want to extract information from. This is done using a Data Source Name. You must have the necessary security permissions to reverse engineer the selected database. For instance, you must be able to supply the Administrator user name and password. This is important! The user name you supply must own all objects that you select to import. If the user ID doesn't own those objects, you'll receive an error and Visio won't import any of the database structures—including any tables that you do have permission for. If someone has used several accounts to create the database objects, you'll need to use the Access Security wizard to reassign ownership to all of the objects.

Once the driver is selected and security is set up, it's a simple matter to import the tables, views, relationships, indexes, and keys into Visio. You have the option to display the entities (tables) immediately, or add them into the drawing area later as required.

You can now modify your design by adding tables, modifying tables, adding stored procedures or triggers, adding fields, changing data types, or changing the size of fields. The amount of material preserved through the import/export process is impressive. For instance, many Access developers use the Description property on many Access objects to document their designs. This is preserved in the Reverse Engineering and Export/Create new processes in Visio. A quick look at an imported database will show that any objects with a description assigned to them have that description copied to Visio's Notes field. The process also works in reverse. As an example, I selected the Shippers entity and added a field called Contact. In the table viewer/editor I added the note "Contact at shipping" (see Figure 4). Figure 5 shows a database created from the reverse engineered Northwind database with the updated Description property for the Contact attribute of the Shippers table.

Once you're happy with your new database design, you can check it for inconsistencies by running Visio's validation process. If you've let Visio know what the final destination DBMS is for your model during the modeling process, Visio will also apply rules specific to that DBMS.

Creating your database

Once you've changed the structure of the database and finished remodeling, you're ready to export the model. From the Database menu in Visio, it's a simple matter to update your ER Source Model and then either update an existing database or create a new database. If you 're updating an existing database with data in it, you'll need to take extra care to make sure that you don't lose any data.

When you're ready to export, you can export a DDL script that you can then execute in Access to create your database. Alternatively, you can also allow Visio to automatically create or modify your database.

Both the Standard and Professional versions of Visio 2002 can be obtained on a 30-day trial from the Microsoft Web site for the cost of postage (see the sidebar "Visio Versions" for more detail on what each version of Visio will do for you). To obtain demonstration versions, go to www.microsoft.com/office/visio (this is also the Microsoft Visio support site). The Enterprise Architect version is only available with Visual Studio .NET Enterprise Architect. I did most of the testing for this article with a trial version of Visio 2002 Professional (the rest was done using the Visual Studio .NET Enterprise Architect version).

The modeling and documentation tools in Visio 2002 aren't for every developer. Access does provide basic documentation tools (though they lack flexibility), and that may be enough for you. FMS provides the "next level" of documentation tools. However, if you re-engineer databases, transfer legacy databases into Access, or need to model your data before implementation, Visio provides an integrated tool that goes beyond documentation.

  • ****Visio 2002 Standard—No database tools. Designed as a drawing aid, useful for flowcharts, diagrams, maps, and organization charts. Can be automated from Access.
  • ****Visio 2002 Professional—Logical modeling of Relational and Object databases. Can reverse engineer a database from most RDBMS formats. Can update an existing model from a linked database.
  • ****Visio 2002 Enterprise Architect—Can export or create a new database based on a model. Can create DDL script. Can export code from the model into either VB.NET or C.NET.
  • A new "Visio viewer" has just been released that allows others to look at your drawings using Internet Explorer 5.x and above.

To find out more about Smart Access and Pinnacle Publishing, visit their website at http://www.pinpub.com/html/main.isx?sub=57

Note: This is not a Microsoft Corporation website. Microsoft is not responsible for its content.

This article is reproduced from the April 2003 issue of Smart Access. Copyright 2003, by Pinnacle Publishing, Inc., unless otherwise noted. All rights are reserved. Smart Access is an independently produced publication of Pinnacle Publishing, Inc. No part of this article may be used or reproduced in any fashion (except in brief quotations used in critical articles and reviews) without prior consent of Pinnacle Publishing, Inc. To contact Pinnacle Publishing, Inc., please call 1-800-788-1900.