Access Synced with MySQL

Databases
1

Hey guys,

I’d appreciate an answer to this query if possible,

If I use Microsoft Access to create a desktop application. Is there any way I can sync the Access database to an online version using MySQL so if I am away from home then I have easy (if limited) access to it and then any changes made either on the desktop or online versions are synced to the other?

2

There’s no easy way, you have to write a program that sits in between, looks at all the data in one database, looks at all the data in the other, and writes appropriate queries in whichever’s language to make the updates. It’s not trivial.

Some people have tackled it before, if you search along the lines of “mysql access sync” you’ll find a program or two, but they’re not free.

If you’re still in the planning stages, why not use the online mysql database for the application itself?

3

Thanks, It’s not for anything major, just a small home database to track certain items. Thats all.

Thanks though!

4

MySQL is free and easy to use, Access is not free and complicated if you run into problems. Just sayin’.

5

Yeh, I understand.

Ok, If I make this little system in Mysql, What would be the best way of having a copy on a server whcih I can access from anywhere and a local copy on my home pc and syncing them?

6

Well if you’re still going down the syncing route you could run a local instance of MySQL and sync that to an online MySQL database to use in remote instances. That’ll be much easier than trying to sync MySQL with Access.

However, I still think you should take Dan’s advice and just make your desktop application interact with the online MySQL database so there’s no syncing required. Is there a need to have the desktop application work offline that you can’t use an online database?

7

Ok. I understand your viewpoint but would like to look into these options before I start. I currently run WAMP on my local computer. What I need advice with is how I sync my remote db to my local database. Both would be in MySQL. Thanks for your advice so far guys!

Thanks

Regards,
Neil

8

You don’t need to sync anything, use just one database, have the app point to the remote db.

Syncing one way is easy (use replication, or mysqldump the whole database), syncing two ways is hard.

9

I’m not great with the MySQL commands but try a Google search for how to dump your database contents which should create a .sql file for you. You would be dumping data from your local database. When you want to “sync” or upload to the online database you would first need to drop the existing database and then execute the .sql file that you just created.

  1. Dump local database to create .sql file.
  2. Upload .sql file to online file system via FTP.
  3. Drop online database.
  4. Execute .sql file on online database server to create clone of local database.

You could probably create a cron job to handle this but I’m not going to be of much help with the code - sorry. Bonus is that there’s a ton of people on here who are a whole lot smarter than me and can probably help you with the code or even give you a better method of doing this.

10

Thanks guys

11

Instead of upsizing to MySQL, why not upsize to MS SQL Server Express? That’s free also and you could use Access build in Upsize wizard.

Is has a maximum size of 4 GB, but since the max size of Access is 2 GB, this shouldn’t be a problem…