create ERD by reverse engineering mdb

[Alisa]

Does anybody have a database management/UML/case/diagram tool that they really like that isn't super expensive that will successfully reverse engineer an mdb including the relationships between the tables? I know that is a lot to ask, which is why I haven't found anything yet. Everything I have looked at either doesn't reverse engineer from ODBC, or it will bring in the tables but not the relationships, or it costs a lot, or it is just really difficult to work with in general. Ultimately I have 2 objectives: 1 is to create an ERD of an existing mdb, 2 is to make a bunch of changes and then forward engineer it into SQLServer or MySQL.

Any recommendations?

 

[Banana]

Hey, been while since I saw you online.

I've tinkered with various ERD tools, but never found one that can take in Access' relationships.

I did another search and did find some JET Console tool, but neither support generating scripts. I'm inclined to suspect that there is no easy way to generate CREATE TABLE scripts based on what you have in your Access file (which should also include the relationship).

If the schema is really complicated and you don't want to do it from scratch**, one way would to be do a loop over all tabledefs and interrogating each table for the name, field data type and name, and indexes and constraints (which would be Relation properties, I think).

**I also am generally inclined to do it from scratch due to the simple fact that I can't always count on the data types being consistent and not wanting to miss out on advantages that the new source can provide.

 

[Alisa]

I know, I've been so busy I haven't had time to come around and hassle all of you in quite awhile! You can tell things are winding down when you start worrying about the huge undocumented mess you have on your hands though

I was starting to come to the same conclusion you did about doing it from scratch, but I am not one to take the hard way if there is an easy way to be found, so I thought it wouldn't hurt to see if there was some great tool out there that I missed out.

 

[Banana]

Well, considering that if it was actually easier to do it automatically, it'd be more widely known, even if it was just to SQL Server only. (Gee, I forgot if Upsizing includes relationship? If it did, then you could use upsizing THEN script the tables from SQL Server...)

 

[Alisa]

Well, considering that if it was actually easier to do it automatically, it'd be more widely known, even if it was just to SQL Server only. (Gee, I forgot if Upsizing includes relationship? If it did, then you could use upsizing THEN script the tables from SQL Server...)

Now that is an intriguing idea that I hadn't considered. I will run the upsizing wizard and see if that works.