Automating reverse engineer database model with Visio

Question

We develop and maintain our database schema with a custom tool. SQL scripts and runtime files are generated from that tool. However, it does not provide a very good visual representation.

Lately I been using Visio to reverse engineer the schema. This is working great so far. But to be able to do this, I need to:

1. create an empty database
2. execute the generated script
3. start up visio
4. choose reverse engineering and choose the created database
5. select all tables, etc
6. wait for generation

I really want to automate this process. Step 1 and 2 are easy, but how can I automate the other steps.

I've been looking for a C# library to create Visio diagrams, but they al seem not suitable for this task.

For instance http://visioautomation.codeplex.com/ and http://www.graphviz.org/ look promising, but on a closer look they were not appropriate.

I've tried to record macro, but the macro recorder does not work in conjuction with the reverse engineering tools.

Answer 1

Michiel,

I am using Visio 2007 and SQLite 3.

Tonight I had a crack at reverse engineering my Sqlite3 database with success. The key is to download an ODBC driver for Sqlite. I found some here > http://www.ch-werner.de/sqliteodbc/. I installed the current one at the top of the list "sqliteodbc.exe".

Then the MSVisio set up was a bit muddled so forgive me if I do not explain the details in an exact, repeatable format.

1. The new installed ODBC driver should appear in Visio when you select: Database > Reverse Engineer > Setup.
2. Scroll down and check one of the three Sqlite drivers - I chose the "SQLite3 ODBC Driver"
3. Then click on "New" > Check "System Data Source" > Scroll down select "SQLite3 ODBC Driver" > Click "Finish"
4. On the dialog that it entitled "ODBC DNS Configuration" is where you enter your Sqlite database settings. The "Database Name" is simply the path and file name of your database. The "Data Source Name" can be anything meaningful to you as it will appear in the Visio dropdown whenever you want to reverse engineer. There are some other settings you can experiment with as well.
5. When you commence reverse engineering you will be asked to connect to your datasource using your credentials. At this point you will get a warning ...

Warning! You are using a Visio 'ODBC Generic Driver' to connect with a 'SQLite' DBMS datasource. By using an incompatible driver, it is possible that the catalog information retrieved will be incomplete.

Just click 'OK' and ignore this.

Unfortunately, in the next screen the option to select "Views" and "Triggers" are greyed out (you can select Tables: Primary Keys, Indexes, Foreign Keys though). This is a shame as I have defined a lot of these in my schema and I have benefited from rev-enging these in Oracle many times in the past.

That is it. Hope someone can find a fix for rev-enging the views.

Dan

Answer 2

Have you tried using the "Refresh" feature? I'm not sure if that is a 2010 thing or if it was always there. You point it at the database and it updates your model with the changes since last time.