Introduction

Access-to-MySQL is a program to move MS Access databases to MySQL server. Depending on your privileges on the target MySQL server you can export MS Access data into new database or overwrite the contents of existing MySQL database.

Features

  • All MS Access data types and attributes are supported
  • Works with all versions of Unix and Windows MySQL servers
  • Stores configuration into profile (see Profile article for related information)
  • Filter data to convert via SELECT-queries (see Using Queries article for related information)
  • Option to convert MS Access database into MySQL dump file (see Customize Conversion article for related information)
  • Special approach for VServer users (see VServer User Notes article for related information)
  • Supports multibyte character sets
  • Option to customize types mapping, select certain columns to convert
  • Converts indexes with all necessary attributes
  • Converts relationships between tables
  • Converts MS Access queries into MySQL views
  • Merges MS Access data into an existing MySQL database
  • Synchronize MySQL database with MS Access data
  • Converts password protected MS Access databases
  • Converts MS Access databases protected with user-level security
  • Quick Launch
  • Command line support (see Command Line article for related information)
  • Easy-to-use wizard-style interface
  • Full install/uninstall support

Requirements

  • Microsoft Access installation (ODBC is not required)
  • Necessary privileges to write into the destination database

Limitations

  • MS Access forms, reports and macros are not converted
  • Does not convert system (hidden) tables


Add Query

This dialog allows you to compose a SELECT-query and result of this query will be migrated to MySQL server as if it would be a regular table. Select name of table where you wish to store query results in the desintation MySQL 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 always check query by running it from MS Access.

See Using Queries article for the further information.


Command Line

To perform batch conversion or call conversion procedure from an automation script you can use console version of Access-to-MySQL called A2SAGENT.EXE. Find this file in Access-to-MySQL 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:

--accp=... | MS Access database password
--charset=... | MySQL character set
--dest=... | MySQL database or dump file name
--dump | convert MS Access database into MySQL dump file
--engine=... | MySQL storage engine (MyISAM, MEMORY, InnoDB, BerkeleyDB, ARCHIVE, FEDERATED, etc). Please refer to MySQL documentation for the further information.
--help | display help message and exit
--logfile=... | path to the logfile where execution traces will be written
--mode=... | how to process an existing MySQL database (0 - overwrite the entire database, 1 - overwrite existing tables only, 2 - skip existing tables, 3 - merge, 4 - synchronize)
--mysqlh=... | MySQL server IP address or network name
--mysqlu=... | MySQL user name
--mysqlp=... | MySQL user password
--port=... | MySQL port
--profile=... | path to a profile to load conversion settings from
--queries | convert MS Access queries into MySQL views
--silent | use this option to disable program output
--skip_idx | skip converting indexes
--src=... | MS Access database name
--sysdb=... | path to MS Access workgroup information file (*.mdw)
--tab_def | convert table definitions only
--tab_file=... | file containing table names to convert (one table name per line)
--uglu=... | user name for MS Access user-level security
--uglp=... | password for MS Access user-level security

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

A2SAGENT.EXE --src="c:\my reports.mdb" --dest="my reports" --mysqlh=mysqlhost --mysqlu=administrator
--mysqlp=the_passsword --tab_file=c:\tabfile1.txt

Table names file should be formatted as follows:

Table_1
Table_2
...
Table_N

Notes:

  1. If you specified a profile, there is no need to specify any other parameters
  2. You can omit 'mysqlh' parameter to connect local MySQL server
  3. You should omit 'sysdb', 'uglu' and 'uglp' parameters if not use MS Access user-level security
  4. It is necessary to specify 'port' parameter only if it differs from the default MySQL port 3306
  5. If you omit 'tab_file' parameter, all database tables will be converted
  6. If you omit 'mode' parameter, default mode 'overwrite the entire database' will be used
  7. Command line parameters that contain spaces should be enclosed in quotes (for example --dest="my database")

MySQL character sets

This parameter specifies correct encoding of text values in the destination MySQL database. It can have one of the following values:

