Ask a question »

Export Access Database to MySQL

April 2015


Export Access Database to MySQL





This tip will show you the way of exporting an Access database to MySQL! Make sure to follow these steps precisely!

Establish a connection plase type ODBC database server


1.1) download and install the driver CONNECTOR / ODBC 3.51 MySQL: http://dev.mysql.com/downloads/connector/odbc/3.51.html
(Choose the version according to the OS, Windows, Linux, Solaris ,...)

1.2) Sub WinXP/2k, go to Control config.> Administrative Tools> Data Sources (ODBC) and click on the "ODBC Drivers" to see the list of ODBC drivers, if the line "MySQL ODBC 3.51 driver "appears is that the driver is installed.

1.3) click on the tab "User Data Sources" and then click the Add button, the list of drivers appears, select "MySQL ODBC 3.51 driver" then click "Finish". This creates an ODBC connection that is a channel for transmitting data to ONE database server MySQL. Can create multiple ODBC connections to the same MySQL server. Every connection manage a single database.
This will provide information to MySQL using all sorts of software (via ODBC) which relate to databases (ACCESS and others).

1.4) The window parameter setting of the ODBC connection to MySQL appears with the tab "Login":

Data source name: ODBC connection id / MySQL.
It is a name that lets you know which database is managed by this connection.
eg MaBaseFacturesSurMySQL

Description: (Optional) Comments on this connection

Server: localhost (or IP address of the server if the MySQL server is on another machine)

User: user name with access to your database
Password: user password

Database: Here you choose the database to be managed by this connection.
eg Invoices

In the "Connect option can optionally set the port number of the MySQL server if it is not the standard port.

In the tab "Advanced" options were on the settings to make when transferring data (type, disable transactions ,...).
Click "OK" in the list of data sources "MaBaseFacturesSurMySQL" should appear.

Using ACCESS to manage a MySQL database


2.1) When ACCESS started, click on File> Open in the File Open window, click on the list "Files of type" and select the type: ODBC Databases ().
The window for selecting the data source appears, click on the tab "Machine Data Source" to display the lists of data sources.
(In this window, you can also create a new connection by clicking the New button that boosts the steps 1.3 and 1.4)

Select "MaBaseFacturesSurMySQL" and click "OK".

2.2) A window allows to choose the tables you want to manage from ACCESS.Sélectionner the table or tables desired then "OK".

2.3) it then worked as an Access database with the exception that there 's limitations due to the difference between MySQL and ACCESS so you can not manage everything from the interface of ACCESS.
Tip: After you open a database, right-click on a table and create a shortcut to the desktop, it will avoid each time steps 2.1 and 2.2.Vérifier that changes are made into account directly on the MySQL server (without using ACCESS).

Export an Access database to MySQL


3.1) Open Access database (*. mdb) and select a table.
3.2) Click on File> Export and select the file type "ODBC Database ()" then "OK".
3.3) Set the destination name of the table (usually the same name is allowed only in ACCESS but you can change it if necessary) and then "OK".
3.4) Click on Data Sources machine and choose "MaBaseFacturesSurMySQL" then "OK".

Import data from a MySQL database in an Access database


3.1) Open Access database (*. mdb).
3.2) Click on File> Get External Data> Import ... and select the file type "ODBC Database ()" then "OK".
3.3) Click on Data Sources machine and choose "MaBaseFacturesSurMySQL" then "OK". Choose the tables to import and then "OK".
For unlimited offline reading, you can download this article for free in PDF format:
Export-access-database-to-mysql.pdf

See also

In the same category

Published by aakai1056.
This document entitled « Export Access Database to MySQL » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.