Access database and MySQL synchronization

Question

I have a client that has a very large internal system that uses Access, which is used internally to process almost all of the company's data. They want to have a web interface for client data in this database and will work on another server. Given access restrictions, the front-end module is likely to use MySQL.

Because of this, there is a data synchronization problem. It is not necessary to be in-second, even daily would be nice, but I'm really not sure about the good tools for this.

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

Is there any practical way to do this?

Answer 1

I would ask an experienced Access developer to increase the Access application to use SQL Server Express for data instead of MySQL. I would choose SQL Server Express over MySQL, it is very likely that it is better to work with Access, and you will most likely find lost additional online support for a combination of MS Access and SQL Server than for MS Access and MySQL.

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

One of the factors is that the client wants the data to go from the website to the database or just publish the data on the Internet? If you just publish on the Internet, you may be asked to update your web database using queries. If in both directions the situation starts to become more complicated, and switching to SQL Server for the future will be the best option.

Answer 2

my steps to “sync” are more like ultra-fast backups

1.- Windows converts your database with this

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

it quickly converts most of the database to YOURDATABASE.sql

2.- on linux (I use Ubuntu)

since you should do this, I often recommend a script for corrections.

my example:

 #!/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 

The zenity program is installed by default in ubuntu, you need "fromdos" to return the carriage in windows

then: 3.-mysql -u theuser -pthepass

mysql> source YOURDATABASE.sql

and you are done in 5 minutes.

Answer 3

From Access, you can quite easily copy content, for example, using a button that launches an SQL query that copies data to an MS-SQL database on the Internet.

I would not go to MySQL if you do not need it, since this introduces problems with character encoding, copying in MS-SQL is more reasonable.

Be very careful, of course, you will have 3 systems: 1 access system, 1 web system and 1 system for copying data. This scenario leads to higher maintenance costs.

Answer 4

If the web database is available on the local network where there are Access users, it would be more appropriate to increase the amount of feedback to the server database and have one database. But most likely this is not the case, since the web server is probably not local (and usually this is good).

If I understand your comments, this is a clear relationship between masters / subordinates, and the Access database is the lead and the website database is the slave. In this case, you should be able to simply replace the website database on a regular basis. There are several ways to do this:

• if you can access the MySQL port over the Internet, you can use ODBC to easily export tables to MySQL via ODBC. I'm not sure that you will have to drop every table in MySQL before exporting, but you will find out as soon as you try to start exporting.

• if you cannot get a direct connection to a remote database, then one of them is to start a local MySQL instance, export to it, then upload the database to an SQL script, upload it to the website and run it to replace the existing database. I did it and it is not as difficult as it seems. If it does not need to be automated, the easiest way is to install phpMyAdmin at both ends and use it. If it is necessary for automation, this is another problem, and I do not know the MySQL commands, but I am sure that it is not so difficult to find the commands.