Follow this easy step by step tutorial to learn how to open MySQL database in Microsoft Access, through an ODBC connection.
Don’t forget to check out our site http://howtech.tv/ for more free how-to videos!
http://youtube.com/ithowtovids – our feed
http://www.facebook.com/howtechtv – join us on facebook
https://plus.google.com/103440382717658277879 – our group in Google+
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.
Finally, click on the Test button.
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.
❗ Subscribe To Our Main YouTube Channel
https://www.youtube.com/user/howtechpc
how about a demo on inserting data to mysql using Microsoft Access?
What if, in the ODBC Admin Window, System DSN tab, there IS NO MySQL ODBC Driverf option? That's what I'm looking at. ALSO, it seems you are only connecting to a MySQL database on my local computer. What if we need to connect to a MySQL database created via PHPMyAdmin on a website? This is something that has me banging my head against the wall.
THANKS Alot
I am making a database in msql and I have a password protected Microsoft Access file and that is pissing me off.can you please help me by guiding how do I open it.
I have lost the password
Say you made a data base in MySQL and had foreign keys with relationships…would those transfer over to access?
i cant find my created data source in Machine Data Source in MS Access
The Server should be an IP address or the server name for the computer where MySQL server is
installed. You can use localhost only if MySQL is installed on the same machine.
Click Test button to make sure the settings you entered are correct.
Thanks mate great video.
thank you. It works for me.
Loved it. Short and sweet with no bullshit in between.
This doesnt work.
I cant find MySQL in "Create New Data Source"
Glad to hear it's working now! 🙂