Toad World® Forums

Convert MS Access to MySQL?


#1

Hello. I am evaluating Toad Data Modeler to see how useful it can be to reverse engineer some Access databases to MySQL. This software appears to be very powerful and I am excited to learn more about it, but am having some issues just getting started for my particular use case. Perhaps someone here can help.

  1. What steps are needed to convert a simple MS Access 2003 database to a MySQL 5.1 database that can be imported to MyPHPAdmin? I reverse engineered the database, then I added a database and assigned the tables to it, then I generated a DDL. I am still getting a #1049 Error Unknown Database ‘foo’.

  2. Are there any tutorials for using TDM, rather than just showing functionality? I found a dated tutorial on YouTube that would have been very helpful if the UI had not changed so much. The flash “guides” are difficult to use because viewers cannot go backward, only forward, so we cannot consume the content in any order except how it is presented. Also, the content is about what the buttons do rather than how to use them in combination to achieve a goal. The help files are also difficult to use for this latter reason. I know that people use this software for many reasons, but a couple tutorials that take a new user through the process of reverse engineering a database of one common type to another common one (e.g., Access to MySQL :)) would be very helpful.

Thanks!


#2

Hi Ed,

thank you for your feedback.

  1. Reverse engineer your MS Access database, convert it via Sync & Convert Wizard to MySQL 5.1 (menu File | Sync & Convert | Sync & Convert Wizard), do necessary changes and generate DDL script. The generated script for MySQL doesn’t contain commands for database creation, it will create structure in existing empty database. If you work on your local enviroment, use third party tool to create empty database (Toad for MySQL or HeidiSQL or something from MySQL vendor) then connect to the database and execute DDL script generated from Toad Data Modeler.

If you work on some web project, every hosting company provides a visual tool to create new database (cPanel etc.). Use the online tool to create new database and then use phpMyAdmin to import DDL and execute it.

In both cases, empty database must exist.

  1. See our flash movies at: http://modeling.inside.quest.com/kbcategory.jspa?categoryID=158
    All the movies should have a “progress bar” at bottom and you can click any point on the progress bar to return back in the movie. Or… you can watch movies again, all of them are very short, few minutes at maximum.

Regards,

Vaclav


#3

Thank you for the helpful response. I followed your directions and received this error. I am using Access 2003 and MySQL 5.1:

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Varchar(50)
COMMENT ‘Value for machines to reference this class. Often the sa’ at line 15


#4

I figured out that “Key” is a reserved word in MySQL that TDM doesn’t trap. I changed the name of that field in MS Access (now “Code2”; I tried to change that in TDM but received other errors), reverse engineered again, generated script again, and tried to import to the database again. Now I get this error for the same field:

#1170 - BLOB/TEXT column ‘Code2’ used in key specification without a key length

How do you specify a key length? This “Code2” field is a hyperlink in Access but is reverse engineered as a text field in TDM. I think I have pushed all the buttons and tabs but can’t figure out how to specify by hand the key length of this field.


#5

Hi,

TDM doesn’t contain verification for reserved words (If you wish to get this functionality quickly, you can write your scripts that will iterate your model objects and check if object names don’t contain reserved word etc… You can find example of verification extension in library - there is package that verifies object length for Oracle models.)

Regarding BLOB/TEXT: Key question is if the data type needs to be TEXT or for example Varchar with specified length. In TDM, default settings for data type conversion is Hyperlink -> TEXT, but you can easily change this. Click Settings | Options and in section general enable check box Expert mode. Then click Settings | Data Type Conversion Settings. New dialog opens. From the Source combo box choose MS Access 2000-2003. From the Destination combo box choose MySQL 5.1. Then click the Load icon. Data types will be displayed in a grid. Click current value in Destination column and press F2. Change the item from Text to Varchar and set parameters if needed. Then do the conversion again.

In general, I guess the column is in index or key. Having indexes on TEXT/BLOB columns doesn’t seem to be a good idea. There are a lot of discussions about it online. Examples:
http://forums.techarena.in/software-development/1185285.htm
http://drupal.org/node/146296

If you still want to use TEXT/BLOB column in index, you can write custom DDL to After Script section (Edit entity and write SQL code on tab After Script) manually.

Regards,

Vaclav


#6

Thanks!