Back in the early noughties, Microsoft Visio used to fully support both reverse and forward engineering of database entity relationship diagrams: you could choose to hook up Visio to an existing database and generate an ERD automatically, or alternatively create an ERD manually in Visio and then pipe out the results as a SQL script of DDL statements. Sadly, when Microsoft released Visio 2010, they discontinued the forward engineering component, and with later releases they have done away with reverse engineering as well. If, like me, Visio is often the only licensed product available on your computer to use for making ERDs, it can be rather frustrating to go through the process of setting out your diagram only to then have to manually write the SQL to create the physical tables. To make matters worse, versions of Visio earlier than 2010 no longer function on modern Windows operating systems like Windows 10.
However, all is not lost: if you are able to get a copy of Microsoft Visio 2010 (which is available through the Microsoft Visual Studio Subscription service) you can not only continue to use its existing reverse engineering functionality – you can also bolt on a bit of freeware to enable forward engineering as well! Alberto Ferrari created a handy C# project that utilises existing Visio libraries to add an extra ‘Forward Engineer’ menu within Visio 2010, and you can freely download that project here.
The story doesn’t end here, however. The project is hosted on a now-defunct code sharing platform called CodePlex, and somewhere along the line the original setup.exe file that you need in order to install the Forward Engineer plugin has gone astray. This means that when you download the archive from CodePlex, you only have access to the original source code. In the steps below, I will explain what you need to do in order to re-generate the setup file and thus be able to install the Forward Engineer plugin for Microsoft Visio 2010…
- Ensure that you have installed Microsoft Visual Studio 2010 with Service Pack 1. Again, if you have a Microsoft Visual Studio Subscription, you can easily get hold of this.
- Download the ForwardEngineer zip file from the CodePlex archive. In order preserve the project for future prosperity, I have attached it to my blog as well (just in case CodePlex itself disappears all together!)
- Extract the zip to a temporary folder, and then navigate inside that folder to the subdirectory called ‘sourceCode’ and unzip the zip file inside (which is also called sourceCode.zip)
- Now navigate into the unzipped directory to a subfolder called v1 and drill down again into Visio.ForwardEngineer
- In here you should find a Visual Studio solution file called Visio.ForwardEngineer.sln which you can open in Visual Studio 2010 (ignore any messages about “going offline”, just click OK)
- In the Solution Explorer pane, right click on the Visio.ForwardEngineer project and choose ‘Properties’
- Select ‘Signing’ and click on the ‘Create Test Certificate…’ button
- Put in a dummy password (it’s not important what you use)
- Now make sure that you select the certificate you have just created in the ‘Choose a strong name key file’ dropdown instead of the default ForwardEngineer.pfx
- From the Solution Explorer, expand the ‘References’ section of the ForwardEngineer project and look for MODELENGLib (it will have a small yellow exclamation mark next to it)
- Right click on it and choose ‘Remove’
- Minimise Visual Studio 2010 and open a new File Explorer window
- Navigate to the following directory: C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin and confirm that you can see the TlbImp.exe file in that directory
(if you are unable to find this directory on your computer, you will need to install the Microsoft Windows SDK for Windows 7 and .NET Framework 4 pack, which you can get here)
- Next, navigate in File Explorer to the following directory: C:\Program Files (x86)\Microsoft Office\Office14 and confirm that you can see the MODELENG.DLL file in that directory
- Right click on that file and choose ‘Copy’
- Navigate in File Explorer to C:\Temp (create this directory if it doesn’t already exist)
- Once in the temporary directory, paste your copy of MODELENG.DLL
- Rename the file to MODELENG_ORIG.DLL (Right click > Rename)
- Open a new Command Prompt window (press ‘Start’ and type ‘cmd’ to find it)
- In the Command Prompt window, type cd “c:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin” and hit Enter
- Now type the following command and hit Enter:
TlbImp.exe C:\Temp\MODELENG_ORIG.DLL /noclassmembers /out:C:\Temp\MODELENG.DLL
You should see the following output confirming ‘Type library imported to C:\Temp\MODELENG.DLL’:
- Now return to the ForwardEngineer solution in Visual Studio 2010
- From Solution Explorer, right click on the ‘References’ section underneath the Visio.ForwardEngineer project and choose to ‘Add Reference…’
- Click on the ‘Browse’ tab at the top of the window, navigate to C:\Temp, select the MODELENG.DLL file (not the MODELENG_ORIG.DLL file!) then click OK
- You should now see MODELENG.DLL sitting happily in the References tree of the Solution Explorer without any yellow exclamation mark next to it
- Right click on the Visio.ForwardEngineer project in Solution Explorer and select ‘Rebuild’. The compile will instantly fail, but this is OK, because you’ll need the error message to direct you to the line of code to update (see next step)
- From the Error List in the bottom pane of Visual Studio, double click on the first error in the list; this will open a file called Visio.ForwardEngineer.DataModel.ModelTransformer
- Amend the second line of the file to change MODELENGLib (which is underlined with a red wriggly line) to just MODELENG
- Click on the ‘Save All’ icon on the ribbon bar at the top, and then repeat step 26 to rebuild the project a second time
- This time around, you should get a nice, clean message in the Output pane that says “Rebuild All: 1 succeeded, 0 failed, 0 skipped”
- Go back to File Explorer and navigate to the folder where you originally unzipped the ForwardEngineer project. Go to the following subfolder: \sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer\obj\Debug
- Select the files from the Debug folder and copy them
- Navigate to the following subfolder: C:\Users\954531\Downloads\ForwardEngineer\sourceCode\sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer\obj\Release
- Paste the files into this folder
- Now return to Visual Studio, locate the Visio.ForwardEngineer.Setup project in Solution Explorer, right click it and select ‘Rebuild’
- This time you should receive a nice message in the Output pane indicating “Rebuild All: 2 succeeded, 0 failed, 0 skipped”
- Now return to File Explorer and navigate to: C:\Users\954531\Downloads\ForwardEngineer\sourceCode\sourceCode\v1\Visio.ForwardEngineer\Visio.ForwardEngineer.Setup\Debug
- In this directory you will find two files: setup.exe and a Visio.ForwardEngineer.Setup.msi
- Double click on setup.exe to install the Forward Engineer plugin for Microsoft Visio 2010!
Once you have installed the plugin, you will now find that when you open Visio 2010, you have an additional menu on the ribbon bar:
Now, when you create a Database Model Diagram, you can use the ‘Forward Engineer’ button to generate a SQL script with all of the DDL commands to physically create the data model in a SQL Server database.
A big thank you from me to Alberto Ferrari for originally creating this excellent feature for Visio. Microsoft should definitely hire him, if they haven’t already!