Custom Search
 


How to export Northwind Access database to MySQL


Inside This Article
1. Export Northwind Access database to MySQL via ODBC
2Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5Implement file directory based image management

Export Northwind Access database to MySQL via ODBC



In this article, we are going to export the popular Microsoft Access Northwind database to MySQL server.

If you can't wait, you can grab the completed and normalized MySQL Northwind script and the image files now.

  • Click here to download the completed and fully normalized MySQL Northwind relational database script. You need MySQL version 5.0 or greater because I included character set and collation information in varchar and text columns.

  • Click here to download all the images for the Categories and Employees table.

If you have the patience and want to learn the real meat about how to export and how to create a true relational Northwind database in MySQL, continue reading.

Software you need for the migration

  1. Windows 2000 or XP (May work on Windows 9x but never tested).

  2. Office 2000 or XP with Northwind database (May work with Office 97 but never tested).

  3. MySQL server 4.0 or higher.

    MySQL 5+ is preferable as our full Northwind SQL script is based on version 5.0.27.

    Refer to the following two pages for MySQL installation guide.

    How to install MySQL 4.1 on Windows

  4. MyODBC driver (MySQL Connector/ODBC 3.51)

  5. SQLyog free Community Edition - MySQL Query and admin tool.

Steps to Migration Northwind to MySQL

There are three major steps here. Skip the step that you have done.

Step #1: Install MySQL ODBC 3.51 Driver.

For detailed information about DSN setup, see how to set up DSN in ODBC Data Source Administrator on Windows.

MySQL Connector/ODBC (MyODBC driver) is the ODBC driver for MySQL databases. It can be used by various ODBC-compliant applications to connect to a MySQL database and work with data stored in the database.

You can download and install MySQL ODBC Driver from MySQL.com website.

Step #2: Create blank Northwind database in MySQL server.

One of the easiest ways to create a new database in MySQL is use open source SQLyog. Create a blank database and name it Northwind.

Create Northwind database in MySQL.

Step #3: Create Data Source Name (DSN) in ODBC for Northwind database.

DSN is the name to the connection details of a database. When connecting to the database, ODBC-compliant applications simply refer to a DSN to obtain connection details.

To learn how to set up DSN for Northwind database, see How to set up DSN in ODBC Data Source Administrator.

Step #4: Export Northwind tables from Access to Northwind database in MySQL.

Here we are going to export tables in Northwind database in Access to Northwind database in MySQL. There are eight tables and we will have to export them one by one.

Below demonstrates how to export Categories table. Follow the same steps to export other tables.

  1. Open Access Northwind database. You should normally work with a copy of Northwind database so that you always have the original Northwind database available to grab for future use. Copy Northwind over to a different directory such as C:Temp.

  2. In the database window, right click table Categories.

  3. In the drop-down menu, select Export. Alternatively, you can use menu item 'File -> Export'.

    Select Categories table in MS Access.

  4. Now the dialog window "Export Table 'Categories' to ..." is opened. In the 'Save as type' drop-down box, scroll down until you find 'ODBC Databases()' entry and click it.

    Select ODBC Databases.

  5. Click [OK] button to confirm the export of Categories table.

    Confirm the export of Categories in Northwind database.

  6. Now in the 'Select Data Source' dialog box, click Machine Data Source tab and select 'myodbc' in the Data Source Name (DSN) list and then click [OK] button. If you haven't created DSN for MySQL, click here and follow the steps to create it.

    Select MySQL Data Source Name.

  7. After you click [OK] button, the Categories table is exported to Northwind database in MySQL.

    Repeat the above steps to export other tables. Below is a screenshot of Northwind database showing in SQLyog after all tables have been successfully exported from Northwind database in Access.

    Northwind database in MySQL.

What's next

A quick look at the table structures reveals:

  • All tables are imported into MySQL as MyISAM table type.
  • No primary keys are migrated.
  • All columns allow nulls.
  • No indexes and no foreign key constraints are migrated.

In next step, we will write scripts to turn MySQL Northwind database into a true relational database. These include changing all tables to InnoDB table type, adding primary keys and foreign keys, adding default constraints and indexes, and more.


Copyright© GeeksEngine.com




Inside This Article
1. Export Northwind Access database to MySQL via ODBC
2Make northwind a true relational database in MySQL
3Restore northwind database from SQL dump
   
4Northwind database schema
5Implement file directory based image management
Related Articles:

1.Steps to install PHP 5.x on Windows as a development machine
2.How to install Apache 2.x web server on Windows
3.How to connect two different versions of MySQL server on the same computer
4.How to configure MySQL server 5.1 on Windows
5.How to install MySQL server 5.1 on Windows with screenshots
6.Five ways to create include path for PHP
7.How to use Date and Time data as integer value in PHP and MySQL
8.How to use Apache Virtual Host to run multiple local websites on Windows
9.Install all PEAR packages by yourself
10.How to install PEAR on Windows
11.How to use PHP and Microsoft SMTP Virtual Server to send emails
12.How to install PHP server-side scripting language on Windows
13.How to install Apache 1.3 web server on Windows
14.How to install two different versions of MySQL server on the same PC
15.How to configure MySQL server 4.1 on Windows
16.How to install MySQL server 4.1 on Windows with screenshots


Other Recent Articles from the Database SQL category:

1.Get single records when duplicate records exist
2.Find duplicate values or non-duplicate values in a table
3.How to get Top 1 record from Oracle by using PL/SQL
4.How to get Top N rows from Oracle by using SQL
5.How the data types in Access Northwind are converted to Oracle
6.How to do cross table update in Oracle

Copyright © 2017 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy