Having MySQL pull data from MS-Access file into MySQL - HELP

[ow-phil]

Hey all,

First of all, in the situation which is described below, the software for the client is a third party software which is written in the .Net framework and saves its information into an MS-Access Database. When the user of the instore application presses "Update" the software uploads a copy of the MS_Access Database onto the webserver. The third-party software can not be recoded, changed or modified in anyway. The client runs 32 stores off this setup and each Store has their own website.

That said...

I need to be able to have an automatic update setup having MySQL pull the data from the uploaded MS-Access DB on the webserver.

I was thinking of running a Crontab to check the Access File to see if the timestamp has been changed since the Cron had last seen the file (say every hour). But how do I get the information in. There are only three tables in the MS Access DB to go in.

Now some other challenges:

• One of the tables in the MS Access DB has Blobs with photos in it. These need to be transferred.

• The process for the pull into MySQL has to be automatic

Thats about it. Anyone with any ideas??? HELP! :rolleyes: 😕

[barand]

I've just experimented with simultaneous connections to access and mysql and this works. Should get you started.

$odbc = odbc_connect('aptaccess','admin','');

$mysql = mysql_connect('localhost');
mysql_select_db ('test', $mysql);

mysql_query('delete from receipt');

$sql = "select * from receipt";
$res = odbc_exec($odbc, $sql);
while (odbc_fetch_into($res,$row)) {
       $flds = "'" . join("','" , $row) . "'";
       $res1 = mysql_query("INSERT INTO receipt VALUES ($flds)");
}

[ow-phil]

What components need to be installed to do this, and how will it react to jpg files in Blobs.

How does it know what fields to dump where?

TIA

Phil

[barand]

To connect to an access db you need either ADO or ODBC

As for the fields, I set up identical tables (except I didn't specify any auto_increment to preserve key values) then just inserted every field in same order. If the tables are not identical then you'd need to specify the destination fields in the insert query.

hth

[ow-phil]

Is there any problem with doing this with Blob Data (images). All images for this 3rd party software is stored in the Access Database.

This has been very helpful.

Many thanks...

Phil

[barand]

I'm afraid I didn't have any blob images to try it with. The only thing I can suggest is set up a small db and try it.