Question Reverse Engineering Access Database in JDeveloper

[Yarimel]

Hi,

How do you reverse engineer an Access database in JDeveloper? Is there a special plugin I need to use?

Yari

 

[georgedwilkinson]

Are you talking about just the tables? If so, you can set up an Access ODBC DSN for your database and use the ODBC-JDBC bridge to connect with JDeveloper.

 

[Yarimel]

I would like to get all functions that I get with Visio, Tables and Views.

How do I create the Access ODBC DSN?

 

[georgedwilkinson]

Go into Windows settings and create a new ODBC connection.

I think you can see the queries in an Access database (the rough equivalent of views in real-live DBMSs).

 

[Yarimel]

Hi George,

Thanks for the info, I was able to connect to the database and see all the information.

Yarimel

 

[georgedwilkinson]

Alright! Do you plan on "re-engineering" it into Oracle?

 

[Yarimel]

No, I was just brought in to document an existing system. There will be a new system created, but the implemetation has not been decided upon as of yet.

 

[georgedwilkinson]

Nice, are you using JDeveloper's UML documentation?

 

[Yarimel]

Yeah, unfortunately they did not want to shell out the money for something like Visio where it would only take a couple of weeks to document because of its reverse engineering features. I now must do everything by hand.

 

[Banana]

Waitaminute.

I think I'm missing something... I have Visio, but didn't end up actually using it as it was too much work (e.g. I couldn't dump the ERD diagram into a SQL file and back)... or can that be done?

 

[georgedwilkinson]

Yes, it can be done in Visio. It is a bear, though.

 

[Banana]

Really. Should go and google for tutorial on defining the tables relationship within Access using Visio, then... Access's relationship tool is a bit long in tooth, if you ask me. Not that I needed it since migrating to MySQL, as it has own workbench for that job, but would be good to know just in case.

 

[Yarimel]

As far as I am aware you cannot dump the ERD Daiagram into a SQL File, but it is the best resourse for reverse engineering an Access Database because I will pull all of the views and relationships out of the database. I am working on a DB that contains about 70 tables and although this may not seem like alot, the tables are not normailized properly and each have anywhere form 100-150 attributes. Along with the 1500+ views, being able to extrapulate the views becomes very important. With this ability I only have to build the ER diagrams from scratch.

 

[Banana]

Thanks, Yari for that exposition; nice to learn a new thing.

Good luck with your project (sorry I couldn't actually help out!)

 

[georgedwilkinson]

Oracle Designer is just about the best ERD modeling tool out there (I still run it on my old 2000 laptop). However, the installation requirements are difficult, at best, and the learning curve is incredibly steep (I have several weeks of classroom training plus several years of experimentation/aka experience).

Visio is a POS (for this type of thing). You can fool with it if you want, Banana, but I'm trying to save you a few hours here.

JDeveloper is a pretty good mid-ground.

The bad thing about almost all of them is the issue with de-normalized tables where incredibly long tables will be documented over several pages. I used to use Oracle Designer with a plotter so I could just see some of my normalized stuff. 70 entities is a lot when you're working with standard size paper, even with a plotter.