let's see what it looks like!
example, we will have a Microsoft Access database. It is the
"Northwind" database distributed as a demo database with Microsoft
Access. On my system, it is located in the folder C:\Program
Files\Microsoft Office\Office\Samples. If it is not there, then search
for the file "Northwind.mdb" or "Nwind.mdb. It might
be in slightly different places with the various versions of Microsoft Access
and various Windows versions. I suggest that you make a copy of it.
We will perform changes to that Northwind database in the process!
Access "relations view" (tools ... relationships from the menu), the
Northwind database looks like the above image. It is quite a realistic example of
a medium size database to use with an administrative system.
all: when importing from Microsoft Access there is one special thing we need do
to before we can start importing. We need to unhide some system tables and let
the import tool read them. It is peculiar to Access that this information is
hidden to any human user by default and only readable to the system. These
system tables contain information about indexes of all kinds and similar
information. This information is also called "metadata." http://www.webyog.com/whitepaper/Preparing_Access__Migration.pdf
explains how to prepare the Access database file for use with the SQLyog
Migration Tool. If you only want to import data and no indexes or primary keys
at all, you will not need to perform this step. However, then you must choose
not to import indexes and foreign keys at the appropriate step of the migration
wizard or an error will occur when the Migration Tool tries to read the index
the case of Northwind-database you might find that the metadata tables are
already unhidden. This is because some Microsoft documentation uses this
database as an example too and has opened it in advance.
If you have
a Microsoft Office Suite installed on your system, you also have the
ODBC-driver that the Migration Tool will need to connect to Microsoft
Access. However, when migrating/importing from other ODBC-compliant
databases you might need to install the appropriate driver software separately.
let's start the Migration Tool wizard!
the Migration Tool from the SQLyog "powertools" menu. When launching
the "Migration Tool Wizard" the first question is whether you want to
create a new job file or edit an existing one. Since this is the first time you
use the tool, choose to create a new one and press the "Next"-button.
The connection dialogue appears.
This is the
"standard" SQLyog connection dialogue. On the Server-tab you choose
the MySQL-server parameters for the target MySQL server. If you want to use
tunnelling or a proxy, enter the details on the Tunnel-tab. Refer to the SQLyog
help-file for details on that. You can also choose a saved connection. At
the button, a drop-down box is added to the standard dialogue where you choose
which MySQL database to import to. It must be an existing database and
the MySQL user you connect with must have adequate privileges. However,
it can be an empty database or it may contain data. So if you do not have
a MySQL-database that you want to import to, then create one before starting
the Migration wizard.
dialogue is this one:
It is where
you enter the ODBC-connection details for the source ODBC database. Pressing
the "new" button or the "
" button will open the Windows
ODBC-manager, which is where to configure an "instance" of an
ODBC-driver to point to the Northwind database. It must of course be the
ODBC-driver for Access you use for this. However, if you have configured
the ODBC-driver in advance, just choose the appropriate DSN. Enter user
authentication details if needed and press "Next." You will be
presented with this:
prefer handwritten SQL, an editor window will open if you choose "Use a
query ..." Here we will choose the "Copy tables
" option, so
that we will see the rest of the GUI-elements of the wizard. The next image
shows the table selection tab and the transform-dialogue.