Is there a way to sync (two way) tables betwen a mysql server and a local MS Access?

Question

Help me figure out a solution to a (not so unique) problem.

My research group has gps devices attached to migratory animals. Every once in a while, a research tech will be within range of an animal and will get the chance to download all the logged points. Each individual spits out a single dbf and new locations are just appended to the end (so the file is just cumulative).

These data need to be shared among a research group. Everyone else (besides me) wants to use access, so they can make small edits and prefer that interface. They do not like using MySQL.

The solution I came up with is:

a) The person who downloads the file goes to a web page, enters animal ID into a form, chooses .dbf file and uploads to a mysql database on the server (I still have to write php code to read the dbf and write sql insert statements from it).

b) Everyone syncs from their local access database to the server. (This is natively possible from access but very clunky).

Is there a tool (preferably open source), that can compare a access table to mysql table and sync the two (both ways)?

Alternatively, does anyone have a more elegant solution?

The ultimate goal is to allow everyone to have access to the most current data on their computers using their preferred database app.

Answer 1

It is possible to use ADO or ODBC with Access and MySQL to run queries to comp[are and update. It is simple enough to automate this through VBA.

This may be of interest: http://stackoverflow.com/questions/442915/what-is-the-best-way-to-synchronize-data-between-ms-access-and-mysql/443003#443003

Answer 2

An obvious hint for implementation:

1. Use UUID() in MySQL and GUID in Access as unique identifiers
2. Store timestamp of the last change in synchronized rows

That way you will easily distinguish record updates from new records and will be able to merge changes even yourself using ODBC.

Answer 3

Is there a tool (preferably open source), that can compare a access table to mysql table and sync the two (both ways)?

Have you looked at this SuperUser post?

Also, have you Googled? I Googled "access compare databases" and the third link on the page lists several different software packages for comparing databases.

It seems that some allow you to synch data and others don't.

For what it's worth, I've programmed multi-master synch between Access and MySQL and it's just not that hard, particularly if you can run it from Access with ODBC linked tables pointing to the live MySQL data tables. Particularly in a case when what most of the new data in Access is new records, it oughtn't be that complex.

On the other hand, what I consider not that complex might be too daunting for you -- I don't know your level of familiarity with SQL, DAO and VBA.