It can be needed in many situations to link our MySQL database to any other database, spreadsheets or reporting tool. This tutorial will focus on one of the same scenario in which we will learn to open MySQL database.
Step 1- Control Panel
To link MySQL with Access, first of all, we would have to make an ODBC connection. For this purpose, open up control panel and click on the System and Security option
Step 2-Administrative Tools
After that, move to the Administrative Tools option.
Step 3-ODBC Connection Option
Click on the ODBC data source option.
Step 4- Making System DSN
ODBC data source admin window will open up. Over here, move to the DSN tab and click on the Add button.
Step 5- Driver for MySQL
Now scroll down the list, choose MySQL ODBC driver and click on the Finish button.
Step 6- Configuring ODBC Connector
A configuration window will open up.
Give a name to the data source, type “local host” in the server field, specify “root” as a user, because it is a default user in all MySQL instances, and write the name of the desired database in the database field.
Step 7- Connection TEST
A message will pop up confirming that the connection is successful. This means that now we would be able to link MySQL with Access.
Step 8- Microsoft Access
Now, open up Microsoft access, click on the Blank database option and let’s create a new database.
Step 9- External Data
After that, move to the “External Data” tab and click on the More option. With that done, a drop down menu will appear on the screen. From the menu, choose the ODBC database option.
Step 10- Linked Table
Within the new window, choose the “Link to data source” option and hit Enter.
Step 11- Machine Data Sources
A window will open up where we will be required to choose the data source connection. Over here, move over to the Machine Data Source tab, choose the same DSN which was created earlier and hit Enter.
Step 12- Selecting tables
After that, it will show us all the tables which our database contains. We can choose any table individually or select all of them.
For this tutorial, we will select all the tables in the database.
Step 13-Database Imported to Access
After that, all of the database tables would appear in Microsoft Access
And that is how to open Mysql database from Access.