Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud)

Written by: Luke Chung, President

About Cloud Computing

We at FMS are very excited about cloud computing and started developing solutions using Microsoft Azure including SQL Azure well before it was released to the general public. I feel cloud computing represents the next big platform change in the software industry and the most significant transformation since the introduction of the Internet in the mid-1990's. It will literally revolutionize the way we create, test, host, and deploy applications, and can do it at a fraction of what it costs us today.

Read my article Microsoft Azure and Cloud Computing...What it Means to Me and Information Workers to learn more about how I see cloud computing impacting our community.

How Cloud Computing Applies to the Microsoft Access Community

Cloud computing will be a huge benefit to the information worker and Access community. Instead of worrying about the hardware and deployment issues around applications, one can focus on building the solution and using the enterprise quality cloud platforms which previously didn't exist or were prohibitively expensive and difficult to use. With Microsoft Access 2010 and SharePoint 2010, Access applications (in limited form) can be deployed over the Internet. With Microsoft Windows Azure and SQL Azure, one can create .NET applications and/or SQL Server databases in the cloud.

Using Microsoft Access to Connect to the SQL Server in Azure

The other huge benefit of Microsoft Azure is that it can host SQL Server databases for you in the cloud (on multiple servers completely transparent to you). At a cost of only $5 per month for a database up to 1 GB in size, it's very reasonably priced.

From a Microsoft Access database, you can connect to the database and use those tables the same way you could link to SQL Server databases on your network or SQL Express on your desktop. For a fraction of the cost of buying and setting up a SQL Server box on your network, you can have Microsoft do it for you without worrying about licenses, downtime, hardware, etc., and it's available over the Internet to anyone you give the credentials for logging into it. It's pretty simple:

Open an Azure account and create a SQL Azure database
Install Microsoft SQL Server Management Studio (SSMS) for Microsoft SQL Server. The latest version is Microsoft SQL Server 2017, though Microsoft SQL Server 2008 R2 or 2012 can also work.
Use the ODBC administration tool to create a file containing the connection to the SQL Azure database
From a database opened in Access (2003, 2007, 2010, 2013 or 2016), use the ODBC connection to link to the SQL Azure tables and views

Installing SQL Server on Your Machine

There's a bit of confusion around the installation of SQL Server. As the developer, when you use SQL Azure, you don't need to install the full SQL Server product on your PC, just the SQL Server Management Studio (SSMS) to manage the hosted database. Alternatively, you can install the free SQL Server Express version. Visit our Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads page for more information and download links.

Installing SQL Server ODBC Drivers on Your Users' Machines

The users of your Access database linked to SQL Azure won't even need that. They simply need to have the ODBC driver installed on their machine. For more info, read my paper on Deploying Microsoft Databases Linked to a SQL Azure Database to Users without SQL Server Installed on their Machine.

Make Sure Your Users' IP Addresses are Listed on the SQL Azure Firewall

For security reasons, SQL Azure (like standard SQL Server) lets you specify the IP addresses for direct interaction with the database. This is required with SQL Azure under Firewall rules. From the Azure portal for your database, choose "Set server firewall", then add the IP addresses. For more information, visit this Microsoft web page on managing SQL Azure firewall rules.

Not sure what your IP address is? Use the WhatsMyIP.org site to get your current IP address.

Creating a File DSN to Connect to a SQL Azure Database

The most complicated part of linking your Access database to SQL Azure is configuring your ODBC connection. Assuming you've taken care of steps 1 and 2 above (created an Azure account with SQL Server and installed a recent version of Microsoft SQL Server Express, you're ready to run the ODBC administrative tool which can be found in your Control Panel.

Run the ODBC Administrative Tool

When you launch it, the ODBC Data Source Administrator screen appears.

ODBC Data Source Administrator screen

You can define your DSN (data source name) either at the user, system (machine), or file level. The first two are fine if you'll only be connecting to the SQL Azure database from your machine. To easily share the connection information with other machines, select the File DSN tab which creates a file that you can share over your network or send to other people:

File DSN tab for the ODBC Data Source Administrator

Press the [Add] button to create a new data source.

Create New Data Source, ODBC File DSN

Install the ODBC Driver that Matches Your Windows Bitness (Not Office)

Download, install and use the latest Microsoft SQL Server drivers since they are backward compatible. You can use an older version if your users already have it installed on their PCs and you aren't using the newer SQL Server features since that release. You can install multiple drivers on the same PC.

There are 64 and 32 bit versions of the ODBC drivers. Install the one that matches your Windows operating system, NOT the bitness of Office/Access.

The Bold Face is the name of the driver used in the connection string to SQL Server:

