Migrating from MS Access (*.mdb,*.accdb) To MySQL database

This article describing how to use ESF Database Migration Toolkit to migrating data from MS-Access 2000-2007 to MySQL database and vice versa.

Introduction:

If you have not installed ESF Database Migration Toolkit, get it now.

1). In "Choose a Data Source" dialog, choose "Microsoft Access (*.mdb)" or "Microsoft Access 2007 (*.mdb;*.accdb)" if you are migrating data from Access 2007; Then press "Browse" to find the Access MDB (or ACCDB) file, if the file has a password, fill out password in "Password" field, then click "Next" to continue.

2). In "Choose a Data Destination" dialog, choose "MySQL Database", fill out the server name where MySQL locating and the server port (default is 3306), plus, you want to offer a username and password to connect it. MySQL supporting some different Storage Engines, please click here to get more information about MySQL Storage Engine. In "CharacterSet" field choose a character set like UTF-8(Notice: the chararacter set must be same as the MS Access database, otherwise, it will lost some characters when converting). Press "Refresh" button, this program will enumerate all databases locating in the MySQL server automation, you can select a database directly, or input a new database name in "Database" field, this program can create the new database in converting. Click "Next" to continue.

3). In "Select source Tables(s) & View(s)" dialog, it is listing all the tables and views in the MS Access database, you can preview source table data via "Preview" button. Selecting the tables or views that you are preparing to convert. In here, you have a chance to change the destination table name at "Destination" column. There is an "Overwrite Database" option below the list (Notice: When you check this option, the destination MySQL database will be removed, and all data will lose!). If you want to set more options for conversion, click "..." button at "Transform" column immediately.

4). In "Transform" dialog, there are some useful options for your conversion. You can only convert table structure via check "Copy Structure Only" option, or check/uncheck "Copy Indexes"/"Copy Foreign Keys" to enable/disable copy MS Access indexes/foreign keys when converting. The "Transfer auto-increment" option is most usefully for you when you converting a MS Access table with a auto-increment field (e.g.: ID), sometimes, when you append data to a MySQL table with auto-increment, perhaps you will receive a message like "Duplicable-keys...", you can uncheck this option to resolve it. Plus, you can change table/column case in "Table/Column Name" option or give a data filter (e.g.: id<100 1="1)." all="" allow="" and="" be="" can="" choose="" click="" data="" default="" done="" ecords="" empty="" etc="" f="" field="" fields="" filter="" ield="" in="" let="" list="" map="" migrate="" migrated="" name="" null="" option="" or="" p="" precision="" save="" settings.="" size="" table="" the="" this="" to="" type="" value.="" want="" when="" which="" will="" you="" your="">

5). In "Execution" Dialog, click "Submit" to starting the MS Access to MySQL conversion. When conversion is running, click "Stop" to stop the conversion.

6). To converting from MySQL to MS Access(*.mdb, *.accdb), just choose" MySQL Database" in "Choose a Data Source" page and choose "Microsoft Access (*.mdb)" or "Microsoft Access 2007 (*.accdb)" in "Choose a Destination" page , Other settings is same as above.

curtsy: easyfrom