How to convert from MS-Access to MySQL

Steps

• 1
  Create an ODBC data source that points to a MySQL database on a machine containing the Access database.
• 2
  In order to create an ODBC data source that points to a MySQL database, you will first need to download and install the MySQL driver (available from http://www.mysql.com).
• 3
  Once you have completed the download, install the MySQL ODBC driver by executing the file.
• 4
  The next step is to create a new ODBC File DSN. Click the Start button, point to Settings, and then click Control Panel. Open the Administrative Tools and double-click Data Source (ODBC).
• 5
  Click on the File DSN tab, and then click Add. Choose MySQL ODBC and click next.
• 6
  Type a name for the connection – for example, the name of your database. Click Finish. The last screen would require you to enter all of the supplied MySQL details.
• 7
  Open the Microsoft Access database, right click each table and click “Export”.
• 8
  In the “Save as type” field, select ODBC Databases from the dropdown list.
• 9
  Accept / Edit the table name (Note: table names will be case sensitive on our servers).
• 10
  Select the newly created datasource as the Export target.
• 11
  The Export to the ODBC datasource will create the tables’ definitions and populate them with the data from Access. Other data definitions will need to be done manually, for example:

  • Primary keys and indexes (in MySQL, a primary key can only be defined on columns that are specified as “not null”, so this may need to be done as well).
  • Auto-number fields (it is recommended that the SQL for the manual changes above are placed into a script file for future speedy execution if needed). An example of ASP code for setting up the new connection string, could be as follows:

  Dim cn set cn = Server.CreateObject("ADODB.Connection")cn.connectionstring = "Driver={Mysql};Server=sql_server_name; Database=database_name; UID=user_name; PWD=password" (A file dsn can also be used)