X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Setting up MySQL and Access    
post Apr 7 2009, 07:31 PM

Posts: 66
Joined: 5-September 08
From: Colorado

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?
Go to the top of the page
post Apr 7 2009, 07:58 PM

Dungeon Cleaner
Posts: 1,504
Joined: 16-June 07
From: Banana Republic

You'd need MyODBC, which is MySQL's ODBC driver.
For a starter database to play around, if you need one, world database is a good start.
A good guide from MySQL for using Access as front-end.
Finally, IMHO, a mandatory reading: Jet/ODBC Connectivity whitepaper.. 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:
Configuring MyODBC
Using Access as front-end to MySQL
I hope that helps! sad.gif
Go to the top of the page
post Apr 8 2009, 01:49 AM

Posts: 66
Joined: 5-September 08
From: Colorado

Sweet! That's a lot to chew on, but this is definitely what I needed. Thanks a bunch.
Go to the top of the page
post Apr 27 2009, 10:17 PM

Posts: 2
Joined: 27-April 09
From: NC

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.
Go to the top of the page
post May 13 2009, 03:24 PM

Posts: 636
Joined: 22-February 04

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...
Go to the top of the page
post May 15 2009, 02:42 PM

Posts: 636
Joined: 22-February 04

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
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>
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 07:27 PM