SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Access Synced with MySQL

    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. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, It's not for anything major, just a small home database to track certain items. Thats all.

    Thanks though!

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    MySQL is free and easy to use, Access is not free and complicated if you run into problems. Just sayin'.

  5. #5
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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?
    TAKE A WALK OUTSIDE YOUR MIND.

  7. #7
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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. #8
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    Visible Ninja bronze trophy
    JeffWalden's Avatar
    Join Date
    Sep 2002
    Location
    Los Angeles
    Posts
    1,709
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    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.
    TAKE A WALK OUTSIDE YOUR MIND.

  10. #10
    SitePoint Wizard rctneil's Avatar
    Join Date
    Jun 2005
    Posts
    1,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys

  11. #11
    SitePoint Zealot
    Join Date
    Jan 2007
    Location
    Almere, The Netherlands
    Posts
    160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rctneil View Post
    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?
    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...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •