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.

Answer 9

Hi,

Basically, I do a lot of Access frontend developing and use SQL Server back end.

This is all very well but I'd like to stick my data on the MS Cloud since a lot of my users travel abroad and being able to link into it that way would be fantastic.

Obviously, I can't remove all primary keys!

I've never used pass through queries but what kind of problems will I encounter using pass through queries?

eg Is making an Access Front End using only passthrough queries 'wrong'? Is it weird? 

EDIT:

Actually, I've just been experimenting.. what happens if you link to a table using a PSQ and then do a 'normal' query based on that in Access?

(I'm sorry if that's a stupid question!)

Answer 10

Hi Hid,
From the earlier conversation with Andrew, I let the SQL development team know about this shortcoming, we are working forward to improve this scenario in an upcoming release.

Answer 11

Hi,

Thanks for reply so soon!

So:

1) What are the chances that the Azure team will bother to add this functionality to appease Access users? By that, I mean that SQL Azure is such a big mega thing whereas a lot of people see Access as a bit of a home developer tool. From a personal point of view, being able to develop in Access using linked tables to a SQL Azure backend would be absolutely fantastic.

2) Assuming I stick with the Pass Through Query route, is it wrong to create a pass through query that basically returns ALL records and then write a query in Access based on that pass through query? I know that Access can be quite clever when you have a SQL backend but I'm not sure how it works with a pass through query and SQL Azure.

UPDATE: Just remembered of course that PTQs are non-updateable. So table linking is pretty much a necessity.

Realistically, I just need to try it.

I've already outsourced my mail to the Google Cloud but I'm very keen to mix it up a bit by having my DB hosted on the MS cloud!

I'm quite surprised that so few people seem to want to use an Access frontend with a cloudbased backend.

Thanks!

Hid.

Answer 12

Hi Hid,

A PTQ can be written to update, insert or delete data, where you would explicity state a list of field names and field values.

For example

INSERT INTO Customers(CustomerName) VALUES('Test')

Or we could create a stored procedure and execute this through a PTQ

usp_AddCustomer 'Test'

It would also be possible to have a local image table, and read and then write-back from that using an PTQ.

Or possibly link to a local SQL Instance and synchronsie the data into the cloud.

So there are some other options, but using PTQ's to do updates etc., would take a bit of work.

Regards

Andy

Answer 13

Hello.  I too want to connect Access to a SQL Azure database.  My question is, can my Access app be local - not on the Windows Azure file system, but on my departmental machine? 

And, add my voice to what will eventually be many many many... if you can't have primary keys in updateable linked tables, then you don't have it.

Thanks,
schubash

Answer 14

I realize this is a huge hack and probably only suitable for some scenarios, but I thought I would post it anyway.  One of the things that you can do fairly easily is to link an Access database to SQL Server.  You could then use SQL Azure Data Sync to take this SQL Server database and synchronize it to SQL Azure.  Once setup, you could then make copies of the SQL Server database (see this post for details) and share them with other users (who could also link to their local SQL Server from Access and sync changes with SQL Azure to be shared with other users).

Obviously this is not going to give you online access to SQL Azure via Access, but if the purpose is to have a public location for your data and then let users share data using Microsoft Access, it might be a start.

Just a thought...

Liam

---

Sr. Program Manager, SQL Azure and Sync Framework - http://msdn.microsoft.com/sync/

Answer 15

Thanks for all the replies!!

I think I'll just wait till it's linkable and easy!!

And then it'd be nice if the entire Access front end could be on the cloud as well.

I know MS Office 2010 will be on the cloud so I wonder what will happen to Access....

Answer 16

The main problem is that the SQL Server Client 10.0 driver when used for ODBC connectivity comes up with an error that points to the version of SQL Server 10.5 not supporting this feature because of some deprecated feature setting. If a driver is available specific to SQL Azure then this will be laid to rest.

Also SQL Azure refers to a version 10.25. I don't see any documentation of this. The linking of tables or importing methods also do not work for the same reason.

The SQL Azure does not support Linked Servers, when it is supported SQL Azure's applicability widens. In this context the OLE DB driver is another feature on the wish list.

http://hodentek.blogspot.com/2009/12/microsoft-access-and-sql-azure.html

---

mysorian

Answer 17

I think this suggestion would work but SQL Azure will not be a backend. Access projects is a nice feature which takes away the worrysome management of security and if SQL Azure can support linked servers then it will be nice addition. This means that someone has come up with a OLE DB driver. At the outset, I wonder why the SQL Azure group avoided connectivity to OLE DB with Microsoft being the pioneer in many OLE DB programs. I always thought it was a natural fit for TDS.

---

mysorian

Answer 18

I also have many commercial apps with Access as a front end and SQL Server 2005 backend.   If some or all of this could be mounted on Azure, this would be a major breakthru.

