convert access db to mysql db through phpmyadmin

I am trying to convert an access database to a MySql database held on a remote ISP.
The ISP doesn't allow remote access to the MySQL database but I do have phpMyAdmin installed and linked to the MySQL database (completely empty, no table, queries or data).
As remotely linking to the SQL db from my pc isn't possible is there a way I can do this through myPHPAdmin or is there another way?
Any help greatley appreciated,
Bnager

P.S it isn't so much the data held within the tables I'm worried about more the tables themslves plus their relationships and queries which I would like to convert to the mysql format.
bangers3474Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

eyeh8uCommented:
If the mySQL instance is configured to not allow remote connections, or there is a firewall preventing it, there is no way that your access database can connect to the MySQL instance. There is also no way the SQL DB can connect to your local MS Access instance.

PHPMyAdmin does not, to my knowledge have a method of importing from an MS Access database. The primary obsticle for that function would be the lack of a JET engine library that works on UNIX/LINUX.

To make matters worse, MS Access does not support exporting your schema to DDL. So you can't even export the SQL to create the schema. And even if it did, the datatypes etc in Access aren't standard SQL types so it possibly wouldn't work on MySQL. Your only option appears to be to manualy re-create the schema in MySQL. You can then export the data from MS Access into CSV format and then import that into MySQL (I think phpMyAdmin has a CSV import?, been a while)

Another option might be to import the schema into a MS SQL Server instance via a DTC Package (Which can understand an MS Access DB and convert it into an MS SQL one) then have MS SQL Server generate DDL Scripts for the schema and try running them on MySQL (they will probably need some changes before they will run).

Bit of a thorny one that ;-)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bangers3474Author Commented:
Sounds like a DTC Package might be the way to go as the phpmyadmin tool isn't something I'm overley familiar with.
Does anyone know of a particularly good one (preferablyinexpensive).
Regards
Bangers
0
alain34Commented:
The best option will be for you to install mysql,php and phpmyadmin locally on the same machine than msaccess.
You need to manually create your table structure on phpmyadmin (as eyeh8u, you cannot export table structure).
Then with simple PHP script you can at the same time connect to msaccess and mysql, and tranfer the data this way.

When you have converted the data locally, you can export from phpmyadmin and import into your hosted mysql.
0
eyeh8uCommented:
>>Sounds like a DTC Package might be the way to go as the phpmyadmin tool isn't something I'm overley familiar with.
>>Does anyone know of a particularly good one (preferablyinexpensive).

DTC is a feature of Microsoft SQL Server 2000, and thus very expensive! You need an instance of SQL Server to do this with.

Alain34's idea of doing it localy might help.
0
frugleCommented:
http://www.dbtools.com.br/EN/dbmanagerpro.php FREE

The DBManager Professional is the most powerful application for MySQL and PostgreSQL It is rich in features. It comes in two editions so you can choose the one that will fit your needs: Freeware and Enterprise. The Freeware edition is totally functional and some features are not even available in any other free software for database management.

This product features a DAO Import Wizard to import data and structure from MSAccess, Paradox, FoxPro, XBase, ODBC and MSExcel worksheets.


http://www.convert-in.com/acc2sql.htm $39.85

Access-to-MySQL is a program to migrate Microsoft Access databases to MySQL server. Depending on your privileges on the target MySQL server you can export MS Access data into new database, overwrite the contents of an existing MySQL database or perform deferred conversion store the contents of the source database into a local "dump" file which can be uploaded by FTP and imported into mysql with: mysql -u user -p databasename < /path/to/file


There's probably more, but these are the ones that have been recommended to me in the past - I strongly suggest you try the FREE one before paying money out - if it'll do the job then use it.

If you feel strongly about paying for software, visit the gift centre and buy Crercio a book!

Mike
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.