value | Description | value | Description
big5 | Big5 Traditional Chinese | dec8 | DEC West European
cp850 | DOS West European | hp8 | HP West European
koi8r | KOI8-R Relcom Russian | latin1 | cp1252 West European
latin2 | ISO 8859-2 Central European | swe7 | 7bit Swedish
ascii | US ASCII | ujis | EUC-JP Japanese
sjis | Shift-JIS Japanese | hebrew | ISO 8859-8 Hebrew
tis620 | TIS620 Thai | euckr | EUC-KR Korean
koi8u | KOI8-U Ukrainian | gb2312 | GB2312 Simplified Chinese
greek | ISO 8859-7 Greek | cp1250 | Windows Central European
gbk | GBK Simplified Chinese | latin5 | ISO 8859-9 Turkish
armscii8 | ARMSCII-8 Armenian | utf8 | UTF-8 Unicode
ucs2 | UCS-2 Unicode | cp866 | DOS Russian
keybcs2 | DOS Kamenicky Czech-Slovak | macce | Mac Central European
macroman | Mac West European | cp852 | DOS Central European
latin7 | ISO 8859-13 Baltic | cp1251 | Windows Cyrillic
cp1256 | Windows Arabic | cp1257 | Windows Baltic
binary | Binary pseudo charset | geostd8 | GEOSTD8 Georgian
cp932 | SJIS for Windows Japanese | eucjpms | UJIS for Windows Japanese

Please refer to MySQL documentation to better understand the concept of character sets and collations.


Connecting to MySQL over SSH

Database administrators and security people know that it is good practice to open only local access to MySQL server for security reasons. However, if you want to connect any of Intelligent Converters applications to MySQL server hosted on a Linux/Unix system, it is necessary to open remote access from your local IP address on the destination server. Obviously it makes MySQL server less secure. To improve the situation you can use SSH protocol for secure data transfer between local machine running Intelligent Converters application and MySQL server.

The most easy way of connecting MySQL through SSH is to use port-forwarding through an SSH tunnel. This technique is based on creating an alias that makes any client application think it's connecting to "localhost" machine, while it's really connecting to the remote server through the tunnel.

If you are using the command line ssh, the command would look like this:

ssh -L <localport>localhost<remoteport> <username>@<servername>

If you use the standard MySQL port, both "localport" and "remoteport" must be set to 3306. You can do the same via tools having graphical user interface like Putty or SecureCRT.

Once you've got the ssh tunnel going, you can open any of Intelligent Converters application and enter in the details for remote MySQL server, using 'localhost' as the server host. See below example of MySQL connection page for Access-to-MySQL converter.


Connect to MySQL Server

On this wizard screen you should enter the necessary settings to establish connection with MySQL server. Access-to-MySQL 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 Access-to-MySQL 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.

You should have the sufficient privileges to create new database on MySQL server. Otherwise you will be able only to import MS Access database contents into an existing MySQL database. See VServer User Notes article for related information.

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


Conversion Settings

On this wizard screen you can customize the conversion process using the following settings:

  • MySQL character set - tune this parameter to set up correct encoding of text values in the database. Please refer to MySQL documentation to better understand the concept of character sets and collations.
  • MySQL engine type - storage engine determines basic characteristics of handling tables such as storage limits, transaction-safe, locking granularity, etc. Each of them has its own cons and pros. Please refer to MySQL documentation for the further information.
  • Convert boolean value as - use this setting to customize how Microsoft Access booleans will be converted. It can be either ANSI standard (true=1,false=0) or Microsoft standard (true=-1,false=0)
  • Convert table definitions only - select this checkbox to convert only table definitions of MS Access database. All the data will not be migrated.
  • Convert views - select this checkbox to convert views
  • Skip converting indexes - select this checkbox if you don't want to convert indexes


Customize Conversion

Access-to-MySQL provides two options to convert MS Access database into MySQL data source:

  • Move to MySQL server directly - use this approach to export the contents of the source database into a database on the target MySQL server. You need MySQL server launched and also necessary privileges to write into a database on the target server. See Connect to MySQL Server article for related information.
  • Store into dump file - use this approach to perform deferred conversion and get more control over the process. Following this way, the program stores the contents of the source database into a local dump file instead of moving it to MySQL server directly. The resulting file contains MySQL statements to create all tables and to fill them with the data. See Importing dump files article for related information.

You can choose one of these approaches on the first page of Access-to-MySQL wizard using the corresponding radio buttons.


Edit Table

On this wizard screen users can edit table name and customize types mapping.

