Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

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,

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.
3 Solutions
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 ;-)
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).
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.
>>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.
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!


Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now