Please note the date on this article, as features change in Azure this article will be updated.
I will at some point in time get around to fully updating this article, but for the moment here are the key details. If you use the SQL Server 2008 R2 Native Client Drivers then the problems detailed in this article appear to have been resolved.
This appears to work for both Access 2007 and Access 2010, I have not as yet tested with any other versions of Access, or fully checked to see that all the features work in 2007.
The easiest way to get hold of the new Native Client drivers (if you are not already running with SQL Server 2008 R2) is to go to the download for the Microsoft® SQL Server® 2008 R2 Feature Pack further down this page you will find a download link for Microsoft® SQL Server® 2008 R2 Native Client, this can be installed even if you are only running SQL Server 2008 or Express on your machine.
Also see the following links for further details:-Access 2010 and SQL Azure
SQL Azure is Microsoft’s cloud computing platform, and in this article we look into whether you can connect an Access application on your desktop to the cloud.
SQL Azure is promoted with the use of Visual Studio as providing the core interface to SQL Azure but also with support for ODBC connectivity, we decided to see just how far you can go in connecting an existing Access database which is currently linked to your own SQL Server using ODBC, to SQL Server in the cloud.
At the time of writing test accounts are available for Azure, and those with MSDN subscriptions will be able to get an Azure account to undertake development and testing going forwards.
We found that connecting from the SQL Server Management Studio to SQL Azure was not that difficult, our biggest issue was that as we have a dynamically allocated IP address and we needed to change the Firewall rules to accommodate this when the IP address changed.
We had a similar level of success using both SQL Server and SQL Native Client 10.0 drivers, but we have noted that SQL Native Client 10.0 drivers are the recommended technology to use.
We also noted that you are recommended to use VARCHAR(MAX) rather than TEXT fields, for the equivalent to a memo field in Access in SQL Azure. But as it is known that VARCHAR(MAX) does not presently work with SQL Native Client 10.0, this is going to be a point to watch.
We found that creating pass-through queries, with the ODBC connection strings set for SQL Azure worked without any problems. This means that building an Access application on the desktop which uses pass-through queries to return data is possible with the current version of SQL Azure, which is fantastic news for Access Developers.
When we attempted to link to a file DSN pointing at SQL Azure, we received the error message that “references to the database and/or server name ‘master..sysdatabases’ is not supported in this version of SQL Server”. The problem here appears to be that either Access or the underlying drivers need a reference to the SQL Server master database.
However if you try and link to master, then this will work because master..sysdatabases does exist in master.
So this appears to be a problem, you can link to master, but master can not contain your application, and you can’t link to your application database because the linking process needs to see the sysdatabases table inside master (this is because master could be somewhere else in the cloud, and you can not make references between different databases in the cloud).
This is a bit ironic, because inside your application you can refer to the table called sysdatabases (just try SELECT * FROM sysdatabases), but if you then try (SELECT * FROM master..sysdatabases) that will not work.
At this point I would like to thank Alejandro Hernandez at Microsoft who has provided a much clearer understanding of this problem.
Alejandro, was able to come up with a work around using a User DSN ODBC DataSource, this involved linking to a local copy of the database, and then changing the User DSN to point at SQL Azure. However, it also truns out that you then get the error message 'More than two-part column name' is not supported in this version of SQL Server. This has turned out to be because when Access attempts to link the table it uses a three-part column name syntax to get the primary key; which is not supported on SQL Azure. The only work around being to remove all the primary keys from the database, in which case the links would become read-only.
The following link has our discussion on the subjecthttp://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/05dd7620-f209-43d2-8c41-63b251c62970
We have also conducted tests with an ADP connected to Azure and again encountered problems with this.
SQL Azure is an exciting technology, and the prospect of having a desktop database linked to the cloud is very exciting. In its first invocation SQL Azure has focused outside of traditional ODBC technologies and I am sure MS will work towards making this a more realistic proposition. But there are still some fundamental functional problems with SQL Native Client and VARCHAR(MAX) fields, and until these are also resolved a read-only pass-through interface is the currently achievable goal.
by Andrew Couch Office Access MVP, Director ASC Associates email@example.com