Access-to-MySQL converts Microsoft Access types into the most appropriate MySQL types. However, sometime it is necessary to change type for one or multiple columns. To customize type mapping select the appropriate item in the listview on the left and specify new name, type and default value for the field using "Field info" group of controls. Please note that listview on the left contains column attributes for the original Microsoft Access database, new custom types and attributes will be displayed in "Field info" controls when the corresponding item is highlighted in the listview.

See Types Mapping article to learn more about safe mapping Microsoft Access types into MySQL types.


Frequently Asked Questions

On this page you can find the most frequently asked questions regarding Access-to-MySQL. If your question is not answered here, please contact us at service@convert-in.com

  • How to bypass error "Cannot recognize MS Access database format..."
  • Why convert Microsoft Access databases into MySQL format?
  • What kind of Microsoft Access data is converted to MySQL?
  • Does Access-to-MySQL require ODBC drivers?
  • What versions of MS Access and MySQL are supported?
  • How can I rename column name in the resulting MySQL database?
  • Does Access-to-MySQL support automation?
  • How to change destination table name?
  • It is possible to connect to MySQL over SSH with Access-to-MySQL?
  • How to bypass error "The program does not support authentication protocol requested by server..."
  • Why do I see error "The file does not comply with MS Access workgroup information file format"?
  • How to uninstall Access-to-MySQL converter?

P: Error message "Cannot recognize MS Access database format"
R: The first reason of this error is that soure file is not a valid MS Access database. The second reason could be missing appropriate Microsoft Data Access (DAO) libraries on the system to read the database. In this situation it is recommended to install Microsoft Access 2010 Runtime as it contains correct version of DAO libraries:

http://www.microsoft.com/en-us/download/details.aspx?id=10910

NOTE: it must be 32-bit installation pack, even if your system is 64-bit. If the error persists, please contact Intelligent Converters support service at
Go to top

Why convert Microsoft Access databases into MySQL format?
1. Performance. According to many benchmarks MySQL shows one of the best performances among DB2, Oracle and SQL Server 2000. MySQL is used by lot of organizations that demand high performance and stability.
2. Cross-Platforming. One of the most advantages of MySQL is its cross-platform capabilities. You can develop your database on a Windows laptop and then deploy on Windows or Linux server. It gives you incredible flexibility when choosing server platform.
3. Multiple users access. Microsoft Access supports data sharing but it has not enough power to handle many simultaneous users. This application has been designed as data manager for local use and that is what it is good for. MySQL is a real client-server database environment that can handle many simultaneous connections.
4. Security. Although Microsoft Access provides some capabilities to protect the data, they are optional and it is easy to bypass them. MySQL provides strong security system and anyone willing to access the data must know MySQL user name and password.
Go to Top

What kind of Microsoft Access data is converted to MySQL?
Access-to-MySQL converts table definitions with all necessary attributes, records, indexes, relationships and queries. It does not convert forms, embedded functions that are not complied with ANSI SQL and Visual Basic scripts. Demo version converts only 5 records per table and does not convert relationships between tables.
Go to Top

Does Access-to-MySQL require ODBC drivers?
No, Access-to-MySQL converter does not require ODBC drivers or any other middleware software. The converter connects to source and destination databases directly and this is the reason of its high performance.
Go to Top

What versions of MS Access and MySQL are supported?
Access-to-MySQL works with all versions of Micrsoft Access (including MS Access 2012) and Windows or Linux/Unix MySQL servers.
Go to Top

How can I rename column name in the resulting MySQL database?
You can modify column name converting contents of the corresponding table as result of SELECT-query. See Using Queries article for the further information.
Go to Top

Does Access-to-MySQL support automation?
Access-to-MySQL converter does not support automation directly. However, there is console version of the program a2sagent.exe in the installation directory. It supports command line arguments and can be called from any script. So, it is possible to write a simple batch script and execute it recurrently using Windows Task Scheduler. Please refer to 'Command line' article of the product documentation to learn more about a2sagent.exe.
Go to Top

How to change destination table name?
You can convert contents of the table as result of SELECT-query with custom name. Click "Add Query" button on "Select tables" wizard page and compose simple query as follows:

SELECT * FROM <original table name>
Then store it with the name you wish to assign. Of course, you need to place actual MS Access table name in the query above.
Go to Top

It is possible to connect to MySQL over SSH with Access-to-MySQL?
Yes, Access-to-MySQL supports SSH connection to MySQL server. Please refer to this article for the further information.
Go to Top

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

    mysql> USE mysql;
    mysql> UPDATE user SET Password = OLD_PASSWORD('some_pass')
        -> 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 Access-to-MySQL.
