Connecting Microsoft Access to SQL Azure

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.

clip_image002

2. Click on the Create button in left to create a blank database.

clip_image003

3. From top select External Data and ODBC Database option

clip_image005

4. Select the second radio button.

clip_image007

5. New panel will come to select Database server name.

clip_image008

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

clip_image009

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.

clip_image010

Copy ODBC connection string from here,

clip_image011

Copy paste ODBC connection string in this panel after selecting advanced option.

clip_image012

8. Click on ok and give a name to connection. I am giving name abc.

clip_image013

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

clip_image014

10. Oh My God and here you are, you have all the tables from SQL Azure Database listed to select in Microsoft Access.

clip_image015

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.

6 thoughts on “Connecting Microsoft Access to SQL Azure

  1. Pingback: Dew Drop – April 22, 2011 | Alvin Ashcraft's Morning Dew

  2. Pingback: DotNetShoutout

  3. Pingback: Microsoft Flight Simulator X Downloads | Buy Pro Flight Simulator

  4. that’s great for connecting to the master database which is easy. what about a database that is not the master? that’s what I can’t get to work.

  5. Pingback: Monthly Report April 2011: Total Posts 21 « debug mode……

  6. To connect to a specific database.

    Step 7,

    **if in the SQL Azure web portal you navigate go to your database and click ‘Show connection strings’ then the connection string will already contain the parameter for that database, but anyhow make sure the connection string has this in it, see below

    ;Database=mydb

    replace mydb with the name of your database.. don’t miss the semi column at the start, as parameters are separated by these..

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