ODBC Driver 17.0 for SQL Server released for Microsoft SQL Server 2017/SQL Server 2019 (Current Version 17.6) (Download)
ODBC Driver 13.0 for SQL Server released for Microsoft SQL Server 2016 SP1 and SQL Azure (Current Version 13.1) (Download)
ODBC Driver 13.0 for SQL Server released for Microsoft SQL Server 2016 (Download)
SQL Server Native Client 11.0 released for Microsoft SQL Server 2012 (Download)
SQL Server Native Client 10.0 released with Microsoft SQL Server 2008, R2

For a history of SQL Server versions, visit our page Microsoft SQL Server Express: Version Comparison Matrix and Free Downloads.

Do not choose the legacy "SQL Server" or "SQL Native Client".
Significant performance problems are associated with using those old drivers against newer versions of SQL Server.

Then specify the name of the file to store the information and press [Next] and [Finish] to verify it. Then you begin to specify the Microsoft SQL Azure elements which is similar to setting up any ODBC DSN for SQL Server.

Create a New Data Source to SQL Server

We'll create a sample DSN for our project. You'll need to know the server name from SQL Azure which looks something like *.database.secure.windows.net:

Create a New Data Source to SQL Server on Azure

After pressing [Next], provide the login ID and password to your SQL Azure database.

SQL Azure Database Login ID and Password

When you press [Next] with valid entries, this screen appears:

SQL Azure Database Name Selection

Select the name of the database you created on SQL Azure. The default is master, but hopefully your database is named something more descriptive. Press [Next] to reach the final screen:

SQL Azure ODBC Choose Use strong encryption for data

Make sure you check the "Use strong encryption for data" option, then press [Finish]. A screen will appear to let you test your settings. Press the [Test Data Source] button. If everything is okay, a screen like this appears:

Linking Microsoft Access to SQL Server Tables in Azure

Now that you've created a file with the DSN for your SQL Azure database, you're ready to link an Access database to tables in your Azure database. From Access, on a machine with SQL Server 2008, R2 (or later) installed, you can then link to tables in the database.

Link to an ODBC Database from Microsoft Access 2016, 2013, 2010 and Access 2007

Depending on which Access version you're using, select the ODBC Database option under the External Data ribbon:

Microsoft Access 2010 and Later

Microsoft Access 2007 Link to ODBC Database
Microsoft Access 2007

The dialog box appears to import or link to the ODBC database. In our case, we'd like to link to the database so we always have the latest data:

Select Data Source

The next step is to specify the data source by selecting DSN File we created (if you created the ODBC setting for user or system, use the Machine Data Source tab):

Select data source (DSN File) with SQL Azure database information

Select Tables

It'll prompt you again for the password. Once you provide that, the list of tables from the SQL Azure database are presented. Select them like you would for any other data source. If you want to avoid entering the database password every time you open the linked table in Access, be sure to check the Save Password option:

Select the SQL Azure tables to link to

By choosing the option to save the password in the Access database, a security hole is created. You'll need to decide if this risk is worth taking over the convenience of not entering the password each time. If you choose to save the password, you are prompted that this is a security issue:

Linked Table Save Password Prompt Dialog

There are a few problems with this dialog box:

The Help button gives no relevant information
The dialog box appears for every table you selected, so you need to manually select Save Password for each one

Once you get through that (which has nothing to do with SQL Azure), you'll find your database has linked tables and views to SQL Azure. Open and use the tables and views just like any other SQL Server data source.

Link to an ODBC Database from Microsoft Access 2003 or Earlier

In Microsoft Access 2003, linking to a SQL Server database is a bit different. From the database container, right click and select Link Tables. When the Link dialog appears, select "ODBC Databases ()" in the Files of type:

After this, the dialog to Select the Data Source File appears which is similar to the steps described earlier for Access 2013, 2010 and 2007.

Deploying Microsoft Databases Linked to a SQL Azure

If your Access database is deployed to others, your users don't need to install SQL Server on their machine but they do need to install the SQL Server ODBC driver. Simply run the SQL Server Native Client Setup.

For more details, read my paper on Deploying Microsoft Databases Linked to a SQL Azure Database to Users without SQL Server Installed on their Machine.

Conclusion

With an Azure account, you'll be able to use SQL Azure to create SQL Server databases cheaply and make them available across the internet in minutes. Imagine what you can do with Access having that kind of scalability and enterprise quality support and bandwidth.

Hope this helps. Good luck and I hope to learn what you're doing or would like to do with Access and Azure.

Have any questions or comments? Visit our blog post, Microsoft Access and Cloud Computing with SQL Azure Databases.