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 $10 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 SQL Server Management Studio (SSMS) for SQL Server 2008 R2 on
your machine
- 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 or 2010), 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 SQL Server on
your PC, just the SQL Server Management Studio (SSMS) to manage the hosted
database. This comes with SQL Azure. The instructions from their
web site says:
The release of SQL Server 2008 R2 adds client tools support for SQL
Azure, including added support through SQL Server Management Studio
(SSMS). SQL Server Management Studio can be used to manage SQL Azure and
can be downloaded for free
here
along with SQL Server Express. Additionally, SQL Server 2008 R2 and SQL
Server Express have full support for SQL Azure – in terms of seamless
connectivity, viewing objects in the object explorer, SMO scripting, and
more.
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 IP Address is Listed on SQL Azure
For security reasons, SQL Azure (like standard SQL Server) lets
you specify the IP addresses to allow direct interaction with the database.
You'll need to specify that in SQL Azure's administration tools before
proceeding. By default, it'll include your current IP address. You'll need
to specify the range of IP addresses of other users you anticipating linking
to its tables or deal with that later when you know their IP addresses.
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 SQL Server 2008, R2
version), 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:
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:
Select "SQL Server Native Client 10.0" and press [Next]. You'll 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 EzUpData project. You'll need to know
the server name from SQL Azure which will look something like *.database.windows.net:
After pressing [Next], provide the login ID and password to your SQL
Azure database:
When you press [Next], if your entries are valid, 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
should appear:
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 installed, you can then
link to tables in the database.
Link to an ODBC Database from Microsoft Access 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
|
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):
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:
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:
- The Help button gives no relevant information in either Access 2007
or 2010
- 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 2010 and 2007.
Deploying Microsoft Databases Linked to a SQL Azure
If your Access database is to be deployed to others, your users don't
have to have SQL Server installed on their machine but they do need to have
the SQL Server ODBC driver installed. You simply need to run the SQL Server
2008 R2 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.
Blog about it with me
here.
 Additional Resources
for Microsoft Azure and SQL Azure
FMS Technical Papers
 Microsoft
Azure, SQL Azure, and Access in Action
Microsoft Resources
|
Email Newsletter
Facebook
Twitter