Please note the date on this article, as features change in Azure this article will be updated.
Access NOW works with SQL Azure (8th June 2010)
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
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
Microsoft SQLNCli team blog
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.
Connecting To SQL Azure
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.
ODBC Driver Choices
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 subject
ADP's and Azure
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.
Top of Page
MUST+SQL automatically removes problems with cascades in relationships removing any cascade
operations but leaving the relationships active. Click below to improve your
Top of Page