Go to Top

Why do I see error "The file does not comply with MS Access workgroup information file format"?
The reason is you try to enter name of Microsoft Access database (.mdb, .accdb) in the wrong box on "User-level Security Settings" wizard page. That page is to specify MS Access workgroup information file (*.mdw). If you don't use user-level security just leave all fields empty. The name of Microsoft Access database must be entered on "Select Databases" wizard page.
Go to Top

How to uninstall Access-to-MySQL converter?
If you need to remove Access-to-MySQL 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 acc2sql.log
Go to Top


Importing dump files

This article explains how to move dump file contents to MySQL server using the most popular tools.

1. MySQL console client (mysql)
Syntax of the command line depends on the structure of the dump file. If the dump file contains 'CREATE DATABASE' statement you should call MySQL console client as follows:

mysql --host=... --user=... --password=... -vvf < dump_file_name

Otherwise:

mysql --host=... --user=... --password=... -vvf MySQL_database_name < dump_file_name

where 'MySQL_database_name' is a name of an existing database.

2. phpMyAdmin
Note, that phpMyAdmin allows to import dump file into an existing MySQL database only, so the dump file should not contain 'CREATE DATABASE' statement.

Step 1. Click on the appropriate database name in the left frame.

Step 2. Click 'SQL' tab on the top of the screen.

Step 3. Enter full path to the dump file in 'Location of the textfile' field or click 'Browse' button to locate it through the directory tree.

Step 4. Click 'Go' button.


Merging and Overwriting

If you try to convert MS Access database into an existing MySQL database, Access-to-MySQL 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 MySQL database with MS Access data. Note that all data contained in MySQL database will be lost.
  • Select 'Overwrite existing tables only' option to replace the contents of MySQL tables having the same names as tables in source MS Access database.
  • Select 'Skip existing tables only' option to not convert existing tables.
  • Select 'Merge' option to merge MS Access data into the destination MySQL database. Access-to-MySQL implements quite simple merging algorithm. If a table has primary key or an unique index, the program inserts into MySQL database only those records whose key/index value does not exist in the destination MySQL table. Otherwise, Access-to-MySQL appends the whole table contents to the corresponding MySQL table. If the structures of source and destination tables are different, the program transforms each record of the source table so that it conforms to the destination table structure.
  • Select 'Synchronize' option to update MySQL table using MS Access data. See Synchronization article for more information.

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


MySQL dump file settings

On this wizard screen you can specify how the resulting dump file should be constructed. There are following settings available:

  • MySQL version - this parameter determines the syntax of MySQL statements being used in output dump file. 'Old' versions of MySQL (before 3.23.6) put more restrictions on database objects names. Note that 'new' syntax is not accepted by 'old' versions of MySQL. Use 'old' syntax whenever you are not sure about the version of your MySQL server.
  • Line breaks - this parameter specifies how to separate text lines in the dump file. If the destination MySQL server is running on MS Windows platform, you should select 'MS Windows style' option. Otherwise select 'Unix style'.
  • Add lock for write around 'INSERT' statements - it makes importing the contents of the dump file into a database much faster. To use this feature in MySQL 4.0 and higher you need global LOCK TABLES privilege and SELECT privilege on the involved tables. In MySQL 3.23 and earlier you need to have SELECT, INSERT, DELETE and UPDATE privileges for the tables.
  • Insert 'CREATE DATABASE' statement - select this option to put 'CREATE DATABASE' statement in the resulting dump file. This setting doesn't take effect without the option below activated.
  • Insert 'CREATE TABLE' statements - select this option to put 'CREATE TABLE' statements in the resulting dump file. You should always select it in order to obtain a valid dump file. The only exception is appending the data to existing tables.
  • Insert comments - select this option to add brief comments. It makes the resulting dump file more readable.

See Importing dump files article to learn how to move dump file contents to MySQL server.


Profile

Profile is a set of parameters that entirely defines the program session. It includes MySQL connection characteristics (host, user name, etc) and all of the necessary information for MS Access database being converted (MySQL destination name, MS Access database password and "convert table definitions only" flag). 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.


Quick Launch

