FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:

Microsoft Access Developer Center

Table Design

Query Design

Form Design

Form Tips and Mistakes

Form Navigation Caption

Using a RecordsetClone

Synchronize Two Subforms

Multiple OpenArgs Values

Late Bind Tab Subforms

Subform Reference to Control Rather than Field

Tab Page Reference

Shortcut Keys


Combo Box Top Tips

Properties and Validation

Select First Item

Cascading Combo Boxes

Zip, City, State AutoFill

Report Design

Suppressing Page Headers and Footers on the First Page of Your Report

Add the NoData Event

Annual Monthly Crosstab Columns

Design Environment

Adding Buttons to the Quick Access Toolbar

Collapsing the Office Ribbon for more space

VBA Programming

Using Nz() to Handle Nulls

Avoiding Exits in the Body of a Procedure

Shortcut Debugging Keys

Setting Module Options

Math Rounding Issues

Rename a File or Folder

Source Code Library

Microsoft Access Modules Library

Royalty-Free VBA Modules

VBA Error Handling

Error Handling and Debugging Techniques

Error Number and Description Reference

Basic Error Handling

Pinpointing the Error Line

Performance Tips

Linked Database

Subdatasheet Name

Visual Source Safe

Deployment

Runtime Downloads

Simulating Runtime

Prevent Close Box

Disable Design Changes

Broken References

Missing Package & Deployment Wizard

Terminal Services and RemoteApp Deployment

Avoid Program Files Folder

Microsoft Access Front-End Deployment

System Admin

Disaster Recovery Plan

Compact Database

Compact on Close

Database Corruption

Class Not Registered Run-time Error -2147221164

Inconsistent Compile Error

Decompile Database

Bad DLL Calling Convention

Error 3045: Could Not Use

Converting ACCDB to MDB

Cloud and Azure

Cloud Implications

MS Access and SQL Azure

Deploying MS Access Linked to SQL Azure

Visual Studio LightSwitch

LightSwitch Introduction

Comparison Matrix

Additional Resources

Microsoft Access Help

MS Access Developer Programming

More Microsoft Access Tips

Technical Papers

Microsoft Access Tools

Connect with Us

Email NewsletterEmail Newsletter Signup

FMS Development Team BlogDeveloper Team Blog

Facebook PageFacebook

Twitter with FMSTwitter

FMS Support SiteSupport Forum

 

 

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

Microsoft Windows AzureWe 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.

Microsoft Access and Cloud ComputingHow 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

Microsoft SQL AzureThe 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:

  1. Open an Azure account and create a SQL Azure database
  2. Install SQL Server Management Studio (SSMS) for SQL Server 2008 R2 on your machine
  3. Use the ODBC administration tool to create a file containing the connection to the SQL Azure database
  4. 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

Control Panel 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

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:

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], if your entries are valid, 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 should appear:

ODBC Microsoft SQL Server Setup Test with SQL Azure Database

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 

Microsoft Access 2010 Link to ODBC Database   Microsoft Access 2007 Link to ODBC Database

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:

Link to ODBC database

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:

  1. The Help button gives no relevant information in either Access 2007 or 2010
  2. 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:

Link to SQL Azure Database with ODBC Database type from Microsoft Access

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.

FMS Development Team Blog Blog about it with me here.


Microsoft Azure and Cloud ComputingAdditional Resources for Microsoft Azure and SQL Azure

FMS Technical Papers

Privately Share Your Microsoft Access data and reports over the Intern etMicrosoft Azure, SQL Azure, and Access in Action

Microsoft Resources

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright FMS, Inc., Vienna, Virginia
Celebrating our 28th Year of Software Excellence