Linking Microsoft Access to Azure

Question

I have been linking an Access 2007 database to SQL Azure.

I now have pass-through queries working and that works very well.

I want to have linked tables working , but whatever method I try I come up against the problem that the ODBC links fail to get generated when it attempts to get at master..sysdatabases. Both when generating links in code and using the built in features. So the ODBC connection tests fine and can be used in pass-through, but not as a linked JET table.

I am not looking for any work-arounds, but only how to make the linked tables work.

Has anyone managed to make this work?

I also noticed that other people had this issue in a different context, so I am also wondering if this will be possible in the future?

I have also noticed that the pass-through channel also works for the standard SQL Server driver in addition to Native Client 10.0

Answer 1

Hi Andrew,
I was able to create and use link tables using Access 2007, it is somewhat painful but once you get the trick it is pretty straight forward.

I tried to directly connect to SQL Azure from Access to create the Link tables and I encounter the same problem as you “master..sysdatabases” so in order to workaround it this is what I did:

 

I copied my schema (no data) to my local Sql Express database (same database name as the one in SQL Azure).

I created a User DSN “ODBC Data Source” pointing to my local Express database. (make sure to un-check “Perform translation for character data”)

I went to the same Access 2007 wizard to link tables, and I used the newly created DSN.

This time it works since I am connected to the local SQL Express.

I told Access to create link table for all my tables.

I saved and closed the Access Database.

I went back to the User DSN “ODBC Data Source” and this time I update the server name and credentials to point to my SQL Azure database.

I check that the user name and password is correct and “Test the connection”.

I went back to Access 2007 and I opened my database file and the previously linked tables are there and now they are getting the data from SQL Azure J

 

I created some queries and reports to check that I can reference them on Access and so far I have no problems.

 

Please let me know how it goes and thank you for using SQL Azure,

Alejandro Hernandez.

Answer 2

Hi Alejandro,

That sounds like a clever work around. I am almost able to get it working, I can create the links to the local instance, then re-point and test the DSN against Azure, but when I try to open the linked table in the very last step I get the message : Depreciated feature 'More than two-part column name' is not supported in this version of SQL Server. I am using Native Client 10.0.

So I presume that somehow it is trying to generate a reference like ServerName.User.Table

Answer 3

We support 2 part column names but I believe not 3 for example: in database TestDb table Foo, columns x & y

create table dbo.foo (x int, y int)

go

select x , y from dbo.Foo -- this is good.

go

select t.x, t.y from dbo.Foo t -- this is good.

go

select Foo.x, Foo.y from dbo.Foo -- this is good.

go

select dbo.Foo.x, dbo.Foo.y from dbo.Foo -- this is NOT good.

I am not getting that error. Can you let me know to what step are you referring in this section?

but when I try to open the linked table in the very last step I get the message

 

Thank you,
Alejandro Hernandez.

Answer 4

Hi Alejandro.

I understand the example that you have given regarding the 2 part column names, and the curious thing is that I am getting that error message even when I do not have any select statements, but when I try and open the linked table.

In order to eliminate any issues with other tables I undertook the following test.

1. Create new SQL Azure database called Test.
2. Create a single table in the database.
CREATE TABLE Test(AnId INT IDENTITY(1,1), ADescription VARCHAR(255))
3. Create local SQL Server database called Test
4. Create the above table in the local SQL Server
5. Create User DSN linked to local SQL Server
6. Create a linked table in Access to the test table
7. Check that when I double click on the linked table it opens and displays the records
8. Close everything, then re-open the User DSN, and change to point at Azure, Test the DSN and everything works
9. Re-open Access and double click on the linked table

At this point I get the error message regarding 2 part column names

If I then add a pass-through query which contains SELECT * FROM Test, then that works.

When I look at the connection string in the linked table def I see the following

ODBC;DSN=AzureTest;APP=2007 Microsoft Office system;DATABASE=test;AutoTranslate=No;

When I look at the DSN in the registry for AzureTest I see the correct settings

DRIVER=sqlncli10.dll
Lastuser=YYYY@XXXX
AutoTranslate=No
DATABASE=Test
Server=XXXXX.database.windows.net

Not sure why this is not working

 

 

Answer 5

Hi Andrew,

I tried to find out what could be the cause and I noticed something, by any chance the local server that you have is SQL Server 2005 or lower? I am asking because when Access 2007 is connecting to SQL Server 2005 it is requesting for this query.
SELECT "dbo"."Test"."AnId","dbo"."Test"."ADescription" FROM "dbo"."Test"

If you notice it is the old t-sql syntax, so I am guessing that it is doing it because the local machine was SQL Server 2005, can you confirm? if that is the case would you mind trying the same exercise but with SQL Server 2008?

Thank you in advance,
Alejandro Hernandez.

Answer 6

Hi Alejandro,

No the tests I undertook were on 2008 although the machine has both 2005 and 2008, I have also repeated this on a 32-bit machine, and on a machine which is both 32-bit and that only has 2008. I get the same result in all cases.

Maybe we could test this against something you have in Azure, or you could test against my Azure account. I can be contacted below.


[e-mail]

Answer 7

I will follow with you offline on Monday, we will get this figure out.

Thank you,
Alejandro Hernandez.

Answer 8

Thank you Alejandro for all your help offline in following this up.

For anyone following this thread, the problem is that Access attempts to use a 3-part column name syntax (which is not supported on SQL Azure) when looking for a primary key on the tables during the process of linking the tables. So unless you remove all the primary keys from the tables the work around will not work.

So until this possibly changes at some future date, you are probably best to stick with using pass-through queries when connecting Access to SQL Azure, and not to try using linked tables.