Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ PHP, Perl, MySQL and Postgres _ Setting up MySQL and Access

Posted by: redphoneconsulting Apr 7 2009, 07:31 PM

I have a database with a handful of users that are all in different states.
I have a web server with MySQL 5.0.67 installed on it. The web server is managed using cPanel.
I am fairly competent with Access as a stand alone, and I've split databases into access front ends and back ends for same network type of activity, but I'm coming up empty on how to host a MySQL back end on my web sever that has an Access .mde front end in several different locations.
I need pointed in the right direction how to create a MySQL back end on my server, and then connect to it using access.
A really simple one table demonstration would mean the world to me.
Can someone help me with this?

Posted by: BananaRepublic Apr 7 2009, 07:58 PM

You'd need http://dev.mysql.com/downloads/connector/odbc/5.1.html, which is MySQL's ODBC driver.
For a starter database to play around, if you need one, http://downloads.mysql.com/docs/world.sql.zip is a good start.
A good guide from MySQL for http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-usagenotes-apptips.html#connector-odbc-usagenotes-apptips-microsoft-access.
Finally, IMHO, a mandatory reading: http://support.microsoft.com/kb/128385. I say mandatory because it is very instructive in designing a effective front-end client using any RDBMS backend. Without this whitepaper, it's easy to flounder with so many errors on data type conversions and #Deleted and slow & ineffective queries.
How for some information on configuring & setup:
http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-dsn-windows.html
http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-examples-tools-with-access.html#connector-odbc-examples-tools-with-access-linked-tables
I hope that helps! sad.gif

Posted by: redphoneconsulting Apr 8 2009, 01:49 AM

Sweet! That's a lot to chew on, but this is definitely what I needed. Thanks a bunch.

Posted by: thunksalot Apr 27 2009, 10:17 PM

Okay, I have the *exact* same issue. But, I don't have any experience setting up Access as an FE or using ODBC. And, since I kinda need to get this working in a pinch, I think I need some expert guidance. I'm not sure if there is a better place to post this on this site, but if there are any freelancers that would be interested in helping me for a couple hours doing some desktop sharing, I think that is what I need.
Thanks.

Posted by: TheZjooj May 13 2009, 03:24 PM

Im in if you want...as a matter of fact I just discovered a way to use PTQ's with access references to forms...that one is HUGE!!
Anyone interested in know I will be posting my solution...soon...
Regards,
Zjooj

Posted by: TheZjooj May 15 2009, 02:42 PM

This is also posted in the forms section of the forum here so if I am breaking a rule someone please let me know...I just thought here would be a good place to put it as well...
****
here's how I did it...
Create the mysql query on the mysql server (or MSSQL query)
create the pass through query in access to reference the mysql server query --- call mysql_query() ---
Create another query and base it on the pass through query created in access like you would normally base it on a table.
You can now add the fields and reference the forms text box or other fields.
Note - In some instances you may get a run-time error and it may tell you you can not base a subform or subreport on a pass through query.
To resolve that error simply apply the recordsource for the subform or report at run-time instead of applying it as the forms recordsource during design. I basically create the subform with the above method already done and my subform based on that query so I can add the fields to my form. Then once I save it I remove the recordsource from the subforms properties. Now when the main form opens I have it apply the recordsource to the subform and requery it. No real performance hit so far and it works like a charm. Now I can pass any textbox, variables etc to a pass through query with no limitations.
e.g. -
MainForm OnOpen()
me.subform.recordsource = QueryBased_on_a_ptq_Query
me.subform.requery
Love to hear feedback on this...can I make it simpler? Could there be a faster or easier way. Does it work for you or am I in some force field that allows it to only work for me? <grin>
Regards,
Zjooj