convert access db to mysql db through phpmyadmin

Posted on 2004-11-03
Last Modified: 2012-08-13
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.
Question by:bangers3474
    LVL 5

    Accepted Solution

    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 ;-)

    Author Comment

    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).
    LVL 16

    Assisted Solution

    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.
    LVL 5

    Expert Comment

    >>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.
    LVL 10

    Assisted Solution

    by:frugle 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. $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!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    two submit buttons one form 15 60
    Microsoft Edge 9 69
    CSS alignment issue 2 32
    Apps on mobile : future? 1 61
    Read about why website design really matters in today's demanding market.
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
    The viewer will learn how to count occurrences of each item in an array.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now