I have already posted on many level 100 developers articles on SQL Azure. You can read them here. I thought if my SQL Azure blog post series does not have a post on “Connecting SQL Azure to Microsoft Access “then it cannot be called as complete post series. So I gave a try and came up with this blog post. Before you start reading this step by step walkthrough, I request you to first have a read on some of previous blog posts on SQL Azure here
As my other SQL Azure post, I am going to connect School Database again. To connect Microsoft Access with SQL Azure School database follow the steps as below.
1. Open Microsoft Access and create a blank Database.
2. Click on the Create button in left to create a blank database.
3. From top select External Data and ODBC Database option
4. Select the second radio button.
5. New panel will come to select Database server name.
6. Click on New and you will pop up with a new panel. To connect with SQL Azure select SQL Server Native Client 10.0
7. Click on Advanced option. Here you need to give ODBC connection string to SQL Azure Database. Login to SQL Azure portal and copy paste ODBC connection string here
To get connection string, select Database and at right in the property you will get Connection string.
Copy ODBC connection string from here,
Copy paste ODBC connection string in this panel after selecting advanced option.
8. Click on ok and give a name to connection. I am giving name abc.
Click next and select Finish. You will prompt with new panel as below
9. Uncheck Use Trusted Connection check box. Provide password and click OK
10. Oh My God and here you are, you have all the tables from SQL Azure Database listed to select in Microsoft Access.
Few points you need to cross check
1. See the Firewall rules of SQL Azure Database server.
2. Edit the Firewall rule range to cover IP address of your client machine.
3. Check SQL Server 2008 R2 native client is installed on your client machine.
4. While copying ODBC Connection string, you have changed the password.
Thanks , I hope this post was useful.