Once you stored conversion settings into profile you can run the program in Quick Launch mode. Just launch Windows Explorer and double-click on the profile description (.a2s) file with left mouse button. This will cause Access-to-MySQL wizard to launch conversion loading the necessary settings from the specified profile. Then you will be taken right to the screen with progress of conversion without entering all conversion settings.

See Profile article for more information about profiles.


Security Settings

On this wizard screen you can provide necessary information to open MS Access databases protected with user-level security. First, you should enter path to MS Access workgroup information file (*.mdw) which stores the information about all users and workgroups. Usually this is a file called system.mdw in the directory where you installed Microsoft Office applications. Then you should specify name and password for the user-level security account.

If you don't use MS Access user-level security just leave all fields empty.


Select Databases

On this wizard screen you should enter the source MS Access database path and MySQL database name or path to a dump file (depending on which conversion scenario you choose). If you specify the name of existing MySQL database or dump file it will be overwritten.

If the source MS Access database is password protected you should select "Use database password" checkbox and enter the password in the field below.


Select Tables

On this wizard screen you can manage the list of MS Access 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 MS Access table for converting into MySQL 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 MS Access 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 migrated to MySQL server. See Using Queries article for the further information.

To edit selected table or query double click the corresponding item in "Selected tables" list box. If it is a table, dialog box appears to edit table attributes and to define custom type mapping. See Edit Table article for the further information. If selected item is a query, you will see dialog box to edit SQL-statement of the query.


Select Views

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


Synchronization

Access-to-MySQL implements synchronization as combination of insert MS Access records that are missing in MySQL table and update existing MySQL records with MS Access data. For synchronization purpose source and destination tables must have identical structures and have primary key or unique index defined.

Assume both MS Access and MySQL databases have table "People" defined as below:

    Table1(
    ID Integer NOT NULL,
    FName Text(50) / VARCHAR(50),
    LName Text(50) / VARCHAR(50),
    EMail Text(50) / VARCHAR(50),
        Primary Key (ID)
    );

This table has the following contents in MS Access database:

2 | Andrew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com

In MySQL database:

1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@yahoo.com
After migrating MS Access data to MySQL in "Synchronize" mode MS Access table is kept untouched and MySQL table is modified as (pay attention to the cell marked red):
1 | John | Smith | j.smith@msn.com
2 | Andew | White | a.white@corporation.com
3 | Phil | Johnson | p.johnson@gmail.com


Troubleshooting

While working with Access-to-MySQL 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:".

  • Error message "Cannot recognize MS Access database format..."
  • Error message "Row size is too large..."
  • Error message "The program does not support authentication protocol requested by server..."
  • Error message "Transferred value is too big. Increase MySQL max packet size and try again..."
  • Error message "Duplicate field values being used for an unique index were found..."
  • Error message "Cannot extract relationships information..."
  • In the resulting MySQL database all MS Access hyperlink fields appear as strings that look like some_text#some_other_text#.
  • Error message "Can't connect to MySQL server on <host name or IP>".
  • The program converts some of MS Access date fields into MySQL CHAR(19) data type.
  • Error message "Access denied for user: <user-name>"
  • Some table names and/or field names in the resulting MySQL database differ from the original.
  • Error message "Access denied for user: <user name> to database <database>"
  • Error message "Error dropping database (can't rmdir..."
  • The program does not convert default values for MS Access MEMO fields.
  • Some empty fields have appeared in the resulting MySQL database with nonempty values.
  • The program converted all data, indexes and queries fine but where are my MS Access forms?
  • I cannot uninstall Access-to-MySQL.

P: Error message "Cannot recognize MS Access database format"
R: The first reason of this error is that soure file is not a valid MS Access database. The second reason could be missing appropriate Microsoft Data Access (DAO) libraries on the system to read the database. In this situation it is recommended to install Microsoft Access 2010 Runtime as it contains correct version of DAO libraries:

http://www.microsoft.com/en-us/download/details.aspx?id=10910

NOTE: it must be 32-bit installation pack, even if your system is 64-bit. If the error persists, please contact Intelligent Converters support service at service@convert-in.com
Go to top

P: Error message "Row size is too large..."
R: MySQL has more strict limitation on row size than MS Access. The maximum row size, not counting columns having BLOB or TEXT type, is 65535. You have to change some columns to MEMO type in the "problem" MS Access table. If you are not sure about how to proceed on the matter or afraid of damaging your data, please contact us at service@convert-in.com to get free professional consultation.
Go to top

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 Access-to-MySQL. You can use OLD_PASSWORD function to make the certain account compatible with Access-to-MySQL. Login to MySQL server with administrator privileges and run the following queries:

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

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

