Introduction

MySQL-to-Access is a program to convert MySQL databases into MS Access format.

Features

  • All MySQL data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Exports MySQL database into MS Access 97/2000/2010
  • Fast conversion engine (10MB MySQL database - in less than 1 minute on average P-IV system)
  • Converts individual tables (see Select Tables article for related information)
  • Converts queries (see Using Queries article for related information)
  • Merges MySQL data into an existing MS Access database
  • Stores configuration into profile (see Profile article for related information)
  • Command line support (see Command Line article for related information)
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Necessary privileges to read the source database on MySQL server


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be converted into MS Access database as if it would be a regular table. Select name of table where you wish to store query results in the desintation MS Access database. Enter query in the corresponding field. Also, you can test query on source database by pushing "Test Query" button. If query is not correct please check that it is a correct SQL SELECT-statement and all table and field names are spelled properly. You can also test query by running it from MySQL client.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call the conversion procedure from an automation script you can use console version of MySQL-to-Access S2AAGENT.EXE. Find this file in MySQL-to-Access installation folder. You can either run this tool directly from command line or call it from any script as well. The program supports the following command-line options:

--acc_ver=... | format version for the destination MS Access database (0 - MS Access 95/97, 1 - MS Access 2000, 2 - MS Access 2007/2010)
--dest=... | MS Access database name
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MS Access database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--port=... | MySQL port
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MySQL database name
--tab_def | convert table definitions only
--tab_file=... | name of the file containing table names to convert (one table name per line)

In the following example the program converts MySQL database "db1" on the remote MySQL server "mysqlhost" to MS Access 2000 database "c:\db1.mdb" using table names file "c:\tabfile1.txt":

S2AAGENT.EXE --src=db1 --dest=c:\db1.mdb --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword --tab_file=c:\tabfile1.txt --acc_ver=1

Table names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Notes:

  1. You can omit 'mysqlh' parameter to connect local MySQL server
  2. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  3. If you omit 'tab_file' parameter, all database tables will be converted
  4. If you omit 'mode' parameter, default mode 'overwrite the entire database' will be used


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. MySQL-to-Access is able to connect to MySQL server using either local or remote mode. Local mode is being used to connect MySQL server launched on the same machine where you run MySQL-to-Access from. Remote mode allows you to connect to MySQL server launched on another computer.

By default the program uses local connection mode. It is indicated by radio button "Local" selected. To establish remote connection you should select "Remote" radio button. For remote MySQL servers you should also enter host and TCP/IP port to connect through. Host could be either network name or IP address. TCP/IP port value is necessary only if it differs from the default MySQL port number 3306. Otherwise you may leave this field as is.

Finally, you are entering user name and password. You can leave these fields empty for anonymous connection.

NOTE: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-Access. See Troubleshooting article to learn how to resolve this problem.


Merging and Overwriting

If you try to convert MySQL database into an existing MS Access database, MySQL-to-Access allows you to choose one of the following options: overwrite the database, merge into the database or specify another destination name.

  • Select 'Overwrite the entire database' option to replace the entire contents of the destination MS Access database with MySQL data. Note that all data contained in MS Access database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of MS Access tables having the same names as tables in source MySQL database.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge MySQL data into the destination MS Access database. MySQL-to-Access implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into MS Access database only those records whose key/index value does not exist in the destination MS Access table. Otherwise, MySQL-to-Access appends the whole table contents to the corresponding MS Access table. Note: the structures of source and destination tables must be identical.

If you decide to enter another destination name just click 'Cancel' button.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection information (host, user name, etc) and all other necessary conversion settings. If you have created a profile, you can load it by pushing "Load Settings" button on the first wizard screen.

Once you enter all the information for converting databases, you can store it into a profile and reuse in next sessions. If you don't use any profile initially or change some parameters while working with the Database Conversion Wizard, you can store the updated information on exit by pushing "Save Settings" button on the last wizard screen.

NOTE: do not modify a profile manually since it may cause incorrect interpretation of conversion settings.


Select Databases

On the third wizard screen you should enter source MySQL database name and destination MS Access database path. Also, you can customize the conversion process using the following settings:

  • Destination format - Use this combo box to select format of the destination MS Access database.
  • Convert table definitions only - Select this box to convert only table definitions of the original MySQL database.
  • Skip converting indexes - Select this box if you don't want to convert indexes or have any problems with converting indexes (see Troubleshooting article for related information).
  • Convert BLOB to Memo - Sometimes MySQL database authors define text columns as BLOB (for example, in order to perform case-sensitive comparison). In this case you should select the checkbox to obtain better conversion results.


