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.
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.
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:
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.
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.
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.
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.
When you launch it, the ODBC Data Source Administrator screen appears.
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:
Press the [Add] button to create a new data source.
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:
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.
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:
After pressing [Next], provide the login ID and password to your SQL Azure database.
When you press [Next] with valid entries, this screen appears:
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:
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:
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.
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
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:
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):
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:
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:
There are a few problems with this dialog box:
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.
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.
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.
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.