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.