Linking a table in Microsoft Access to MySQL table

MySQL is my favorite DBMS but it lacks a nice reporting interface. There are some reporting systems designed to generate customized reports out of MySQL databases but I want to show you a Microsoft Access based solution for using its awesome tools.

First you need to prepare your system by installing necessary drivers:

1-  Depends on the architecture of the MS-Access installed on the system (x86/x64) download and install proper edition of MySql ODBC driver for windows from http://dev.mysql.com/downloads/connector/odbc/

2-  Open ODBC data source administrator and Create a system DNS:

  1. For x32 ms-access: c:\windows\sysWOW64\odbcad32.exe
  2. For x64 ms-access: C:\Windows\System32\odbcad32.exe

3- Goto System DNS and click on Add and select MySQL ODBC x.x ANSI Driver

Image

4- Fill up the fields and use an account on MySQL to connect and finally test the connection

2

5- At the end you should be able to see your record in system DNS

3

The next phase is to actually linking the MySQL database:

1-  Create a blank ms-access db and name it mysql_link.accdb

2-  Goto “External Data” tab and select “ODBC Database” to get the external data linked in ms-access

3- Click on the link to data source

a4

4- In machine data source tab you should be able to see the System DNS you created previously. Select it and click OK

a5

5- Then chose the table you want to link (in our example: “Database_Name”)

a6

6- Chose a record identifier (usually primary key)

a7

Now, you can see a table in list of table which simply linked to MySQL database ( to the table and field you have defined before). It is possible to design forms, queries and reports for this linked table just like a native table.

P.S. Just for security reasons, it is recommended to define a separate user in MySQL and limit the access to tables and fields of desire; and use that user for your link.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s