An Access Front-End to MySQL - State of the Union
(Page 4 of 4 )
For the developer who is familiar with the Access dialect of SQL, there are certainly times when it may be useful to connect to an online database using Access. However, there are a considerable number of limitations to what can be done. The functions of MySQL are not supported and also, surprisingly, some of the capabilities of Access. For instance, I was disappointed to find out that nested select statements - a capability that production versions of MySQL lack but Access has - won’t work. Also, given the restrictions on altering the structure of your database, a developer will most often want to work with a tool such as phpMyAdmin rather than Access. In some ways it looks like we have the worst of all possible worlds – we’ve lost functionality on both sides of the equation. But don’t start the divorce proceedings just yet.
Access’ reporting capabilities are quite a different matter. You can use the report wizard to create professional quality reports quickly. Granted that a report is only as good as the query it is based upon, knowledge of Access’ version of SQL will also be necessary in order to use the report writer.
You can also take advantage of some of the other features of Access. You can use Access’ documentation capabilities (Under the menu options, “Tools”, “Analyze”, “Documenter”) to produce a quick report on the structure of your database. Note though, that MySQL data types will have been converted to their Access equivalent. If you need an Entity Relationship Diagram of the tables in your database, this can also be created and printed from Access. Unfortunately though, cardinality cannot be shown. Should you need to convert a MySQL database to Access, using Access as a front-end is probably the quickest way to do this. With a “SELECT …INTO” statement you can quickly produce a local Access version of any linked table.
What is a disadvantage for the developer is perhaps an advantage for the database user. Because data definition queries are not supported, there is no danger of the database structure being accidentally altered. While phpMyAdmin is an excellent piece of software and not difficult to use, lots of damage could be done in a short amount of time by an inexperienced user. If you are a web developer with customers who are willing and able to do their own database updates, Access may be an ideal tool to act as a front-end. If the database is not large and your client is already familiar with Access this may in fact be the ideal solution.
Some web developers might question the wisdom of allowing clients to perform database updates to their site – Isn’t that taking money out of your own pocket? I don’t think so. A client who is actively involved with his/her website is much more likely to continue needing your services than one who isn’t. A customer who recognizes the utility of their website is going to find even more uses for it.
Resources
http://www.devarticles.com/c/a/ASP/Using-MyODBC-To-Access-Your-MySQL-Database-Via-ASP/
http://www.mysql.com/products/connector/odbc/
http://builder.com.com/5100-6388-1050135.html
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
