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

Question

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

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