Reverse Engineering an Access Database

Reverse Engineering an Access Database in Visio is a quick and easy way to document your work. These instructions refer specifically to Visio Professional 2002 SP-2, which is installed on the MIS notebook computers. Note: If you wish to print these instructions, set the Page Setup to Landscape mode.

Start Visio. Normally, you will see the “Choose Drawing Type” dialog (see below); choose “Database” as the Category, then the Template “Database Model Diagram.”

You will see a blank drawing surface, with a set of shapes on the left.

Choose the menu item Database, then Reverse Engineer... from the drop-down list. This starts the Reverse Engineer Wizard (see following figure). In the Installed Visio drivers list, choose “Microsoft Access.” The Data source for your database is probably not in the list of Data sources; therefore, click the New... button.

Choose “User Data Source,” then click Next >.

Choose “Microsoft Access,” then click Next >.

On the following screen (not shown), click Finish.

The next screen allows you to name and describe the data source, then indicate its location. The name must be unique (vis-a-vis the other data source names on the first screen of the wizard). The Select… button allows you to browse to find the database. Once the information is entered, click OK.

You will be taken back to the first screen, with your new data source highlighted (see below). Click Next >.

You will be asked to enter your User ID and Password. After entering these – if any – click OK.

The next screen requires you to select object types to reverse engineer. I usually uncheck Views. Click Next >.

On the next screen, Select All tables, or those that you want to include and click Next >.

Add the shapes to the current page and click Next >.

Review your choices, then click Finish.

Visio will extract the database structure and arrange it automatically for you. Click the X at the lower left corner of the Output to close that window and change the magnification (near the upper right of the window) to something more legible (e.g., 75%).

To change the style of the ERD to something more familiar, choose Database, Options, Document…   On the General tab, I usually set the symbol set to Relational and use Physical Names; On the Table tab I choose to Show physical data types; On the Relationship tab I click Crow's feet.

You may have to fiddle with it a bit to get a pleasing display.

Don't forget to save your work!

This is a living document that is subject to revision during the course of the class. Last revised Friday, November 03, 2006; at 8:47:44 AM.