Synchronizing Access DB and MySQL

Question

I've got a client who has a very large internal system using Access, which is used internally to handle virtually all company data. They want a web front-end to the customer data in that database, and would be running on a different server. Given the limitations on Access, the front-end would likely use MySQL.

Which leaves the issue of synchronizing the data. It doesn't need to be to-the-second, even daily would be fine, but I'm really unsure of a good means of doing this.

Given the scope of the existing system, it would be a disproportionate amount of time and work to move the entire system to another database such as MySQL.

Is there any practical way to accomplish this?

Answer 1

I would get an experienced Access developer to upsize the Access application to use SQL Server Express for the data instead of MySQL. I would choose SQL Server Express over MySQL is it is very likely to work better with Access and you'll likely find a lost more online support for the combination of MS Access and SQL Server than you would for MS Access and MySQL.

This work can take several weeks or more for an experienced person. And of course the larger the system the longer this will take.

One factor is if the client wants data to come down from the website to the database or just publish data to the web? If just publishing to the web then the suggestion of updating the web database using queries could work. If both ways then things start to get trickier and upsizing to SQL Server for the future would be a better option.

Answer 2

From Access it is possible to fairly easy copy content, for example using a button that runs a SQL query that copies the data to an MS-SQL database on internet.

I wouldn't go for MySQL if you don't have to as this introduces character encoding issues, copying to MS-SQL is more sensible.

Be very wary of course, you are going to have 3 systems: 1 access system, 1 web system and 1 system for copying data. This scenario is and introduces higher maintenance costs.

Answer 3

my steps to "sync" more like superfast backup

1.- in windows convert your database with this

http://www.bullzip.com/products/a2m/info.php

it converts fast most of database into YOURDATABASE.sql

2.- in linux (I use Ubuntu)

since you must do this often i recomend a script for corrections.

example of mine:

#!/bin/bash
#edita tablas
szAnswer=$(zenity --title="YOURDATABASE" --entry --text "some text" --entry-text "");
fromdos $szAnswer;
sed -i 's/DATETIME\ DEFAULT\ .*/DATE,/g' $szAnswer;
sed -i 's/DATETIME/DATE/g' $szAnswer;
sed -i 's/FLOAT/DECIMAL(10,2)/g' $szAnswer;
sed -i 's/(NULL,/(\ ,/g' $szAnswer;
sed -i 's/ NULL);/\ );/g' $szAnswer;
sed -i 's/,\ NULL,/,\ ,/g' $szAnswer

zenity program is default in ubuntu you need "fromdos" for carriage returns in windows

then : 3.- mysql -u theuser -pthepass

mysql>source YOURDATABASE.sql

and you are done in about 5 mins.

Answer 4

If the web database is accessible on the local LAN where the Access users are, it would make more sense to upsize the back end to a server database and have a single database. But that's likely not the case, as the web server is probably not local (and this is usually a good thing).

If I'm understanding your comments, this is a clear master/slave relationship, with the Access database being the master, and the website database being a slave. In that case, you should be able to simply replace the website database on a regular basis. There are a couple of ways to do this:

1. if you can get access to the MySQL port across the Internet, you can use ODBC to simply export the tables to MySQL, via ODBC. I'm not sure if you'd have to drop each table in MySQL before exporting, but you'd find out as soon as you tried to run the export.

2. if you can't get a direct connection to the remote database, then one option is to run a local MySQL instance, export to it, then dump the database to a SQL script, upload that to the website and run it to replace the existing database. I've done this and it's not as difficult as it sounds. If it doesn't need to be automated, it might be easiest to install phpMyAdmin on both ends and use that. If it needs to be automated, that's a different issue, and I don't know the MySQL commands, but I'm sure it's not that hard to locate the commands.