Currently, the only db that we have is on excel, which is not what we want. I am looking to create a db with access 2000 as the frontend and mysql as the backend. I wanted to use access as the frontend because I use a lot of the information in the db to create reports in excel that are sent via e-mail. So, I thought once the data was linked to access from mysql, then I could use vba to export it into the reports on excel. Is this the easiest way to do this? Or should I use another front end? My db will only have about 20 tables, but I am not sure how much it will grow in the future.
Thank you for your time and have a great day.
Actually, there is an easier way to export MySQL data to Excel . There is an utility called MySQL Data Wizard, which can export data from MySQL to a number of formats, including MS Excel. The fully functional version of this software is available at http://www.mysqlmaestro.com/products/download.html.
Similar export features are also implemented in another product called MySQL Maestro. The main difference between MySQL Data Wizard and MySQL Maestro is that the second one also allows you to develop MySQL databases and administer MySQL server. This product is also available on the same site at http://www.mysqlmaestro.com.
SQLMaestro's correct that the application at mySQLMaestro.com (SQLMaestor <> mySQLMaestro.com? hmmm... )will do what you need, as will several other SQL frontends - including Access which you already own and know how to use. However if your reports need to go out on a schedule you might want to also consider a script-based solution that could be controlled with Windows (or other) scheduling.
I say this from experience - it's a royal pain to have to be "here" at 7:30am every Mon, Wed & Fri just to click "okay" to generate a report & email it. -As long as the reports & recipients don't change often, it's easy enough to accomplish both generating xls files OTF and email them using a simple VBScript, ASP,or PHP.