Now that Azure is released, what is the status of this opportunity?

Thanks

Grant Sutherland

Answer 19

I have made a strong case here,

http://hodentek.blogspot.com/2009/12/microsoft-access-and-sql-azure.html

---

mysorian

Answer 20

Hello Alejandro,
I have been following this discussion with deep interest. Can you give us a rough date of when the SQL development team might be able to provide a workable solution or work around on this? This has enormous implications and potential in many areas of my business and I eagerly await your response...
Respectfully,
Marty

Answer 21

AS someone who has a number of Access db's that the clients would love to have access to as they travel, the option to put this on Azure, is great, but Linked tables would be great. ALso eagerly awaiting the answer to this

---

ZR

Answer 22

From the Netherlands: I see indeed a great potential for my business as an Access-developer. I'm also eagerly watching any progress on this linked tables issue !  Maybe a solution in this matter would be e small step for you guys, but to us as developers a giant leap forwards in our business !  So please respond to our request .

Answer 23

Access data is on a file. With Windows Azure Drive becoming available it should be possible to host the database files on Windows Azure in the drive and work from there. Will this idea work? This way the Linked Server issue can be put on the back burner.

---

mysorian

Answer 24

Jayaram, a construction as you describe could possible work in a front- en backend access database solution. But especially the access-frontend with an SQL-server backend database is a solution which is appealing to us developers and a lot of our customers. And storing data in the cloud is therefor a great opportunity. So in my opinion the linked server issue is still very much "alive". 

Answer 25

Hi Andrew and all others that have been following this thread.

I had a chat with the SQL Azure dev team and we were able to enable the 3 part name column reference to unblock this scenario, I have just verified that I can have linked tables (with identity columns) using Access 2007 and SQL Azure. I can add, update and delete rows using the Access UI and I can build reports from the linked tables.

We still need to use the workaround of using an in-premises SQL Server in order to do the "initial linking" and then later on update the User DSN to point to SQL Azure. (details on the beginning of the thread ).

I appreciate all of you for be interested on SQL Azure, as usual, suggestions and comments are welcome. 

Thank you again,

Alejandro Hernandez

Answer 26

Alejandro
Just wanting to express my deepest thanks for your help on this. I have been able to connect and link tables within Ms Access 2007 using the example above and have a working model. This is potentially a fantastic leap ahead.

I am curious if anyone has been able to create a DSN-less connection to Sql Azure... I have multiple databases that use to such a DSN-less connection to traditional sql server architecture but in experimenting today I have been unsuccessful using the DSN-less connection with Azure. It would seem that it should be possible as it is just mimicking the same connection information contained within the DSN...

Overall, very excited with this advance! Thanks again Alejandro...

Answer 27

I am curious if anyone has been able to create a DSN-less connection to Sql Azure... I have multiple databases that use to such a DSN-less connection to traditional sql server architecture but in experimenting today I have been unsuccessful using the DSN-less connection with Azure. It would seem that it should be possible as it is just mimicking the same connection information contained within the DSN... 

I'm not sure what is meant by DSN-less connections. Is that with respect to MS Access using a DSN-less connection? Or other programs? Or from code/scripts? I'm not too familiar with MS Access connectivity to SQL Server, but you can use DSN-less connections to SQL Azure in code/scripts whether it be via OLE DB or DSN-less ODBC.

The DSN-less ODBC connection method is given in SQL Azure site, etc. The OLE DB method can be found here:

http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/3060e51c-4b54-41aa-ac28-df684683822a

Answer 28

Hi Daluu,
The DSN we are refering It is related to how MS Access connects to SQL Azure and updates it's metadata. So far the DSN workaround works for Access to SQL Azure.
As you mentioned, there are other ways to connect different applications to SQL Azure, but in this case we are particularly talking MS Access during the "first linked tables"

Thank you,
Alejandro Hernandez.

Answer 29

Hi Alejandro,

As SQL Azure has only recently been made available in Australia, I've just tried your procedure for linking an Access 2007 database to a database on SQL Azure, but it's just not working properly. Interestingly, when I create the DSN, the message I get is as follows:

Attempting connection
Connection established
Verifying option settings
䑇䙅[Microsoft][SQL Server Native Client 10.0][SQL Server]Reference to database and/or server name in 'master.dbo.syscharsets' is not supported in this version of SQL Server.

If I don't save the password with the links, it'll connect, but of course I then have to enter the password every time I want to read a different table. If I try to save the password when I link the tables, I get an "ODBC - connection to 'SQLAzure' failed" error. I tried relinking (through the UI and in code), but that fails every time.

What now?

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 30

