Automate data flow from MS Access to MySQL

I need to automate a data flow from MS-Access to MySQL for an insurance company.
Users will continue to work in MS-Access, but the changes done through the Access forms should take effect in MySQL as well.
This is the decision of our web analyst, because the web system will not be able to replace all MS-Access functionalities that are currently used, and the data management will currently continue to be done in MS-Access (this was the decision).

I see two options, and one of the options contains my question:

• Automate the data upload through VBA (when changes occur via the form, an Ajax request sends the data via GET parameters to a PHP script on the server that performs the necessary manipulations on the data before storing them in MySQL. Immediate upload into MySQL could have been possible as well, but it was decided not to do this);

• Send a simple request to a PHP script, and perform all actions in PHP: Get records from MS-Access in PHP, manipulate the data in PHP, and store them to MySQL in PHP. (My preference. I will need to create a script that runs automatic migrations every night as well, so I will need to do this anyway).

The MS-Access database is stored on our local network, ie. a different server than where the PHP script runs (which is an external web server).
I would prefer to manage everything in PHP, but (my question):

Is it possible to connect to the host of our local network and the host of the external server simultaneously, querying data from MS-Access, manipulating them with PHP and inserting / updating them in MySQL at the same time, in a single script?

Answer

Is it possible to connect to the host of our local network and the host of the external server simultaneously, querying data from MS-Access, manipulating them with PHP and inserting / updating them in MySQL at the same time, in a single script?

Yes, but you don’t really need a PHP script, access can work with MySQL through an ODBC connection or by using ADODB connections. It would be easiest to modify the forms to work with the MYSQL back-end.

The advantage of the ODBC connection is you can insert from the access DB to the MySQL DB in a single query. Using ADODB connections you can take an access query, and do a batch insert (you’ll have to do it through a string), but that can also be relatively quick since you only connect to the MySQL db to send the INSERT command, the rest is string manipulation.

If you are going to go with an asynchronous bulk update through php

<?php
$dbName = $_SERVER["DOCUMENT_ROOT"] . "productsproducts.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$db = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");

That code will allow you to connect to the access back-end (credit to http://www.sitepoint.com/using-an-access-database-with-php/) note that the queries will be parsed by access, so you should format them the access way.