Bi-Directional Synchronization of database changes in mySQL and Access

April 19, 2006 9:33 AM

How can I implement bi-directional synchronized table changes in a mySQL database and Microsoft Access?

I have webhosting with Dreamhost which has PHP and mySQL. I've created a mySQL test DB, installed mySQL Administrator locally and created the mySQL ODBC connection. I've made the ODBC connection to Access and brought in a sample table.

If anything changes in either the sample Access table(s) or the mySQL DB table(s) (i.e. adding a field or data population), I would like those changes to populate to both locations.

My primary reason is: I am much more familiar with Access and would like to continue to use it for queries (until my SQL skills are more refined). However, when I implement a web form and collect data, I understand that data will go to my mySQL database (correct?).

I do understand this exists: Access2mySQL Sync, but am looking for free alternatives.

Thanks for any input!

posted by mic stand to computers & internet (6 comments total)

mic stand, unless you plan to keep one copy of your DB in mySQL and another in Access, you don't need the utility you've described. It wouldn't be good practice to do this as a constant operational situation anyway, particularly if your database grows to any size, or you begin to construct SQL statements of any appreciable complexity, as the differences in SQL standards implementation between the Jet engine used by Access and the mySQL engine can create problems for the conversion utility, and because the Jet engine is notoriously susceptible to corruption.

What I would strongly recommended to do, instead, is to make local backups of the mySQL database on your Windows machine, and do this on a regular production schedule (at least daily, if you are collecting any volume of data on the Web site). Then install and run mySQL server's free Windows version as a local server. This will give you some protection from data loss due to corruption of the database up on the Web server, and is something you need to do anyway, for security. You can create another OBDC connector to the local copy of the database, and run it under the locally installed copy of mySQL server on any Windows machine. The memory and resource footprint for running mySQL server and Access together on the same machine at the same time is pretty minimal, if you don't have tables running to millions of rows, and you should be easily able to do this on any Intel machine with more than 128Kb of memory. You just start mySQL server on your local Windows machine before starting Access, and the whole thing can be done with a couple of mouse clicks, once you have mySQL server installed, and the OBDC connection set up.
posted by paulsc at 10:34 AM on April 19, 2006

Dreamhost has PHPMyAdmin, which is a web-based interface to your MySQL databases. It doesn't have drag-and-drop like Access does, but otherwise is quite an easy way to interact with MySQL databases. If I were you, I would try to use PHPMyAdmin rather than trying to set up this Access--MySQL scheme you describe.
posted by jellicle at 11:38 AM on April 19, 2006

paulsc - how does that solve the problem? how would changes to access be propagated through to the server's mysql database?
posted by andrew cooke at 11:54 AM on April 19, 2006

Thank you all. Jellicle, I have started working in PHPMyAdmin since posting this, however, I am having a difficult time importing .csv files. I save a .csv file with the required data (firstname, lastname, email...customerID auto-populates). The data goes into the first field only. CustomerID autopopulates correctly, though. (lastname and email are blank).

And my apologies if this question is a tangent from the first question.

By the way, I installed mySQL query analyzer to do the tasks I originally was requiring of Access.
posted by mic stand at 12:40 PM on April 19, 2006

andrew cooke: what I've suggested simply uses the Access GUI via ODBC connections as a client for mySQL, both locally and up on the Web server, and I hope I made that point adequately in my answer above, without confusing the OP. There is no attempt to maintain "bidirectional" communication between two databases with potentially different state implementations, which is pretty likely, in my experience, to result in corruption to the Access database if the Web server's database is getting frequent updates anyway.

But there is a local copy of the database which can be used for query development and testing, and which will be a ready backup for the Web server database, in case something does happen. If the OP's absolute need is for some kind of "bidirectional" table update, I'd suggest the use of something like SQLyog. But I'd also strongly suggest that if the OP is going to be doing table design changes on live databases that are synchronized, that he do something to assure he has a consistent copy of the production DB before such changes are made, especially if he using simple MyISAM tables (which don't have the transaction commit logic protection of InnoDB tables).
posted by paulsc at 12:43 PM on April 19, 2006

Paulsc, You are absolutely right. I don't want auto- or manual changes to immediately and constantly synchronize, I just wanted the most-up-to-date data to be available when I go into Access.

Dabbling with phpMyAdmin has helped meet some of my needs and mySQLquery Analyzer does also. Getting the data into the tables is my next task (manually and ultimately by web form submission). This is a recreational project for now so nothing is dire nor will deleting the DB...tables..so forth, and starting over be a problem either.

Just gathering data and getting the test foundation set.

Thanks again!
posted by mic stand at 12:49 PM on April 19, 2006