Microsoft Access and SQL Azure Databases
Helping people and organizations make better data based decisions to achieve their missions
Published:
Browse All Articles > Microsoft Access and SQL Azure Databases
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:
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:
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.
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.
Helping people and organizations make better data based decisions to achieve their missions
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (1)
Commented:
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 :)