The difficult I do immediately, the impossible takes a little bit longer.
Reverse Engineer a Database...
You inherited a database. You've been asked to revamp it. There is no documentation. The Relationships Window is great, in that it tells you how the tables are related but that's it. (This is providing there *are* relationships!)
You could use the Data Dictionary by Crystal which is an excellent tool and will generate the necessary reports for you. However, myself, I'm more visual and prefer an ER (Entity-Relationship) diagram, so I use Visio. AND with the free Forward Engineer Add-In ( for Visio 2010 only ) I can upsize to an SQL Server directly from my model in Visio.
Let's begin, open Visio and follow the A, B, C, D for Visio 2007 and A, B, C for Visio 2010...
Similar view for Visio 2003...
From here on it is the same for both versions...
Once you have the Reverse Engineer the wizard opens. If Microsoft Access is not there you will need to select New... and follow the steps. (For these steps we will assume it is there.)
If your database has a User and Password you will need to enter it here. Otherwise, just select OK.
Navigate to your database
( Flash slideshow ) Next you will be present with 4 windows. Simply follow the instructions (numbered 1 thru 4) and select Finish and your model will be imported into Visio, see example below...
Wondering what some of that stuff means?
Red Ovals - You'll notice in tblQuote that qQuoteID has an Identifier of I9. You will see that same Identifier in tblQuoteDetail.
Blue Ovals - The reason that is BOLD is because I have tagged that as a Required field.
Green Arrow - Data Types as they will be represented should I decide to upsize. The numbers seen in paranthesis are field lengths.
Sadly, this has been removed in Visio 2013! Here's hoping someone creates an add-in!