From Access Database database to the web. Best approach?

[GaryS]

I'm working with a admin company that uses a PC-based Access database to manage membership records. The membership information - at least certain parts of it - are needed for the website (mySQL on the web server).

At some point it may be possible to install something on their PC to "throw the data" automatically. But for now, I need to assume that I won't be able to tamper with their machine in any way.

Two pieces of "good news": (a) as far as I know, there will be no need to synchronise data: it will be a one-way street Access ==>> mySQL; (b) the data transfer is likely to be infrequent: once a day at the very most.

so....

Would appreciate your thoughts on how best to get data from their Access database to the database on the webserver.
Would also appreciate your thoughts on whether to transfer all of the data (+/- 1000 records), or just changed data.


Thanks!

[NogDog]

I don't believe Access has a built in change-tracking mechanism, so if you wanted to transfer only changed records, you would need to create some mechanism to track what was added, updated, and/or deleted since the last transfer. Therefore I'm guessing that just uploading the entire table might end up being the more effective solution (but that's based on a lot of assumptions, so don't hold me to it).

As far as getting the data from Access to MySQL, my first thought would be to use Access's export function to dump the table data into a SQL file. Then you could create a PHP file on the web host with a file upload form which then takes that SQL file and runs it against the database.

[NogDog]

Here's a (perhaps slightly dated) article I found on migration from Access to MySQL that might give you more food for thought: http://www.kitebird.com/articles/access-migrate.html

[GaryS]

NogDog - Thanks you very much for your posts.

You've confirm some of my thoughts (which is always nice). Thing all the reords will have to move. I'd wondered about export to XML... but I hadn't though about SQL dump, which would be easier.

Thanks you very much for the link. It threw up one idea that might be very useful: seems like its possible to link to one or more of the SQL tables from Access. Need to have a play; if it works, it would mean that the some of the SQL tables (things like categories) could become "the master data"... meaning that fewer tables would need to be uploaded.

Thanks again for posting - very useful.