What is the best way to synchronize data between MS Access and MySQL?

Question

Hello,

I have an access database on a windows machine, which I must import into mysql on a linux webserver. At the moment the access dabatbase table is exported as a text file, automatically copied with ftp, and then loaded into mysql.

Is there a better way to do this, perhaps using an ODBC connection or something else?

What is the best way to limit copying information that is already present, ie to only transfer records that are in the access database but not yet in the mysql one.

The access database is handled by another program, and it would be best if I did not have to makes changes to it, or even open it. There is no possibility of needing to import updated records from the access database. The mysql database will be the main source, and some records will be deleted or changed. I only want to import records that were never in the mysql database, not restore the ones that were purposefully deleted.

Answer 1

If you do want incremental updates, the way to do it is writing a script that does connect to both databases (through ODBC at least on the Access side) and compare all tables. The advantage of copying the whole thing is that you are sure not to forget anything, downside may be the size and the fact that the mysql will not be available during the reload.

Answer 2

I would do the following:

Dump all your Access data into a temp table in MySQL, which will be your "master" source of data for a moment. You should be able to do this by setting up an ODBC data source pointed at MySQL, right-clicking on the Access table, and hitting "Export".

1. Run an UPDATE script in MySql to update matching records
2. Run an INSERT script in MySql to insert new records
3. (Optional, depending on whether this is what you want): Run a DELETE script in MySql to delete records that AREN'T found in the newly imported Access table.
4. Drop the temp table in MySql.

It is possible to do all of this from inside MS Access, via linked tables. But the performance won't be as good, and it will require changing the Access statements when/if your MySql tables change.

This answer may be helpful to you, too.

Answer 3

To help transfer only changes, I suggest you add a new table to your Access database called something like RecordChanges.

The table structure would be as follows:

RecordChangeID (int) - Primary Key (Autonumber)
TableName (varchar(250)) - Name of table that changed
RecordID (int) - ID of the record in that table that was added / modified
RecordAction (char(1)) - 'A' if add, 'M' if modified or 'D' if deleted

Note - by adding a user ID and other details to this you could have a nice audit trail.

This is the painful part - but I would create a subroutine in your application to add a record to this table every time a record is changed in a table you want to synchronize with your MySQL database.

Once this is done I would create another table with only one record, called ExportStatus, with the following structure:

LastRecordChangeID (int) - ID of the last Record Change 
                           you exported in the Record Changes table

Then create a subroutine to go through all Record Changes since the last export (you retrieve this from your ExportStatus table) and generate SQL statements to update your MySQL database, being sure to update your ExportStatus table when done. You could delete all the RecordChange records that were successfully exported, or leave them in as an audit trail.

Before implementing this, you would need to do an initial synchronise the way you are currently doing it.

Answer 4

Why not link the tables through ODBC as suggested by Keltia, and then use a series of queries to add records that are missing and to update changed records. The queries can be run through VBA. ADO will also work well with MySQL and Access.

Some notes on code used in Access:

   Set db = CurrentDb
   strSQL = "Insert INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
   & "Select AnyField As NameOfMySQLField FROM AnyAccessTable;"

   db.Execute strSQL, dbFailOnError
   End Sub

-- http://forum.lessthandot.com/viewtopic.php?f=95&t=3862

Answer 5

See my answer in

Access DB5 to MySQL automatically