Limitations of SQL Azure: only one DB per connection
If you don’t know yet – Access can work with SQL Azure database, like it works with SQL database, you can link tables and run pass-through queries.Works great! I did some work recently on porting SQL server app to SQL Azure, and found that you can’t use following syntax:
Select [SupplierID] From [AlexDB]..Suppliers
It fails with "reference to database and/or server name in is not supported in this version of sql server" error.
Fellow MVP pointed me to SQL Azure and relational data : Limitations of SQL Azure article, which explains why it fails: its nature is similar to USE restriction – you can use one connection with only one SQL Azure database.
Select [SupplierID] From [AlexDB]..Suppliers
It fails with "reference to database and/or server name in is not supported in this version of sql server" error.
Fellow MVP pointed me to SQL Azure and relational data : Limitations of SQL Azure article, which explains why it fails: its nature is similar to USE restriction – you can use one connection with only one SQL Azure database.
Labels: Access, Azure, SQL Server
posted by Alex Dybenko | 15:56
8 Comments:
Hi Alex,
You are talking about one of my favorite development platforms. As you pointed out there are a few differences. The main difference IMO is that you cannot use ADODB with SQL Azure. But you can do just fine using ODBC queries and recordsets.
I have some tips and code on how to use SQL Azure from Access on my How To's for Access, SQL Azure, and SQL Server page.
Because there is not much in any one place on SQL Azure for Access developers I set up a Microsoft Access and SQL Azure Information Center for Developers.
Disclosure: I do not get paid for the single advertisement on my Microsoft Access and SQL Azure Information Center for Developers pages. The ad is on a sidebar and is for Microsoft Access 2010, the version recommended for use with SQL Azure.
However there are some ads on some of the other pages on my website.
Patrick Wood
Hi Patrick,
thanks for links!
Do you have projects Access+Azure in production? Can you share your experience?
Hi Alex,
I have been helping a number of people through emails and on the phone and I hope to release within a few weeks an Access and SQL Azure Management application. It will make it easier for Microsoft Access developers to use SQL Azure and manage Access Front Ends and users. It will enable a developer to distribute a single copy of an Access Front end online, for example, that upon first use will connect to SQL Azure using code and register itself with SQL Azure getting a unique user name and be assigned to one or more SQL Azure Database Roles.
The application will enable a developer to create and assign roles to individual users, granting or danying Permissions to any database Table, View, or Stored Procedure using those roles, and activate and deactivate users.
Why do we need this bundle Access+ Azure, what does it give better than Access+SQL ?
Rona
You can consider Azure SQL as SQL server hosted at provider, you don't need a server, backup plans, etc. You can access it from different locations. So it makes life easier
I don't see why would you use Access with SQL Azure database. Doesn't the newest Access update have all the features the SQL Database has?
Access update - you mean new Access Engine? You can't compare it with SQL server, Jet running on client and SQL server on server.
Interesting issue, thank you for the detailed description!
Post a Comment
<< Home