This article shows you how to connect to a database and populate a diagram with its tables, relationships and properties. For this I will use an Access database but it could be any data source where you have its driver installed.
I want to represent a relational database structure on a diagram but I want it to show primary and foreign keys, unique indexes and the relationship structure between tables. I also want it to contain any parameters, constraints or other logic associated with the tables.
I also want to show how to generate a SQL Server database from a Visio diagram but that feature is not available in later versions of the software. Therefore all of the reverse engineering and database generation screenshots come from Microsoft Office Visio for Enterprise Architects. The Enterprise Architects edition came with Visual Studio .NET Enterprise 2003 and 2005. Whilst the reverse engineering featire remained for all editions of Visio Professional or Premium thereafter, the ability to generate a database was removed.
Reverse engineering a database
For the reverse engineering example, I will use the fictional sales database example from Microsoft called Northwind Traders that was created using Microsoft Access. If you want to follow along, you can either open Access and create a new Northwind database using Access (search for Northwind in the template box) or with any database that you can connect to. The screenshots are from Office 2003 but the majority of features remain the same today, just that they might be in different places.
In newer versions of Visio, selecting a new Database Model Diagram will ask you for units of measurement to create the diagram whereas in older versions you select the measurement with the template.
In the later versions of Visio, the Database Reverse Engineering Wizard launches immediately. It can be cancelled if you want to create your own database diagram. For older versions, you get a blank diagram and have to launch the Wizard manually.
The Database Reverse Engineering Wizard is the same as it was in 2003, just that visually it might look slightly different.
You should be able to select the Visio driver appropriate to the database that you want to connect to as well as the Data source. Try ODBC if you don’t have Microsoft Access on your list.
Enter the database connection credentials next. For this Northwind example and in most cases, the user and password will be blank.
Select the database and click OK. You might get a message to say that the default driver has changed if you needed to switch drivers earlier.
If you want to see Views and Tables (or even Stroed Procedures if you are using SQL Server) then keep those selected. I’m only interested in Tables for my diagram but I’ll keep Primary keys, Foreign keys, Check clauses and Indexes selected.
Select All for the tables or other objects you have or unselect any that you don’t want to include, like auditing tables that you might not want to show up on your model.
The next step is answered Yes as you want the objects to be drawn on the diagram. You will then get one final look at what will be converted before clicking Finish. You can click the Back button if anything is wrong.
The results will display. If there are a lot of tables, you might have to moved them around the page to get a better view. A report showing the step by step actions that Visio went through to draw the diagram and how long it took, is shown at the bottom of the screen.
If you select an object, you can select teh properties in the lower pane
Generating a database from a Diagram
I am going to use the same database model diagram to create a SQL Server database. This could be any database, even Access again, as long as you have a driver for it on your Machine. I believe that the SQL Server driver comes with Visio but it is no good unless you have a SQL Server to connect to. If you have SQL Server Management Studio installed with any version of SQL Server (local or remote) then you can do the following as long as you have Visio for Enterprise Architects also installed. Otherwise, you can just look at the screenshots to see what used to be available.
SQL Server is not as forgiving as Microsoft Access so I need to tidy up the model before we start to prevent an indexing error.
It is quite simple, for each table the primary key(s) need to be set to Required.
Remember that each table needs this fix. To start the database generation wizard, select Generate from the Database menu.
I don’t already have a SQL Server database to put the new tables in so I’m going to select “Generate new database”. Click Next and then select teh “New…” button.
You may get a message to say that the default driver has been changed because it was set to Access previously.
Creating a new data source requires a few more steps. Select User Data Source and then SQL Server.
Select the SQL Server and give this data source a name and description.
Before clicking Next, enter the details of how you connect using Windows authentication or other credentials.
Keep the defaults and click Next and Finish.
Connection details will show and you can Test it here, click OK when ready to continue.
Once you have the Data Source set, add a name for your database and click Next. When prompted for your user credentials, enter those or click OK.
You get one final review before clicking Next to validate. If there are any errors, click back or cancel to fix them. If not, click Finish.
As we will be creating a new database in SQL Server, we can also specify the logical and transaction log file names and paths. Or leave them empty to use SQL Servers default settings. Close, kicks off database generation.
It will automatically create the database but will also generate a script as a record of the sections. you can also use that to re-run the database creation independently, maybe on another server. Click Yes to view the script.
From the code editor, I always “Save As” with a SQL extension as I have SQL files automatically associated and opening with SQL Server Management Studio.
The final thing to do is to check that the database has been created on the SQL Server. From Microsoft SQL Server Management Studio I can see that Northwind now exists and can see that the primary key of the tables do not Allow Nulls.
By the way, you don’t have to create a database or even connect to a database to generate the script. You can leave the database field blank for that option.
Final note
My final thought is that I used to love this feature and I wish that Microsoft would bring it back. I shouldn’t have to keep an old copy running on an old machine. If they don’t want to have an Enterprise edition of Visio, then they should include it in Professional.