How to Implement Synchronization/Replication

One Star

How to Implement Synchronization/Replication

Hello,
I've searched for details on how to implement synchronization every which way I can think of and haven't come up with much. I looked at the manuals as well. I'm looking for a guide/tutorial for this scenario:
- We have an app that runs on Access
- I want to use TOS to watch certain tables for changes and write them to a MySQL database
Synchronizing inserts I can undrestand: just compare the primary keys in Access to MySQL and insert those that are missing
However, what about updates? Access doesn't have triggers that would allow us to fire a procedure when an update is processed. The only way I can think of would be to compare ALL the fields in EVERY ROW of the tables in Access and MySQL and update fields in MySQL that have changed. Seems like that's going to be a pretty hefty query to run every 10 seconds or whatever we can handle as far as latency.
Am I missing something?
Thanks,
Scott
One Star

Re: How to Implement Synchronization/Replication

No, not missing anything.
I don't think you are going to find any out of the box solution that won't require some modifications to your schema/app. You could definately do this with talend, but your access data model and perhaps the application would need to be modified heavily to track these changes (lack of triggers except in forms, etc.).
Without knowing all the details a couple quick recommendations:
Move to MySQL and link Access if you need the front end. Compatibility might not be 100%
Upsize access to sql server and as above use linked tables. Should be transparent to the application and some short term benefits like performance and larger db capability.
Also take a look at using Talend's SCD components for sql server and or a specialized solution like SymmetricDS (LGPL) which supports database agnostic replication. http://symmetricds.org/
--
Matt.
One Star

Re: How to Implement Synchronization/Replication

Thanks for the input maxsom.
Your suggestions about moving to a robust RDMS are right on. We've attempted both and had some issues. But regardless, in the next few months the app will officially support SQL server anyway, so in that not too distant future we won't have to monkey with Access.
When dealing with a fully featured database system that supports triggers, I'm thinking that the way to handle replication would be to create a secondary table that is written to whenever an insert or update is processed that Talend can read and take appropriate action on. Or I suppose a field like "replication_status" (which would be null for no change or INSERTED or UPDATED) could be added to each table that needs to be replicated. Talend would select all records where replication_status IS NOT NULL and process those records, setting replication_status back to null after the process is successful.
Am I am on the right track?
Thanks,
Scotty