Connecting Microsoft Access to SQL Azure

How to connect Microsoft Access to SQL Azure.

I have already written on many levels 100 developer's articles on SQL Azure. You can read them here. I thought that if my SQL Azure blog post series does not have a post on "Connecting SQL Azure to Microsoft Access" then it cannot be called a complete post series. So I gave a try and came up with this article. Before you start reading this step by step walkthrough, I request that you first read some of previous articles on SQL Azure here

As in my other SQL Azure article, I am going to connect to the School Database again. To connect Microsoft Access with the SQL Azure School database follow the steps below.

1. Open Microsoft Access and create a blank Database.
   
   
    
2. Click on the Create button in the left to create a blank database.
   
   
    
3. From the top select External Data and ODBC Database option.
   
   
    
4. Select the second radio button.
   
   
    
5. A new panel will be shown for selecting the database server name.
   
   
    
6. Click on New and a new panel will pop up. To connect with the SQL Azure select SQL Server Native Client 10.0.
   
   
    
7. Click on the Advanced button. Here you need to give ODBC connection string to SQL Azure Database. Login to SQL Azure portal and copy and paste the ODBC connection string here.
   
   To get the Connection String, select the Database and at the right in the property you will get the Connection String.
   
   
   
   Copy the ODBC connection string from here:
   
   
   
   Copy and paste the ODBC connection string in this panel after selecting the advanced option.
   
   
    
8. Click on ok and give a name to the connection. I am giving the name abc.
   
   
   
   Click next and select Finish. You will be prompted with a new panel as below.
    
9. Uncheck the Use Trusted Connection check box. Provide a password and click OK.
   
   
    
10. Oh My God and here you are; you have all the tables from the SQL Azure Database listed to select in Microsoft Access.
    
    
    

There are a few points that you need to cross check

1. See the Firewall rules of the SQL Azure Database server.
2. Edit the Firewall rule range to cover the IP address of your client machine.
3. Check that the 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 article was useful.