Select Tables

On this wizard screen you can manage the list of MySQL tables to convert. In the left part of the screen you can see "Available tables" list box. It contains all available tables of the source database except those that you have selected already. On the right you can see "Selected tables" list box. To add new MySQL table for converting into MS Access format you should select it in "Available tables" list box and click "Add" button (or double-click the selected item). Also you can use "Add all" button to insert all available MySQL tables. To remove an item from "Selected tables" list you should select it and click "Remove" button. You can use "Remove all" button to remove all items from "Selected tables" list box.

Push "Add Query" button to add SELECT-query filtering data that will be converted into MS Access database. See Using Queries article for the further information.


Troubleshooting

While working with MySQL-to-Access wizard you may encounter unexpected behavior or result. Use the following information to solve some common problems and get the result you want. This summary is organized as a list of problem-resolution pairs. Each problem description starts with "P:" and each resolution - with "R:".

P: Error message "The program does not support authentication protocol requested by server..."
R: MySQL 4.1.0 uses the authentication protocol based on a password hashing algorithm that is incompatible with that used by MySQL-to-Access. You can use OLD_PASSWORD function to make the certain account compatible with MySQL-to-Access. Login to MySQL server with administrator privileges and run the following queries:

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('mypass')
        -> WHERE Host = 'some_host' AND User = 'some_user';
    mysql> FLUSH PRIVILEGES;

Of course, you should place actual values instead of 'some_pass', 'some_host' and 'some_user'. Now you can use 'some_user' account with MySQL-to-Access.

P: In the resulting database certain fields are empty although in the original they are not.
R: MS Access maximum record size is 2000 bytes (it is a sum of all field sizes except MEMO and OLE fields). If MySQL record size exceeds this limitation MySQL-to-Access truncates some trailing fields.

P: Error message "Can't connect to MySQL server on <host name or IP>".
R: Make sure MySQL server is launched on the target machine. Also please check the port number field to verify that you have specified the correct number.

P: There are no databases in 'MySQL database name' listbox.
R: The user has not enough privileges to extract databases list. Ask your MySQL server administrator give you 'SHOW DATABASES' privilege.

P: I cannot uninstall MySQL-to-Access.
R: If you need to remove MySQL-to-Access product from your computer, you can use "Add or Remove Programs" option in the Windows Control Panel. If this option is not available for some reason, you can call uninstall procedure running the following command line from the installation folder:

UNWISE.EXE sql2acc.log

P: Error message "Duplicate field values being used for an unique index were found in the source database..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating Oracle data into a new MS Access database, you can either edit source database in order to resolve the conflict manually or restart the program with "Skip converting indexes" checkbox selected. When appending Oracle data to an existing MS Access database, you should remove the conflicting unique index in the destination MS Access database.

NOTE: unlike MySQL, all MS Access indexes are case insensitive. For example, two different MySQL keys "string" and "String" will be considered as identical keys in MS Access.

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to MySQL server until you have the appropriate permissions. Ask your MySQL server administrator give you 'SELECT' privilege on the source MySQL database.

P: Program freezes on the second dialog while connecting to remote MySQL server.
R: MySQL server cannot resolve network names. Close the program, restart MySQL daemon with "--skip-name-resolve" option and try again.

If you came across some other kind of problem while using MySQL-to-Access, please contact our support department at service@convert-in.com


Using Queries

Queries give you the ability to extract partial data for converting into MS Access database. The following examples are provided to illustrate how to use queries for particular purposes. Assume that we have table "Table1" as below:

   Table1(
    ID INT NOT NULL AUTO_INCREMENT,
    FName VARCHAR(50),
    LName VARCHAR(50),
    Birthday DATE,
    Notes TEXT
   );

Example 1. Convert certain records.

    SELECT * FROM Table1 WHERE ID > 1000
Example 2. Choose and rename columns.

    SELECT FName as FirstName, LName as LastName FROM Table1

Example 3. Skip records containing NULL values.

    SELECT * FROM Table1 WHERE Notes IS NOT NULL

You can add queries on "Select Tables" wizard screen. See Select Tables article for related information.


Contents

Add Query

Command Line

Connect to MySQL Server

Introduction

Merging and Overwriting

Profile

Select Databases

Select Tables

Troubleshooting

Using Queries