<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Access and SQL Azure Databases

Published on
11,412 Points
4,912 Views
5 Endorsements
Last Modified:
Approved
Community Pick
Luke Chung
Helping people and organizations make better data based decisions to achieve their missions
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

From a Microsoft Access database, you can connect to a SQL Azure database and use those tables the same way you could link to SQL Server database 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 say:

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.

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.

More Details and Related Resources

For more details on creating the ODBC file to your SQL Azure database, read my paper on Linking Microsoft Access Databases to SQL Azure.

For distributing your database to end-users, read my paper on Deploying Microsoft Databases Linked to a SQL Azure Database to Users.

For an overview of what I think Azure brings to our community, please read my article entitled Microsoft Azure and Cloud Computing...What it Means to Me and Information Workers.

To read my blog related to this article, visit Microsoft Access and SQL Azure Blog.

Hope this helps.
5
Author:Luke Chung
1 Comment
LVL 56

Expert Comment

by:Mark Wills
Good Article.

Went to a MS meeting the other day. Azure and the cloud are very important initiatives for Microsoft, so great to revisit this Article and vote "Yes".

Would like to see more here in EE :)
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month