Migrate MS Access database to MySQL

[Hans Lodder]

Hi!

I have a large MS ACCESS database, and I want to migrate it to MySQL,
tables and content.

An approach could be:

- connect to MS Access with ODBC
- copy the structure from each table
- create the MySQL table structure
- copy the data from the MS Access table to the MySQL table

My questions:

a. Is this a good approach?
b. Is there an ODBC example available
c. Are there any pitfalls to be expected?

I appreciate hearing from you, and keep up the good work!

Kind regards,

Hans Lodder

[ujayadev_at_gmail.com]

Hi,

You can directly import MS Access database into MYSQL.  Pls go thru the following link:
http://dev.mysql.com/doc/connector-odbc/en/connector-odbc-examples-tools-with-access-import.html

HTH,

Warm regards,

Jayadev

[Hans Lodder]

Hi Jayadev!

Thank you for this appropriate link!

I tested this function with the latest release of the MySQL Workbench
under Win 8 Pro x64. Unfortunately the migration fails in database
re-engineering stage.

The message suggests that the Win8 ODBC driver does not recognize the
'get list of all tables' ODBC command. Checking the implementation at
the MS Tech site reveals that MS did implement this command. Using other
ODBC applications also demonstrated this working implementation.

Checking other sources I found that more people noticed that the MySQL
Workbench fails migrating MS Access databases at the same point with
with the same message.

Checking the documentation reveals that MySQL did not test this
particular migration function. A MySQL programmer commented that he has
no clue as what is going on.

So my conclusion for now is that the import of MS Access databases into
MySQL is not implemented under Win8 Pro x64.

But thanks for your suggestion anyway.

Kind regards, hans

PS: My original question still stands, and more suggestions are kindly
invited.


Op 1-4-2013 09:18, ujayadev_at_gmail.com schreef:

[ujayadev_at_gmail.com]

Hi Hans,

Try running the MYSQL workbench in compatability mode: XP service pack 3.  Maybe that will solve the problem.

I have not tested it though.

Warm regards,

Jayadev

[Hans Lodder]

Hi Jayadev!

It took me some time, but today I managed to migrate my database from MS
Access to MySQL. I used an old Win XP SP3 laptop. But the MySQL
Workbench software does not work there either. The reason turns out to
be rather simple.

The problem is that Oracle discontinued the from MS Access to MySQL
migration functionality in the MySQL Workbench. So far, Oracle has
received many complaints worldwide. They are currently reconsidering
their choice, and maybe in the future they will reactivate the code
again. It is still there.

There is a workaround though. I followed it, with some difficulties:

1. Download and install the MySQL Migration Toolkit (2006!). You can
find it here:

http://downloads.mysql.com/archives.php?p=mysql-migration-toolkit

2. Download and install the Java 5 runtime (also 2006!), to be found here:

http://www.filehippo.com/download_jre_32/592/

Then it works, and surprisingly fast, using the old laptop.

Thanks again for pointing me in the right direction!

Kind regards, hans


Op 4-4-2013 10:08, ujayadev_at_gmail.com schreef:

> <http://ujayadev_at_gmail.com> schreef:

[john s wolter]

Here's some additional resources I found useful.  Simply I only had time to paste these into this message. Also note the use of HTTPS as I have fsf.org HTTPeverywhere installed in my browsers.  Some sites are not setup to use HTTPS and just report no response error.

Most utilities are geared towards moving tables one at a time rather then migrating an entire database.  I view that as a major missing element.  Another issue are the VB-scripts are difficult to migrate.  Add to that list Forms and interactions with M$-SQL Server.  

I have read about translating from Access to M$ SQL Server and then migrating to MySQL.  I do not know if that provides a more or less complete and hassle free migration then going directly to MySQL.

I've switched to MariaDB(.org) a FOSS fork from MySQL.  They are adding features that Oracle has not developed.  I could speculate those may have not been developed for competitive reasons with Oracle DB.  Some applications are still being written in an Oracle MySQL dependent manner.  See http://en.wikipedia.org/wiki/MariaDB as well.

...you'll need to know more than you want to about M$-Access...

https://en.wikipedia.org/wiki/Microsoft_access

...scroll down to discussion...

http://forums.mysql.com/list.php?65

...Oracle MySQL migrating guide

http://forums.mysql.com/read.php?65,27115,27115#msg-27115

...very experience programmer...

http://25yearsofprogramming.com/blog/2010/20100718.htm

http://25yearsofprogramming.com/aboutus.htm

http://25yearsofprogramming.com/msaccess/index.htm

http://25yearsofprogramming.com/blog/index.htm

http://25yearsofprogramming.com/blog/2010/20100717.htm

http://www.devart.com/dbforge/mysql/

-----

...Search /w Google...

migrate convert microsoft access database to mysql - Google Search

https://www.google.com/#hl=en&sclient=psy-ab&q=migrate+convert+microsoft+access+database+to+mysql&oq=migrate+convert+microsoft+access+database+to+mysql&gs_l=hp.3..33i29.14889.16474.4.16808.8.8.0.0.0.0.229.1141.0j7j1.8.0.les%3B..0.0...1c.cwO9GH3FMxQ&pbx=1&fp=1&biw=1220&bih=634&cad=b&bav=on.2,or.r_cp.r_qf.

How can I easily migrate an Access database to MySQL? - Super User

http://superuser.com/questions/313209/how-can-i-easily-migrate-an-access-database-to-mysql

MDB Tools - Unlocking Your Data

http://mdbtools.sourceforge.net/

MDB Tools | Reviews for MDB Tools at SourceForge.net

http://sourceforge.net/projects/mdbtools/reviews/

MySQL :: MySQL 5.0 Reference Manual :: 20.1.4 Configuring Connector/ODBC

https://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration.html

MySQL :: MySQL 5.0 Reference Manual :: 20.1.4.3 Configuring a Connector/ODBC DSN on Windows

https://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-dsn-windows.html

http://www.mysql.com/why-mysql/white-papers/a-guide-for-migrating-from-microsoft-access-to-mysql/

..this company also does a nice Windows print to PDF freeware utility which installs and acts like a virtual printer...

http://www.bullzip.com/products/a2m/info.php

...several different conversion tools, none appear to be GPL...

http://dbconvert.com/formwizard.php

Cheers,

John S Wolter

[Dmitry Narizhnykh]

Hello, 

Try the software from convertdb to automate your migration process. The tool is able to migrate tables, queries to views and data.

Regards,

Dmitry 

понедельник, 1 апреля 2013 г., 9:57:15 UTC+3 пользователь Hans Lodder написал:

[Sathish D]

Try this tool SQLyog Ultimate. It has Import External data option which uses ODBC diver to import access data to MySQL. You can also schedule the import process.

[ma...@monitor.wc.lt]

On Thursday, April 20, 2017 at 7:31:04 PM UTC+3, ma...@monitor.wc.lt wrote:

You can use Online converter: http://excel2mysql.esy.es/acc2sql.php  .Hope it helps !

[ma...@monitor.wc.lt]

Online Access to MySQL converter You can find here : http://excel2mysql.esy.es/acc2sql.php