Database Synchronization Advice Access and MySQL

Zack
Zack used Ask the Experts™
on
Heyas,

I need some advice I have two databases one is MySQL and the other is MS Access the Access one is being phased out however some it functions are yet built into the MYSQL db front end parts as of yet. My query is what would be the best wat to Synchronize changes across both databases I think of dumping the data from both db into a spreadsheet. That seems cumbersome to the extreme I would have to have fair bit of VBA code to pull that off, if a I could pull that off.

Any other ideas of how I might get around this problem.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011

Commented:
It is possible to create linked tables from the Access front end to the mySQL database. .The Access app could directly update and read from the mySQL database.
ZackGeneral IT Goto Guy

Author

Commented:
I've explored that option too messy any other methods you know of?
Top Expert 2007

Commented:
>>too messy

In what way?
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

MIS Liason
Most Valuable Expert 2012
Commented:
Yes, I am also not quite sure how linking the SQL tables into Access would be "Messy"?

"My query is what would be the best wat to Synchronize changes across both databases "
"Your Query"?
Can you post the details of "Your Query"?

"Synchronize changes "...
Depending on your exact definition of "Changes", any solution to do something like this may be inherently "Messy"

You can have a query that simply copies new records, but that won't detect "Changed" records, ...(unless you have a Date/Time stamp process in place for this)
What about deleted records?
What about conflicting changes?
What about archived records?

Any system to do this will need "rollbacks" in case some part of the transfer fails.
Do you have this in place yet?
You will also need to have rock-solid error handling in all of your subs.

In other words, Synchronizing databases sound easy, but is actually quite involved if you want to do it right.

Your did not state what version of Access you were using, but if you are still using the 2002-2003 format, you can also investigate "Replication"

So as you can see here, you will have to very clear and specific as to exactly what you need to do here.

JeffCoachman
ZackGeneral IT Goto Guy

Author

Commented:
Cheers for the info replication looks to be exactly I what I might use.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Great, Thanks

Just keep in mind that Replication will not be available if you upgrade to the .accdb format.

Hopefully by then you will have moved the data fully to MySQL (or MS SQL Server)

;-)

JeffCoachman

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial