Using PHP to migrate data from Access to MySQL

ppowell

I've read some online resources that utilize various MySQL command-line actions to migrate data from Access to MySQL.

The situation is this: a group of co-workers of mine will be using an Access db in a shared source (for now, a directory.. ???) to be able to generate reports on the fly. What they want to do is to be able to migrate that data to a MySQL db instance that currently exists on a different server.

What would be the best way to do this within a PHP wrapper (if even PHP at all, which I prefer); if so, what are the ways I can ensure security as this is US Federal Government data and thus must be locked down tight?

Thanx

Phil

PS: They're insisting on Access on their end as it is their apparently easiest (and quickest w/o my having to build an entire CMA for them in 3 months) means of maintaining the data locally before migration.

vronsky

This ressource will be useful,'cos what I'm doing actually is exporting data from Access in CSV format, then loading it in Mysql with a PHP script.

ppowell

Ok so I should have

1) Script that extracts data from Access into flat-file CSV

2) Script that retrieves CSV and puts into MySQL.

That sounds like a reasonable approach, except that in realtime application, what would you do with the CSV files in the interim, how would you ensure referential integrity, and how would you ensure that the data mirrors absolutely upon a re-running of the 2 scripts?

Sorry if I'm asking too many questions but the sysadmins here would probably ask me that.

Phil

vronsky

so what you need is a MySQL mirror of a MSAccess??

I know with PEAR you can connect the MS access, then retreive the data and store them into Mysql.

But I don't find it a great solution.

Maybe somebody else can help.

ppowell

That sounds a bit bloated to have to load the entire set of PEAR classes, and even so, being that I work for a US Federal Government subcontractor, we have to abide by government regulations which restricts usage of third-party applications, including PEAR classes.

So basically, PEAR is out in this case. I found some stuff using mysqldump commands to possible handle it, but not sure. I might go with the CSV idea for now.

One other problem, though: the Access database will, again, be found in a shared folder that will, of course, have to allow for this server I'm on access to get to.. Access. Would I access it via the computer's IP address and go to the folder? How would I get there?

Phil