Database synchronize between local and remote
- Thread starter henentao
- Start date
dfenton
AWF VIP
- Local time
- Today, 17:00
- Joined
- May 22, 2007
- Messages
- 469
How I synchronize my local Access database with web database which is located in my domain website?
In Oracle, we could set a trigger or thread, but how about Access?
Well, it entirely depends on what you need to do.
Is the Access database completely a slave of the server database? That is, can you just replace the Access data with the Oracle data?
If so, it's reasonably easy to accomplish, because all you have is an import operation (you discard the old Access data and start over with an empty MDB).
But if the data needs to be edited in both locations, it's pretty hard and requires a lot of work.
Now, one issue that occurs to me is that the easy way to do all of this is to link directly to the Oracle database. But since it's on a web server, that's not possible (unless you can get VPN access to the web server).
So, you're basically in a position where you need to have Access to tell the web server to write a dump of the data. The easiest way to do this is to run an export to tab-delimited CSV files. You would code this in the scripting language installed on your web server. You would call it from Access as you can any URL, and the results from your script show in the web browser.
Once you've told the server to export the data, you would then run an FTP script to download the CSV files and then import them into your database.
I've done this with MySQL and Access more than once (though I was only handling the Access end of it, not the web server part).
The key insight:
Access can execute a script in a publicly accessible location on the web server and the script would write the data dump to an FTP folder. Once that's done, Access can do the rest.
What you could also do is use an ETL tool. The tool will allow you to transfer the Access database to the web-based MySQL. There are plenty of ETL tools but you could use an open source one: it is free to download. Try to look at Talend Open Studio, you can try it out and decide whether or not it fits your needs.