P: Error message "Transferred value is too big. Increase MySQL max packet size and try again..."
R: The error mostly caused by BLOB values that are larger than max_allowed_packet. Sometime you may see "packet too large" as part of the error message, and that confirms that you need to increase max_allowed_packet value. You can increase MySQL max allowed packet size with one of the following options:

  1. restarting server daemon as
    mysqld --max_allowed_packet=16M
  2. running the following query though mysql client
    SET GLOBAL max_allowed_packet=16M
  3. setting max_allowed_packet in option file ("my.ini" or "my.cnf")
    [mysqld]
max_allowed_packet=16M
Go to top

P: Error message "Duplicate field values being used for an unique index were found..."
R: This error means that an attempt has been made to insert a record with a duplicate unique key. When migrating MS Access data into a new MySQL 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 MS Access data to an existing MySQL database, you should remove the conflicting unique index in the destination MySQL database.

NOTE: this error may indicate a general incompatibility issue. For instance, MS Access allows auto increment column to have 0-value while MySQL does not. When you attempt to insert 0 into auto_increment column, MySQL replaces it with first acceptable (non-duplicated) value. Following this way sequence 0,1,2,3 will be converted into 1,1,2,3 that rises the error because duplicate values occur under the primary key. The only way to fix the problem is to remove "auto increment" attribute in MS Access table. If you are not sure whether it affects your situation, please contact us at service@convert-in.com
Go to top

P: Error message "Cannot extract relationships information..."
R: You need read-permissions on system tables to be able to extract relationships information. Follow these easy steps:
1. open the database in Microsoft® Access
2. go to 'Tools'>'Options' menu
3. in the appeared dialog box select "View" tab and activate "System Objects" check box
4. push "Apply" button and close the dialog
5. select "MSysRelationships" table and go to 'Tools'>'Security'>'User and Group Permissions'
6. in the appeared dialog box select "Read Data" checkbox
7. push "Apply" button and close the dialog
8. close Microsoft® Access
Go to top

P: In the resulting MySQL database all MS Access hyperlink fields appear as strings that look like some_text#some_other_text#.
R: MySQL has no data type similar to hyperlink. That's why the only way to save the entire information is to import it into plain text. Access-to-MySQL converts each hyperlink field into MySQL TEXT field formatted as <URL name>#<URL address>#.
Go to top

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.
Go to top

P: The program converts some of MS Access date fields into MySQL CHAR(19) data type.
R: Unlike MySQL, MS Access has no separated types DATE, TIME and DATETIME. Access-to-MySQL tries to map date columns properly using the content analysis. When it is impossible to perform unambiguous conversion (e.g. column contains pure dates and pure time values), the program converts MS Access date fields into their string representation. This approach preserves ordering relationship inside date/time column, and even you can use the same MySQL queries as if it was a date/time field.
Go to top

P: Error message "Access denied for user: <user-name>"
R: You will not be able to connect to the target MySQL server until you have the appropriate permissions. Ask your MySQL server administrator to give you sufficient permissions to create new databases on the target MySQL server. See VServer User Notes article for related information.
Go to top

P: Some table names and/or field names in the resulting MySQL database differ from the original.
R: MS Access and MySQL accept different sets of characters for table names and field names. When Access-to-MySQL converts a name containing prohibited characters it replaces them with '_' in the destination database.
Go to top

P: Error message "Access denied for user: <user name> to database <database>"
R: If the destination database already exists, you should have necessary privileges to add/remove tables. If the destination database doesn't exist, you should have sufficient privileges to create new databases on the target MySQL server. See VServer User Notes article for related information.
Go to top

P: Error message "Error dropping database (can't rmdir..."
R: It means that destination database exists and cannot be deleted for some reason. You can either select "Overwrite existing tables only" mode or specify another name for destination MySQL database.
Go to top

P: The program does not convert default values for MS Access MEMO fields.
R: Access-to-MySQL converts MEMO fields into MySQL TEXT type as the most appropriate on a range of acceptable values. TEXT and BLOB fields cannot have default values.
Go to top

P: Some empty fields have appeared in the resulting MySQL database with nonempty values.
R: Unlike MySQL, MS Access allows the fields declared as NOT NULL to have empty value. To prevent conflicts during the conversion process Access-to-MySQL assigns the default value to each of those fields.
Go to top

