Visio Professional makes it easy to extract information from a DBMS and add it to the drawing page. The Reverse Engineering Wizard walks you through the steps required to connect to a data source, specify a DSN, connect to a target database, and select the objects to extract from the database. Visio can create a diagram automatically based on the objects you extracted, or you can create the diagram yourself. The wizard analyzes and validates the schema before adding it to the database model diagram in Visio.
The information that the wizard can extract depends on your target DBMS. If the DBMS doesn't make all of the schema definition available, the wizard displays options for the available items and dims other options. After you finish using the wizard, its status is displayed in the Output window and the extracted schema is displayed in the Tables And Views window, Code window, and the drawing page if you choose.
You can use the Reverse Engineer Wizard to connect to a target DBMS, but it's quicker to configure a default driver first. To connect to and extract information from a database, Visio uses built-in database drivers that are enhanced to work specifically with the Database Model Diagram template. Visio includes built-in drivers for the following DBMSs:
In addition, a generic OLE DB Provider and ODBC driver are included. If you are developing a database for a DBMS other than the ones listed here, you can use the generic ODBC driver with a vendor-supplied 32-bit ODBC driver to connect to your DBMS.
Using the Drivers command, you can configure a driver in advance to specify default data type properties and a default data type to use when creating columns, as Figure 19-7 shows.
Figure 19-7. You can set up and select a database driver before using the Reverse Engineer Wizard.
Follow these steps to specify a default database driver:
When you start the Reverse Engineer Wizard, you can create a data source for a particular database. A data source includes the data you want to access and crucial information for accessing that data, such as the name of the database, the server on which it resides, and network information. Visio refers to a data source by its data source name (DSN) and lists default DSNs on the first screen of the wizard, as Figure 19-8 shows. You can create, edit, and manage DSNs within Visio.
Follow these steps to start the wizard and connect to a data source:
Figure 19-8. You can connect to a database by specifying a Visio driver and then an associated data source.
On the next screen of the Reverse Engineer Wizard, you can choose the schema information to extract, as Figure 19-9 shows. Select the options you want, and clear the check boxes for the objects you don't want to include in your database model. To proceed, click Next.
Figure 19-9. You can select from the options that your DBMS makes available to Visio. An Access database does not support triggers and stored procedures, so those options appear dimmed.
Visio lists the tables and views that your target database contains on the next screen of the Reverse Engineer Wizard, as Figure 19-10 shows. For convenience, you can click the Select All button, and then clear the check boxes for any tables you don't want to include. The options you select here will be included in the model that Visio preserves, but don't necessarily have to appear in the diagram—which is an option on the next screen of the wizard. Click Next to continue.
Figure 19-10. You can choose the tables and views you want to include in the model that Visio creates.
If you want Visio to generate a diagram based on the objects you've extracted from the database, you can select the Yes, Add The Shapes To The Current Page option on the next screen of the wizard, as Figure 19-11 shows. If you select the No, I Will Add The Shapes Later option, Visio still extracts the selected information from the database and adds it to the model that's stored with the diagram. However, no shapes for the tables, views, and relationships are added to the drawing page. Instead, the objects are listed in the Tables And Views window. To continue, click Next.
Figure 19-11. You can choose whether Visio diagrams the tables and views that the wizard extracts or adds them to the model without adding them to the drawing page.
On the last screen of the wizard, you can review the options you've selected. Click Back to change your selections on any of the screens, or click Finish to extract the selected information and add it to the model in the Visio drawing file. Visio creates a database diagram if you selected that option. Otherwise, choose Database, View, Tables And Views to see the tables and views extracted from the target database. Review the contents of the Output window for information about the reverse engineering process.
Tip - Display Related Tables
To quickly display related tables on the drawing page, drag a table from the Tables And Views window onto the page. Right-click the shape, and then choose Show Related Tables. Visio adds all the tables in your model that have a relationship to the first table and connects them.
To keep your model in sync as changes are made to the physical database, you can use the Refresh Model command on the Database menu. This command starts a wizard that looks and works much like the Reverse Engineer Wizard. With the Refresh Model Wizard, you connect to your target DBMS, and then the wizard compares your database model diagram to the current physical schema. If the wizard detects any differences, you can choose to update the diagram to match the database, or ignore the difference on the screen shown in Figure 19-12.
Figure 19-12. If your diagram no longer matches the target database, the wizard lists each difference.
To run the wizard, choose Database, Refresh Model. To resolve a conflict, select an item from the list displayed by the Refresh Model Wizard, and then choose the No Change option to ignore the conflict or the Refresh Model option to update your diagram.