From Ms Access export data to a text delimited file (like comma delimited). i.e select table right-click and export to an external file and select file type as text file.

Then from Mysql simply
LOAD DATA INFILE LOCAL <filename> FIELDS TERMINATED BY '<DELIMITER>' ;

Simple really.

    Thanks you Matt, John, and Nilanjan. :-)

      3 months later

      It is not that easy and the syntax given in the previous messages is wrong.
      You first export the access file into a .txt file. Swicth to mysql> 'type' load data infile 'yourfile.txt' into table 'your_mysql_table_name'; . You should also create the mysql table beforehand with appropriate variables to fit into the incoming table. I gave up, I will do a new database in mysql.(I have only a few records to justify a conversion)

        It is really that easy.
        1) The MS-Access export file must be delimited.
        2) The MySQL database structure must be in the same order (field names) as the access file that you exported.
        3) I put the .txt file in the same directory
        as the mysql db files and run the syntax:
        load data infile 'yourfile.txt' into table 'your_mysql_table_name';

        I'll help as I can.

        Matt

          a month later

          I only started using MySQL this morning and dbTools is by far the best method of converting databases ive found and ive been looking for ways to do it for about 5hours.

          dbTools can be found at http://dbtools.vila.bol.com.br/ it does everything for you, it creates a MySQL database based on any ODBC database, both structures and data are converted. I converted my access databases in seconds, the program interface is a bit buggy, but besides that its a great program.

          I have also heard that Mascon can do conversions but i havent tried it yet
          Mascon can be downlaoded from
          http://www.scibit.com/Products/Software/Utils/Mascon.asp

          Hope that helps,
          Tim

            2 months later

            Hi,

            Please send me programs to convert access to mysql and mssql to mysql.

            Rupesh

              a month later
              5 months later

              I just did it with about 150 records and imported via a web-based "PHP MyAdmin" on the remote server....just open database in Access, right-click on the table you want to export and choose "export..."

              Then make sure you choose to export as a text file (in the pulldown options) and add the extension ".csv" to the filename before exporting. A dialog box will then pop up to let you choose your specific text-file options for exporting.

              Once your finished exporting, change the exported .csv file extension to .txt.

              Now go to your PHP MyAdmin interface on your web host. If you haven't already, make a table with the same number of columns and the correct data types. Make sure they are in the same order as your exported Access database. Then open the open that table administration page. About half way down the page you'll find the option to "insert textfiles into table". The rest is self explanatory. Worked like a charm for me. I did notice some "#" characters in some of my text fields when viewing from the MyAdmin interface, but when viewed through the actual PHP page the formatting was all intact.

              Another hint - open your text file before you import into MySQL and see if just the text fields have quotes around them or if both text and numeric data are within quotes. If just the text is within quotes then make sure to check the option for that on the MyAdmin import screen. Also make sure you set the field delimiters to be commas when importing, or if you used a different delimiter, just make sure you specify that when importing. If you don't understand what I mean, just go try it and you should be able to figure it out.

                2 months later

                Thank you Brett! your reply here helped me a lot!

                  importing with the phpmyadmin is always a bit critical.
                  i can recomment the db tools mentioned in one of the threads, it rox (althoug its not very stable)

                    5 months later

                    Just tried it, following Brett's instructions to the letter - it worked like a dream. Converted an Access database with over 2,000 records to mySQL in less than 10 minutes and without any loss of data. Thanks Brett!!!

                    At http://www.convert-in.com/acc2sql.htm there's a great prog to convert your Access database to mySQL. The demo version will only do 5 records - but you can use it to transfer your tables and structure to your mySQL database and then follow Brett's instructions to transfer all your data via a txt file.

                      9 months later

                      The above posts make sense for 'dumping' data tables to MySQL, but how do you go about duplicating the field types, keys and relationships?
                      Is there some type of report in Access that explains these parts?

                        Write a Reply...