5

I've worked with MySQL and MSSQL for some time and have used a variety of CASE and UML tools when designing some of my more complex projects.

I was recently asked by a colleague if I could provide an Access database for his department. The application itself isn't too complicated, and Access actually looks like the ideal tool for the task at hand.

Apart from a couple of very amateur databases many years ago (before I'd even heard of normalization) I haven't much experience of Access. I've got into the habit of using ER diagrams to design databases and want to do the same when working with Access. It seems that the built in "Relationship" view is fine for viewing relationships (duh!), but you have to create the tables first, which seems to defeat the point somewhat.

Does anybody know of any way to directly design the database using an ERD, either natively within Access, or else via round-trip generation using a 3rd party tool? Non of the software I usually use support Access.

I think I've been spoiled by by using "real" databases, but any help is appreciated...

Update: I selected the answer below because it jogged my memory about using ODBC for the job - I already use the excellent ArgoUML application that can connect via ODBC. Just wanted to clarify that selecting the answer below wasn't endorsing the ModelRight application as I never needed to install it.

James Marshall
  • 630
  • 2
  • 7
  • 15

3 Answers3

5

ModelRight will do this for you. You can download the community version which will access any db using ODBC and Mysql.

http://www.modelright.com/downloads.aspx

I currently use it with SQL server and it works great.

Dwight T
  • 1,457
  • 2
  • 11
  • 20
  • Well I now feel stupid! I already have tools that'll let me connect via ODBC, but it's so long since I've used Access it didn't even occur to me! – James Marshall Oct 24 '08 at 09:37
0

You can most certainly design tables from the diagram view. Simply right click on any table in the diagram and select design view.

To add a table, you do have to create the table first (but that’s not really a big deal, and really only amounts to an extra mouse click or so.)

And you find that the Access diagram correctly shows left joins where as SQL server and others you note do NOT show left joins in the relationships designer correctly. So this all important join information has to be documented some place else in those other "supposed" professional tools. so somehow managed to leave out a most useful and important option in their relatonship desingers.

So any and all table design can be done from the Access relationships (diagram) view. You ONLY “very slight” change is to whack the create tab, and then choose table design – you have to add the table. (just add the PK id autonumber) and save it. From that point onwards from the Access ERD view, you can add the table and right click to design everything else` (including setting up relationships) from that point on.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

Dezign from datanamic (erd tool) can read in access databases and make a diagram out of it. No ODBC needed. Native connection to ms access.

dezign for databases