Using PHP to convert Access to MySQL

tickled_pink

Well, this is a humdinger for me. A friend for who's business I'm doing some PHP coding has only now, two weeks after I've started, thrown up something that they're already doing and now want some way of integrating it into the project.

They presently go around clients with a laptop and use a form created in MS Word to take down clients' details. Ideally they would like some way to transfer the data collected to the server at the end of the day and be able to run a script that will take the information and transfer the relevant data to the various MySQL tables.

I toyed with various ideas. PHP was out of the question on the laptop as it would have been too awkward for them to set up, along with MySQL. I then considered writing something in C# or Java (definately a worst case scenario) for them to enter the details and it would store the info into an easily accessible format for the script.

But then I thought about MS Access. The only problem is, I have no idea as to how the data is stored. That would be an easy solution to implement but I need some info on how Access stores its databases and tables before I can set about writing a PHP script to transfer the data to MySQL.

Anyone have any ideas or pointers?

bastien

its simple enough to export from access to mysql (google it and there are ready made solutions) all that happens is the export from access to a CSV file and that gets imported into mysql thru the mysqlload utility...

its the need to connect to every laptop in turn that throws me....kinda silly way to do it...but what needs to happen is that once the laptops are connected back to the network, a function in access can be written to export the data to a csv file and even emailed to the server or copied or whatever....then a cron job could take the data and load it automatically...

hth

tomkleijkers

Yeah, pretty complicated...

I have the same problem, having an MSAccess database and want to use it in MySQL. It works while using the Access2MySQL programs, but I want it automated too because the Access DB will be updated each month.

I'm planning to write a script for it, but i'm too busy right now....

If you find a solution, please email me😉

ow-phil

I have been working on something too. I have a similar problem with importing information from MS-Access to MySQL. The problem is the AccessDB is FTP'ed to the website by a third party program. I need for an automated solution to run on the server to pull the data into MySQL on a twice-daily basis. Speed isnt an issue.. just getting the information from Access to MySQL.

I already posted to this in the Database forum: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10253914

If you get any ideas please let me know... I am beating my head against the wall on this one too...

Phil

TomD

Originally posted by tickled_pink
They presently go around clients with a laptop and use a form created in MS Word to take down clients' details. Ideally they would like some way to transfer the data collected to the server at the end of the day and be able to run a script that will take the information and transfer the relevant data to the various MySQL tables.

Maybe the native XML format of Word 2003 will solve this problem for you and avoid the whole Access middle man.

bastien

can think of a few of solutions off the top of my head...all using the scheduled tasks (assuming your in a win environment)

use php to access the access db and pull the records out, then place those records into the mysql db
use the export utility of access to create a csv file and use scheduled tasks to run the mysql load functions
use vb/vba to create an export file and using command line mysql load to load that file into the mysql db
use vb/vba to create the export at the local site (before the ftp) and ftp only the csv file to the server...then run the mysql load functions

take your pick..

should you want more details or something else let me know

regards

bastien

tickled_pink

Originally posted by ow-phil
I have been working on something too. I have a similar problem with importing information from MS-Access to MySQL. The problem is the AccessDB is FTP'ed to the website by a third party program. I need for an automated solution to run on the server to pull the data into MySQL on a twice-daily basis. Speed isnt an issue.. just getting the information from Access to MySQL.

Just been talking to my client today about the system. It's on hold for the time being while I carry on working on the php elements (site meant to go live on 1st November).

However, the issues we discussed were along similar lines. Ideally I will be looking at creating a semi automated system with the help of Access. One thing I will say about Access is that it's a very good tool for drafting your tables and DB structure before you come up with MySQL equivalents.

Anyway ... The system will eventually allow my client to enter all customer details with Access forms on a laptop. Rather than force the client to export the database into another format (where anything can go wrong) I will be looking to use the built-in VB to add a way of uploading the data to the server ... possibly as a text file containing a MySQL script. Then, the client only needs to enter their admin area and choose an option which will run a script to integrate that data into the existing MySQL tables.

Hope that's understandable. Maybe it'll give you some ideas.