Page 1 of 1
Automated MS Access to MySQL
Posted: Tue Feb 21, 2006 1:15 am
by SBro
Although not hard in itself (getting data from access -> mysql), the way the client wants it done in this situation is presenting some problems. They pretty much want it automated going from a local ms acess db to a mysql server on the web. This is what is required:
- get data from ms access db
- edit / format it (ie. strip out commas and/or place quotes around each field)
- export to csv
- send to web server
- cron job which periodically checks for new csv file and acts accordingly, places data in mysql db
I know this isn't specifically a php question, but the only idea I've come up with so far is some VB code within the Acess db, that will do all of this including sending to the web server. As the client literally doesn't want to do anything more then press a button in access / run a batch file or equivalent, it is proving quite a headache for me (esp with my limited VB knowledge

.
Anyway I guess what I'm looking for is for those of you with some Access knowledge, whether you think my method would be the best way, or is there some alternative way that I've looked over? Can an access db be uploaded and worked with by php functions before placing in mysql? Although this wouldn't be ideal given the size of the Access DB and the constant changes ie. uploading. Thanks for any suggestions.
Posted: Tue Feb 21, 2006 1:22 am
by josh
Posted: Tue Feb 21, 2006 1:26 am
by SBro
Yes I believe it does, however as the database will be re-uploaded every hour, I'm trying to stay away from this option as uploading a DB 40Mb+ won't be much fun for the client. Which is why I'm just trying to get a small csv file out of it, however if the ODBC way turns out to be the "best" way maybe this will be the only option

Posted: Tue Feb 21, 2006 1:34 am
by josh
That doesn't make sense, keep on file the primary key of the last record you have, asking it to give you everything with a primary key > that record (assuming your primary key is sequential), you could do anything you could with CSV and more, and without the 'middle man'.
Posted: Tue Feb 21, 2006 2:27 am
by raghavan20
i do not so much technical knowledge to deal with this problem. but why do not you want to entirely migrate the access db to mysql,,,,why are you keep on reading from access to mysql. is it not possible to have a master and slave concept...just curious
or is it not possible for you to read the log files in ms access and find changed values and just generate csv file for it???
Posted: Tue Feb 21, 2006 6:43 am
by SBro
jshpro2 - I'm not sure I know what you mean, I know I can get just new data through a query or similar and export to csv and thus just have new data in the csv file. If you are talking about getting the new data placing in a new (and thus smaller) .mdb, uploading that and then merging that back with the existing .mdb then I didn't know that was possible.
If I haven't made it clear, the access database will always be modified locally, when it goes to the web server (in whatever format mysql etc.) this is to only do some extended functions (not availalbe in local version) that the client wants possible through the web.
raghavan20 - master and slave? If I do the csv method it will only be changed values.
Posted: Tue Feb 21, 2006 9:33 am
by josh
Here's an easier way to grasp this. All your rows have primary keys that identify that row. Add a field that is called reSync or something of the like and default it to 1. Write a macro or something in access so when your user updates any row it changes that to a 0. All new rows created get a 0 as well. You connect every 5 minutes or what not and ask access for just the 0's via ODBC. After you download them you ask access to update all those to 1's so you don't re-download them next time.
Posted: Tue Feb 21, 2006 9:50 am
by RobertGonzalez
Your client wants a button that they can press that will automatically export your local Access DB to a hosted MySQL DB?
Do you have a local server setup on the machine that houses the Access DB? You could write an application that connects to both data sources, and, at the request of the user, queries all the data in Access, creates new tables in MySQL, inserts all the data, checks to make sure the insert worked, then deletes the old MySQL tables and renames the new MySQL tables to their original names. This can all be done rather quickly and behind the scenes, as long as you can connect to both the local Access database and the hosted MySQL database from your local server.
Posted: Tue Feb 21, 2006 9:53 am
by josh
Everah wrote: You could write an application that connects to both data sources
or you could just use odbc
Posted: Tue Feb 21, 2006 3:50 pm
by SBro
Jshpro2, I'm still having trouble grasping what it is you mean exactly. I understand that part about setting a status or whatever to get just the new details. However the actual connecting and accessing I'm having trouble with. I understand the odbc functions in php, but I don't understand how you want me to get to the access db. How would my php code with the odbc functions access the ms access db ? The php code would be on the web server, so short of uploading the access db to here (a linux server which will create problems in itself), I don't see how it is possible? I apologise for not being on the same wavelength

But thanks for your responses.
Everah, what would this "application" be written in? Would I do it in VB in access ?
Posted: Tue Feb 21, 2006 4:27 pm
by RobertGonzalez
Ideally you would code the interface in PHP. I have set apps up before that read from multiple data sources and write to multiple data sources. The thing to look for is being able to connect to your Access database. Access is not a server, so it would have to be on the same machine as the web server OR you would have to put a web server on the machine that houses your Access database. This is easy enough to do.
The next thing is that you will have to be able to connect to your MySQL server. If it is remotely hosted you will need an IP address or server name so you can connect to it. If it is on the same machine as the Access DB (which this thread says it is not) then you can just connect to localhost.
Once your Access DB is on a web server, you can develop a PHP script that will connect to both your Access DB and your remote MySQL DB Server. Have the script grab all the information from the Access database and then insert the data to the remote MySQL database. I would probably tend to go with complete inserts, so it would involve creating a few temp tables (one for each table in Access), uploading the data from Access to MySQL temp tables, then after confirming that the data gathering and inserts worked, renaming your temp tables to override the original tables that were there.
There are probably a lot more steps to this than are in this thread. I am sure there is probably a better way to accomplish this task, also. But this is an option.
As another option, you could always buy a license for
SQLYog and set up a
SJA to periodically DTS the Access Data to MySQL using the software's built in import/export tools. Have a look at
this information to see if it would help you.
Posted: Tue Feb 21, 2006 5:11 pm
by josh
With odbc you just connect up to the computer that is on your network running access and you run SQL against it. Much easier then getting into that other stuff, if you cannot connect remotely due to firewall issues or anything like that you can always have a command line PHP installation running on the same machine that is running access and schedule a windows task for a script that will push data to the main server, when the server acknowledges receiving of the data the local script updates all those rows as synced.
Evarah's method is completely valid also I just feel that odbc would save you a ton of time
Posted: Wed Feb 22, 2006 3:01 am
by SBro
Thanks for the help guys, I appreciate it, I will have a play with both methods and see which is better suited.