How to request mySQL data from Microsoft Access

[HuntsvilleMan]

Is there a better way to request mySQL data from a Microsoft Access application.  The code I've cobbled together with much help is below.  My server is Yahoo Small Business.  Thanks for any suggestion about better way to do this kind of data request or ideas for making it more secure.  It's all new to me.

 

Mike

    'Microsoft Access program to call PHP program
    Set rs_result = MyDB.OpenRecordset("result", dbOpenTable) 
    baseurl = "something.com/php/mydirectory/"
    CurrentDb.Execute ("DELETE FROM result")  'Remove results from the temporary table     
    url = "https://" + baseurl + "result.php?" _
            & "date=" & URLEncode(txtYearFirstDate) _
            & "&myDATABASE=" & URLEncode(myDATABASE) _
            & "&myTABLE=" & Trim(myTABLE) _
            & "&myUSERNAME=" & URLEncode(myUSERNAME) _
            & "&myPASSWORD=" & URLEncode(myPASSWORD)
    Call Application.ImportXML(url, acAppendData)
    
********************************************************

<?php
   $myDATABASE = $_REQUEST['myDATABASE'];
   $myTABLE = $_REQUEST['myTABLE'];  
   $myUSERNAME = $_REQUEST['myUSERNAME'];
   $myPASSWORD = $_REQUEST['myPASSWORD'];

   $query = "SELECT * FROM " . $myTABLE;
   $query .= " WHERE substring(confirm_time,1,10) = " . "'" . $thisdate . "'";
   $result = mysql_query($query);

   /*send back requested data in XML format */
   echo '<?xml version="1.0"?>';
   echo '<results>';

   while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
   {
     echo '<result>';
     echo '<person_id>' . $row['person_id'] . '</person_id>';
     echo '<appointment_id>' . $row['appointment_id'] . '</appointment_id>';
     echo '<confirm_time>' . $row['confirm_time'] . '</confirm_time>';
     echo '</result>';
   }
   echo '</results>';
?>

Use CODE tags in the future, thank you.

[Muddy_Funster]

I'm sorry - what is it that you want to do?  Do you know that MS Access has an ODBC conection layer that can run the MySQL ODBC Connector driver and just link directly to your MySQL database tables its self?

[HuntsvilleMan]

What I am doing with this code is making a request for data from a mySQL database hosted by my ISP (Yahoo Small Business).  The result is Microsoft Access fields being appended by the requested data in the mySQL rows.  So far as I can tell Yahoo doesn't support an ODBC interface and since it isn't my server I'll need some pointers on how I might do this myself or possibly get Yahoo to offer this service.  Maybe I need a better ISP.

 

This ineed to send data back and forth between Microsoft Access and a distant servers is all quite new to me.  On a local Firebird server I do have an ODBC interface that makes data access a bit easier. 

 

So, what I'm looking for are better solutions to use with applications that exchange data between Microsoft Access and mySQL databases not on my local area network.

[Muddy_Funster]

The easier (and in my opinion) better way would be to use the ODBC if you can.  If Yahoo won't support you in doing this then I suggest moving to 34SP.com as I know for a fact that I have ODBC access to my several MySQL databases I get from hosting with them.  I would make this sound less like an advert if I had working knowledge of other hosting companies that provided the service - but I don't, although I do hope some other people will post some up.

 

I personaly suggest sticking with what you know just now and moving once you have the knowledge and confidence that if something goes wrong somewhere along the line, you will be able to sort it.

 

Oh, and I only asked as your thread title is asking the oposite of what you are in the post