NOTE: This web log entry assumes that you have the software installed on your workstation: MySQL, MS Access, and phpMyAdmin

MS Access as a Supplemental Development Tool

I used to work a gig as Web Developer for an office in the Education College of a well-known State University. The Program Director needed a ColdFusion programmer who was skilled with Macromedia DreamWeaver, so I began working there on a project which required me to redesign an MS Access Database.

Working with MS Access every day, it wasn’t long before I grew fond of the “Relationships view” because of the intuitive nature of its visual representation of the relationships between multiple tables. In addition to simply allowing the user to draw relationships between multiple tables, the MS Access Relationships view allows for specification of the types of relationships between data.

All Access Pass to a Well-Structured DB

For those of you out there who happen to have to have MS Access installed on your Web Development workstation (the Microsoft Database is standard issue with the Microsoft Office suite), I recommend learning to use it. Don’t misinterpret: i do NOT recommend trying to develop your web applications with MS Access, but to develop your tables and relationships, and to use the Form wizards within MS Access may actually help you to develop a better understanding of what’s going on inside of your own creation because the visual interface of Access might help you to see relationships that you may otherwise have overlooked, and it might give you an idea for how you want to setup your Web Forms. Utilize the graphical interface to learn something about table relationships you will employ in MySQL, and use the Relationships view as an aide design more efficient data access forms by manually entering into MySQL that which you created in Access. It may NOT always work out favorably, but it is never harmful to gain new perspective.

MySQL: phpMyAdmin Export XML

To get the ball rolling quickly, begin your project in MySQL and phpMyAdmin^†. Open your browser and point it to your local installation of phpMyAdmin, create your database and tables. If you need help with determining the proper data-types for your MySQL tables, reference mysql.com DATA TYPES. Once you’ve got your basic table structure setup, then Export from the options menu above database view as soon as your database become visible in the main window. highlight all of your tables, and choose XML as the export format.

If you have MS Access 2003, it should import your table structure perfectly.
NOTE: MS Access and MySQL Data Types are not 100% interchangeable, so you may encounter errors. The point of this process is to develop good relationships, and efficient forms– but NOT for the technical aspect of data-type storage. For determining what data types to use, refer to the MySQL manual at http://dev.mysql.com/doc/. Most importantly, ensure that you have the proper data types after you have finished playing with MS Access.

The point of the whole process is not to take shortcuts and play with Microsoft Office, but to learn something about database design itself. It doesn’t make any sense to try to use an .mdb file for your Web Application, so please don’t try to do that. Your learning will only be enhanced if you always start off by creating your database in MySQL. Create your tables, and do your best to work out a Normalized relationship between the tables all inside of MySQL.

There’s no replacement for good planning, and your web application will only ever be as good as the carefully developed, solid database used as the backend. Become a master with your database software. When you’re able to create the basic database structure, including multiple tables with the appropriate data types for your application, you might consider having a look at it from another perspective in MS Access because it might help you to grow a skill for good database planning.

MySQL should be Your SQL in the End

If you’ve made a mistake in judgment in your initial design, and spend countless hours trying to “code around it”, this might be an indication that you could use a second perspective. Maybe it’s not for you, but some people might benefit from the added perspective MS Access has to offer. I can not stress enough, however, that the whole point of the exercise is ONLY as a supplement– a possible “self-help” method that you may wish to try on your own to gain a better understanding, and better planning habits.

MySQL is a better database for working with web sites for several reasons, not the least of which is its low-cost, the very straight-forward interface, and low level of abstraction, not to mention the countless other Application-specific reasons, such as multiple simultaneous connections (very limited with MS Access, and essential for any web application).

^†(MySQL 5.0 and phpMyAdmin 2.6 are installed on my PC while authoring this post)