Graham, I can't speak for anyone else and don't pretend to however if you follow the steps listed above EXACTLY, it DOES work. I have a local copy of the sql server 2008 database that mirrors the one up on Azure. The mixed mode password/logon is EXACTLY the same as up on Azure. You then link the tables in Access, shut down Access, change the server name inside the DSN and it works. For me, it's taken a few times and it's a bit buggy getting the links to update when you change the server but once it's done, Azure works really, really well. I'm currently running test and am close to releasing a commercial app based on Azure with an Access front end. I use the setup program to create the DSN reg keys on the target computer. I've yet to get a DSN-less connection to work but the DSN itself will suffice for now...

If you set it up right, you do NOT have to enter the password to read each table. Your table links are not right. When this happens to me, I have dropped the tables, and relinked. Again, it make take a few time but once done correctly, you only have enter the password once upon logging into Azure.

Inside Access you need to have the table links display "ODBC;DSN=(Dsn Name);App=2007 Microsoft Office System;DATABASE =(your database);Auto Translate = No;QuoteId=No;;TableID=(Your Table Name)

If you don't see the AutoTranslate or QuoteID you've got the wrong settings. It won't work.

Not claiming to be an expert, just relaying what i have found...

Marty

 

Answer 31

Thanks Marty.

As I said in my first post, I CAN get it to connect if I DON'T save the password while linking, but doing so forces me to enter the password the frst time I open up an Azure table in each session. Notwithstanding, I still get the same (following) message when testing the DSN.

• Reference to database and/or server name in 'master.dbo.syscharsets' is not supported in this version of SQL Server.

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 32

Hi Graham, It is interesting that you have to enter the password every time you open a table, if the password is saved on the DSN file, that should be enough.

I believe the reason why you are getting the "master.dbo.syscharsets" error is because there is a setting that is check (by default) and you need to un-check it. Such setting is “Perform translation for character data” and can be un-check while creating the DSN connection.

Thank you for your questions and I hope this can be of help.

Alejandro Hernandez.

Answer 33

Hi Alejandro,

Thanks for your response, however, that's not it. 'Perform translation for character data' is unchecked. I read your procedure and followed it precisely.

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 34

OK, I found the answer.

I spoke with Dave Robinson (Senior PM for SQL Azure) and Russell Sinclair (Senior PM on the Access team), who both said Access 2010 is the only version of Access that is supported against SQL Azure. They also indicated that the R2 version of the ODBC driver should be used. They said ODBC v10 and previous versions of Access might work, but they're not suported.

I haven't tried it yet, but I'm pretty confident. I will post back here after I do try it.

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 35

Here is the final answer.

If I use Access 2010 RC and the R2 version of the ODBC driver (V10.5x), I can link from Access to SQL Azure directly using a DSN that already points to SQL Azure. Opening a table in Access does not require a password. What this means is that Alejandro's procedure is not necessary in this case.

If, however, I use Alejandro's procedure using exactly the same configuration as above, I need to enter a password once per session to see the data.

Therefore, if you want to reliably make this work, use Access 2010 (RC or later), ODBC v10.5x, and link the tables using a DSN that points to SQL Azure.

There are a few more things to keep in mind, but I'll be publishing a white paper on the subject soon.

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 36

So much for the Final Solution. Standby everyone (at least anyone who's watching this thread). I've just discovered an anomaly. I'm in discussion with Russell and Dave to try and figure it out.

 

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 37

Well it was just an anomaly. It works just fine now.

---

Regards, Graham R Seach Microsoft Access MVP Sydney, Australia

Answer 38

>There are a few more things to keep in mind, but I'll be publishing a white paper on the subject soon

 

Graham.  Just wondering if you 've created that white paper yet and where it might be found?  If not, whien is it expected?

---

WoodyS

Answer 39

Connecting to SQL Azure using Microsoft Office Professional Plus 2010(beta) version  
Version 14.0.4536.1000(32-bit)  with an ODBC connection is straight forward now.

---

mysorian

Answer 40

I recently wrote these papers that you may find helpful:

• Microsoft Access and Cloud Computing with SQL Azure Databases (Linking to SQL Server Tables in the Cloud)
• Deploying Microsoft Databases Linked to a SQL Azure Database to Users without SQL Server Installed on their Machine
• Microsoft Azure and Cloud Computing...What it Means to Me and Information Workers

We've been working with Microsoft Azure, SQL Azure and linking it with Access and Excel. Exciting stuff.

Answer 41

Migrating and linking Access 2010 tables to SQL Azure worked well for me.

See my Migrating a Moderate-Size Access 2010 Database to SQL Azure with the SQL Server Migration Assistant post of 9/7/2010.

---

Microsoft Access 2010 In Depth (QUE Publishing)
OakLeaf Blog
Access 2010 Blog
Amazon Author Blog

Answer 42

I think allot of people are following this thread that use ACCESS and want the same solution.

 

Steve

---

N/A