P: The program converted all data, indexes and queries fine but where are my MS Access forms?
R: Access-to-MySQL doesn't convert MS Access forms because MySQL doesn't support forms.
Go to top

P: I cannot uninstall Access-to-MySQL.
R: If you need to remove Access-to-MySQL 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 acc2sql.log
Go to top

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


Types Mapping

Microsoft Access and MySQL have similar set of data types. Some of them are compatible with each other, while others are not. When designing the schema of custom (non-standard) types mapping, it is necessary to remember the following compatibility matrix for popular data types of Microsoft Access and MySQL. In the tables below all valid type mappings are marked green (with restriction on data if any), every prohibited or dangerous type mapping is marked orange.

Into Text / Binary

 | Bool | Byte | Integer | Long Integer | Single | Double | Currency | Decimal | Text | Date/Time | OLE | Memo
CHAR | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | Possible truncation of text | dest. length >= source length |  | Possible truncation of text
VARCHAR | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | dest. length >= source length | Possible truncation of text | dest. length >= source length |  | Possible truncation of text
BINARY |  |  |  |  |  |  |  |  |  |  | Possible truncation of data |
VARBINARY |  |  |  |  |  |  |  |  |  |  | Possible truncation of data |
TINYBLOB |  |  |  |  |  |  |  |  |  |  | Possible truncation of data |
TINYTEXT |  |  |  |  |  |  |  |  |  |  |  | Possible truncation of data
BLOB |  |  |  |  |  |  |  |  |  |  | Possible truncation of data |
TEXT |  |  |  |  |  |  |  |  |  |  |  |
MEDIUMBLOB |  |  |  |  |  |  |  |  |  |  | Possible truncation of data |
MEDIUMTEXT |  |  |  |  |  |  |  |  |  |  |  |
LONGBLOB |  |  |  |  |  |  |  |  |  |  |  |
LONGTEXT |  |  |  |  |  |  |  |  |  |  |  |

Numeric

 | Boolean | Byte | Integer | Long Integer | Single | Double | Currency | Decimal
BOOLEAN |  |  |  |  |  |  |  |
TINYINT (-128 ... 127) |  | If source is out of range -128...127 use UNSIGNED | Possible truncation of data | Possible truncation of data |  |  | Possible truncation of data | Possible truncation of data
SMALLINT |  |  |  | Possible truncation of data |  |  | Possible truncation of data | Possible truncation of data
INTEGER |  |  |  |  |  |  | Possible truncation of data | Possible truncation of data
BIGINT |  |  |  |  |  |  | Possible truncation of data | Possible truncation of data
DECIMAL |  |  |  |  | Possible loss of precision | Possible loss of precision |  |
FLOAT |  |  |  |  |  | Possible loss of precision | Possible loss of precision | Possible loss of precision
DOUBLE |  |  |  |  |  |  | Possible loss of precision | Possible loss of precision

Date / time

Microsoft Access date/time colums should be mapped in MySQL datetime. In case of mapping in MySQL date or type truncation of data is possible. String representation of MS Access date/time values can also be stored into any kind of MySQL text types (see 1st table on this page). Other kind of mapping for MS Access date/time type is not allowed.


Using Queries

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

   Table1(
    ID Integer,
    FName Text(50),
    LName Text(50),
    Birthday Date,
    Notes Memo
   );

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 not isnull(Notes)

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


VServer User Notes

If you are using Virtual Server hosting you probably have no sufficient privileges to create new databases on the target MySQL server. For this case we recommend the following approach:

  1. ask your Virtual Server administrator to create blank database for you
  2. run Access-to-MySQL and select this database as the conversion target
  3. click "Yes" when you will be asked if you'd like to overwrite existing MySQL database

When you complete these steps, the entire contents of the source MS Access database will be imported into the specified MySQL database.


Contents

Add Query

Command Line

Connecting to MySQL over SSH

Connect to MySQL Server

Conversion Settings

Customize Conversion

Edit Table

Frequently Asked Questions

Importing Dump Files

Introduction

Merging and Overwriting

MySQL Dump File Settings

Profile

Quick Launch

Security Settings

Select Databases

Select Tables

Select Views

Synchronization

Troubleshooting

Types Mapping

Using Queries

VServer User Notes