[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

Move MS Access Tables to MYSQL online

I have a MySQL database that is on web server.  I need to automatically move data that is on Access database on land (regular) computer and move it to the MySQL database automatically.  I can schedule the task using windows scheduler every night.  Also, the old data should be deleted before being placed in the MySQL database.  An open source solution (free) or simple code would be preferred, since I don't have a lot of experience coding in PHP.  
0
jjrr007
Asked:
jjrr007
  • 8
  • 7
1 Solution
 
Beverley PortlockCommented:
The simplest way is probably to dump the Access tables as a CSV and then FTP them to the MySQL server where they could be loaded with either LOAD DATA INFILE or a custom function written using fgetcsv()

I'm assuming that you can run scheduled jobs on the MySQL server machine so that it automatically checks to see if an import is required.

Have a look at http://www.php.net/fgetcsv and http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Are these the sorts of things you have in mind?
0
 
alain34Commented:
Hello jjrr007,

In addition, you should read the following from the horse's mouth.
http://dev.mysql.com/tech-resources/articles/active-grid.html

To me, the answer is odbc and php.
I have successfully manage to connect to a mssql using odbc to migrate the data to mysql.
I can see no reason why you could not connect to a ms access database using odbc and then push the data to a mysql database.

Look here for more information
http://uk.php.net/manual/en/function.odbc-connect.php

Regards,

alain34
0
 
alain34Commented:
jjrr007,

Another free resource http://www.kofler.cc/mysql/mssql2mysql.html#msacc2mysql

alain34
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jjrr007Author Commented:
Thanks for your responses. alain34, I agree it would be best to just query the MS Access database directly from the webserver.

Querying the MS SQL database would make this process far simpler (and less chance of an error).  I could automatically FTP the database to the webserver.  I have posted another question regarding how I can do this in PHP.  The link to the question is:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_23168623.html
I'm hoping someone can walk me through how to query the Access database using PHP from the webserver.  Please feel free to post how I can do this in this questioin and in the question noted above.  I can award points for both since the topics are different.  

Please bear in mind that the My SQL database doesn't allow remote connections and is on a Unix server.  So I can't use the link that you just posted Alain34, because I can't use VB from that Unix server and I can't run queries from a remote connection.

bportlock, I can't run automated jobs from the webserver.  Could you please walk me through how I can change my php page to query the MS Access database?  I have posted more question details in the question link above.  

Thanks again to both of you!
0
 
jjrr007Author Commented:
bportlock,
I re-read the links you provided.  They look good; I apologize for any misunderstanding  I wanted to ask some clarification on how I  can implement this method.  I could have MS access export the database tables into CSV using a MACRO at preset times.  Also, I can have the CSV file automatically uploaded to the webserver and visit the php page in a Internet browser.  So I wanted to please ask:

1. Aassuming I can do the things just mentioned, the only thing I will need to do is create a php file that will parse the csv file and save the information in My SQL.  Is that right?

2.  Do I just need to use the php code in the attached code snippet (the code snippet is from the link) ?How will it delete the old data and import the new data into MySQL?
<?php
$row = 1;
$handle = fopen("test.csv", "r");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    $num = count($data);
    echo "<p> $num fields in line $row: <br /></p>\n";
    $row++;
    for ($c=0; $c < $num; $c++) {
        echo $data[$c] . "<br />\n";
    }
}
fclose($handle);
?> 

Open in new window

0
 
jjrr007Author Commented:
alain34,

I used some sample code to query a Northwind database.  I placed the code in a php file and uploaded the northwind access database in the same directory as the php file.  When I visted the php page in the web browser, I just received a blank page.  What do you suggest?


<html>
<body><?php
$conn=odbc_connect('northwind','','');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
$sql="SELECT * FROM customers";
$rs=odbc_exec($conn,$sql);
if (!$rs)
  {exit("Error in SQL");}
echo "<table><tr>";
echo "<th>Companyname</th>";
echo "<th>Contactname</th></tr>";
while (odbc_fetch_row($rs))
{
  $compname=odbc_result($rs,"CompanyName");
  $conname=odbc_result($rs,"ContactName");
  echo "<tr><td>$compname</td>";
  echo "<td>$conname</td></tr>";
}
odbc_close($conn);
echo "</table>";
?></body>
</html> 

Open in new window

0
 
alain34Commented:
Have you checked your php error log?

Try to add the following to see if you can see that you have manage to connect
if (!$conn)
  {exit("Connection Failed: " . $conn);}
else {echo "Connection Successful";}

Open in new window

0
 
jjrr007Author Commented:
Thanks alain34,

I tried the code below and received a completely blank page.  Also, the NorthWind database was uploaded to the same directory.  

The error logs says:
PHP Fatal error:  Call to undefined function:  odbc_connect() in /hermes/web09/b2480/hy.cybereshop/Northwind.php on line 3

What do you suggest?
<html>
<body><?php
$conn=odbc_connect("Driver={Microsoft Access Driver (Northwind.mdb)};Dbq={Northwind.mdb}", '', '');
if (!$conn)
  {exit("Connection Failed: " . $conn);}
else {echo "Connection Successful";}
odbc_close($conn);
echo "</table>";
?></body>
</html> 

Open in new window

0
 
alain34Commented:
Hi,
It seems to me that php installation do not have the odbc extension installed.
You may need to change your php.ini file to enable it.
to check if it is installed properly, just fire a page with phpinfo() present and see if the extension is listed.
Regards,
Alain
0
 
jjrr007Author Commented:
Alain34,

Thanks for your patience.  I think we can get this question resolved very soon. I ran the phpInfo() in a php file and it generated a large amount of information.  Where do I look on the page to see if the extension is litsted?  I think I see the section, though I'm not sure.  Below is one of the sections:

SERVER["HTTP_ACCEPT"] image/gif, image/x-xbitmap, image/jpeg, image/pjpeg, application/x-shockwave-flash, application/xaml+xml, application/vnd.ms-xpsdocument, application/x-ms-xbap, application/x-ms-application, application/vnd.ms-excel, application/vnd.ms-powerpoint, application/msword, application/x-silverlight, */*

Does the above mean that MS access won't work?  I noticed similar results in the "HTTP_ACCEPT" and the "_ENV["HTTP_ACCEPT"]" sections.
0
 
alain34Commented:
You need to look for:

odbc
ODBC Support enabled
Active Persistent Links  0  
Active Links  0  
ODBC library  Win32  

Directive Local Value Master Value
odbc.allow_persistent On On
odbc.check_persistent On On
odbc.default_db no value no value
odbc.default_pw no value no value
odbc.default_user no value no value
odbc.defaultbinmode return as is return as is
odbc.defaultlrl return up to 4096 bytes return up to 4096 bytes
odbc.max_links Unlimited Unlimited
odbc.max_persistent Unlimited Unlimited
0
 
jjrr007Author Commented:
I don't see anything written about odbc on that page.  As it stands right now, it doesn't seem like I can query the Access database.  

Can I some how enable ODBC (so I can query MS Access) by changing the INI file or some other method?  
0
 
alain34Commented:
changing the ini file is only one aspect. Are you doing that on your local machine or on an hosted account?
If you change the ini file, you also need to install the extension in the ext folder under your php installation
0
 
jjrr007Author Commented:
I am using a web hosting account.  How do I make the changes?
0
 
alain34Commented:
You need to speak to the hosting company sadly. It is never possible to install new extension without speaking to them.
I was under the impression you wanted to do that locally...
0
 
jjrr007Author Commented:
Thanks a lot for your time and expertise alain34.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now