ms access -> mysql suggestions

kayelle

Hi, I'm looking for some suggestions.

It's more db related than php.

I have a client with a ms access db which is linked to oracle and updated regularly. It's our intention to export the ms access db to a mysql db on a cron (say 5:30 am every day) via ftp to the webserver so I can use mysql for their shopping cart. It seems like the most straightforward way to keep their inventory and pricing updated.

So far I've thought of a couple of ways to do this:

Put a mysql db on the machine where access is. Use myodbc to link the dbs and then write a perl script that dumps the mysql db and ftps it to the website server. I haven't actually used myodbc much but that seems like the common suggestion.
export the access db to a csv file and ftp and import to the mysql db on the web server. I don't know all of the details of this either.

Is there a better way than this or are one of these ideas better than the other? What kind of trouble can I get myself into?

Any thoughts are welcome.

Thanks much.

goa103

Hi,

Damn... I think you're already into trouble. To sum things up :
php
ms access
oracle
mysql db
cron
ftp
webserver
shopping cart
myodbc
perl script
export
csv file
import

Is there a better way than this or are one of these ideas better than the other? What kind of trouble can I get myself into?

Are you serious ? The problem is that the system uses dozens of technologies. The idea is to use 1 good technology to handle your system. PHP/MySQL, MS Access/ASP.NET... Here you're into trouble because if something goes wrong... You can't do anything. Was the export to cvs file successfull ? Is my PERL script (why do you post on a PHP forum anyway) ?...

To get things up, keep things simple. If they use Oracle, use Oracle Designer, if they use MS Access, use MS SQL Server...

I don't mean to be rude but my idea is to WARN you.

Hope it helps,
JM. Moliba

kayelle

I know. In an ideal world, I would slim down the technologies, but I don't seem to have the option or that would have been my obvious choice. I'm only able to work with what's in front of me and on a very strict deadline to boot. I probably should run away, but I'm not convinced it can't work with enough checks in place.

I'm writing to this forum because this question is regarding the base of a php site and I've always gotten quick & helpful responses here. If you have suggestions about a comparable forum, please send them my way. In my searches I always seem to end up here.

Thanks again.

goa103

Hi,

I better understand your problem now. My idea would be to export your MS Access DB to a MySQL one. Then upload the DB to your website.

To export your MS Access DB :
http://server.bo-bo.si/php/dbfConverter/
http://www.cynergi.net/exportsql/
http://www.accessmysql.com/
http://www.churm.com/konvertr/

I used MyAccess and it perfectly worked. But there might be issues if your MS Access DB contains special features...

Maybe there's a way to use from command line so you would only have to set a cron job (Windows Task Manager) to export it daily, hourly... Whatever.

To update the online DB I developed a "stupid" but useful PHP tool : UQiQi. It allows you to update your DB from a .sql file. So if you create a cron job that calls the script... The DB is automatically updated.

But you first have to upload your .sql file on the website. Remote access from your website to your local .sql file is an issue. That's why you should use your favourite FTP client to upload your .sql file... An other cron job 😃.

MS Access DB -> MyAccess -> MySQL local DB > .sql > FTP Client -> UQiQi -> Website/MySQL DB.

I forgot what MyAccess generates... a .sql, directly access the local DB... Just try it 🙂.

Maybe MyAccess allows you to export and update a remote DB. I am sure there's a prosoftware to do it.

Curious... Why do they have to use Oracle ? Maybe you can advice them to hire you to develop a their E-Commerce website with PHP/MySQL 🙂

JM. Molina

goa103

Hi again,

I have just visited the MyAccess website :
"MyAccess is an AddIn for MS Access 97/2000 which allows you to manage MySQL databases from within Access. Main functions are"

Sounds like you to don't have to export your DB anymore as MyAccess allows you to "manager MySQL databases from within Access". And I am sure it supports remote access !

JM. Molina

bad76

If
you can configure odbc driver on server you can access with php directly to jet engine of Access, and you don't have to copy every day all on mysql...

Else the second you say, i think better...

kayelle

Thank you so much. This is a huge help. I think that I will likely go the myaccess route. I think I'd much rather work with php/mysql.

I'm still in the researching/planning stages, but hope to get started within a couple of weeks. I may post more to this thread as I go on.

Thanks again for your help and research.