Linking Microsoft Access 2010 Tables to a SQL Azure Database

Russell Sinclair described how to link Microsoft Access 2010 tables to a SQL Azure database in his Access 2010 and SQL Azure post to the Microsoft Access blog of 6/7/2010. Ryan McMinn didn’t include screen captures in the narrative for creating the ODBC connection with the SQL Server Native Client 10.0 driver. I also encountered a problem that required a workaround to get links to a Northwind database working.

This step-by-step tutorial adds screen captures and describes the workaround for primary key column name conflicts. It also describes an issue with adding subdatasheets to linked table datasheets.

Note: This tutorial assumes that you have a SQL Azure account with at least one populated database and have SQL Server Management Studio [Express] 2008 R2 available to edit primary key names of the linked tables.

---

Creating a SQL Azure Data Source Name (DSN)

1. Launch Access 2010, click the External Data tab and click the ODBC Database button to open the ODBC Select Data Source dialog:

2. Click the New button to open the Select a Type of Data Source dialog and select the System Data Source option:

3. Click Next to open the Select a Driver for Which You Want to Set up a Data Source dialog and select the SQL Server Native Client 10.0 driver:

4. Click Next and Finish to open the SQL Server Data Source Wizard’s first dialog, type a name and description for the data source, and type the full server name, including the tcp: prefix (tcp:servername.database.windows.net):

You’ll find it easier to copy the server name from SSMS’s Connect to Server dialog.

5. Click Next to open the second Wizard dialog, select SQL Server Authentication, type your administrative account name with an @servername suffix, and password, and mark the Connect to SQL Server check box:

6. Click Next to open the third Wizard dialog, change the default database from master to the Northwind[DS] database and accept the default ANSI options:

7. Click Next and Finish to open the ODBC Microsoft SQL Server Setup dialog and, optionally, click the Test Data Source button:

You tested the connection when you selected the Northwind[DS] database from the SQL Azure-generated list in step 6.

8. Click OK to save your Machine Data Source and return to the initial Select Data Source dialog with your new data source selected:

9. Click OK to open the SQL Server Login dialog, accept your Login ID and type your administrative password:

Options let you change your password, database, default language, application name, and workstation ID.

10. Click OK to open the Link Tables dialog. Click to select the tables to link to your Access front end:

SQL Azure Labs’ SQL Azure Data Sync application created the SQL Azure tables from the Northwnd.mdf database included in the Northwind and pubs Sample Databases for SQL Server 2000. For more information about using SQL Azure Data Sync, see:

• My very detailed Synchronizing On-Premises and SQL Azure Northwind Sample Databases with SQL Azure Data Sync of 1/28/2010
• Liam Cavanagh’s review of my Data Sync tutorial: Sending Email Notifications Using SQL Azure Data Sync of 1/28/2010
• Wayne Berrry’s Introduction to Data Sync Service for SQL Azure of 7/6/2010
• Wayne Berry’s Partial Synchronization with Data Sync Service of 7/7/2010

Only those tables present in the original Access 97 Northwind database are marked for linking. SQL Azure Data Sync generates the dbo.TableName_tracking tables.

11. Click OK to generate the linked tables. If you marked the Save Password checkbox in the Link Tables dialog, you receive this warning message for each table:

Working Around Invalid Primary Key Names

Using the Northwnd.mdf database, you receive the following message about an invalid Primary Key PK_dbo.TableName for each table you attempt to link:

12. To eliminate these errors, which prevent linking the tables, open the SQL Azure database in SQL Server Management Studio 2008 R2 and change the name of each primary key to PK_TableName as shown here for the Categories table:

Tip: When changing the name of PK_dbo.Order Details, remove the space (PK_OrderDetails) for safety. If you don’t you might encounter issues elsewhere.

Editing a Linked SQL Azure Table in a Datasheet

13. Repeat steps 9, 10 and 11 to create links and open one of the tables for editing: 

14. Add a new item to the table to verify updatability:

15. Close and reopen the linked table to verify the record’s addition:

16. Open and scroll the Orders and Order Details tables to compare performance with tables linked from an on-premises and SQL Azure Northwind database. I find performance with a 350-kBps (inbound) DSL connection to compare reasonably well with the same database running on premises in SQL Server 2008 R2 Express.

Adding a Relationships Diagram and Subdatasheets

17. Optionally, create an Access database diagram in the Relationships window: 

You can’t enforce referential integrity locally and Subdatsheet Name = [Auto] doesn’t generate subdatasheets for table datasheet views automatically: 

You can Specify a top-level Subdatasheet Name, such as Table.dbo_Orders for the dbo_Customers table, which will automatically set Orders.CustomerID and Customers.CustomerID as the linked fields from the relationship specified in the diagram (as shown above).

18. Click the Datasheet button to open the Orders for the specified customer, Alfreds Futterkiste for this example:

The subdatasheet property values don’t persist because SQL Azure doesn’t support SQL Server 2008’s extended properties, which store the subdatasheet’s table and field names. Therefore, you lose the subdatasheet when you close and reopen the table. Lack of support for extended properties appears to make creating Lookup columns impossible.

19. The only obvious method of persisting subdatasheets is to create a SELECT * FROM tablename; query over each table and specify Query.queryname as the Subdatasheet Name property value:

This approach enables cascading subdatasheets, as shown here:

I’ve posted a message about this issue in the Access for Developers forum to determine if there’s a simpler workaround.

Pass-Through Queries

As Russ mentions in his blog post, you can execute pass-through queries against SQL Azure with the above DSN:

20. To create a pass-through query to verify SQL Azure’s lack of support for extended properties by returning an error when you attempt to execute the SQL Server 2008 fn_listextendedproperty system function, click the Create tab and the Queries group’s Query Design button, close the Add Table dialog, and click the Pass-Through button to open the SQL editing window and the query Property Sheet.

21. Select the ODBC Connect Str[ing] property’s builder button to open the Select Data Source dialog, click the Machine Data Source tab and select the SQLAzureNorthwindDS system data source you created in steps 1 through 10.

22. Click OK to open the SQL Server Login dialog, type your password, and click OK to connect.

23. Click Yes in the Connection String Builder dialog (refer to step 9) if you want to save the password in the connection string, where it can be seen by anyone who has authorization to enter query design mode. Otherwise click No (recommended).

24. Copy and paste the following example statement from SQL Server 2008 Books Online’s fn_listextendedproperty (Transact-SQL) topic:

SELECT objtype, objname, name, value
FROM fn_listextendedproperty(default, default, default, default, default, default, default);

to the SQL Window:

The full connection string, with security-sensitive values masked is:

ODBC;DSN=SQLAzureNorthwindDS;Description=Data Source for NorthwindDS on SQL Azure;UID=RogerJ@jcXXXXXXzaf; PWD=XXXXXX;DATABASE=NorthwindDS;

25. When you click Run to execute the query, you receive the following error message:

26. Test the query with a valid T-SQL statement that returns rows. The recordset will be read-only, as are all recordsets returned by pass-through queries and stored procedures.

Why Not Use an OLE DB Connection?

Stack Overflow user Troy asked Why doesn’t Microsoft support OLE DB connections to SQL Azure? in this 6/21/2010 question:

At the MSDN website it says, "Connecting to SQL Azure by using OLE DB is not supported."

There are other places on the web where folks report that it works fine for them after tweaking the server name in the connection string, such as here and here. Even SQL Server's Analysis Services uses OLE DB to connect to SQL Azure!

I develop a native/unmanaged application in Delphi that connects to SQL Server using ADO through the OLE DB provider for SQL Server. I'm considering adding SQL Azure support. It would be really helpful if I could reuse the majority of my code without not too much change. I probably wouldn't consider going this direction otherwise.

It would be helpful if Microsoft were more clear on why "OLE DB is not supported". If there are certain limitations within the use of OLE DB, what are they? Maybe I can work around them, or maybe it wouldn't affect me.

Microsoft also mentions that ODBC is supported. So could I use the "OLE DB provider to ODBC" and connect this way? Or is any combination that includes OLE DB "not supported"?

Ryan Dunn (@dunnry) from the SQL Azure team replied on the same date:

You can use it, however it has not been thoroughly tested for all cases. Essentially, it should work for most things, but there might be a few edge cases where it won't work. Until we document those cases, it remains unsupported. That being said, if you were to use and run into errors, we would love to know about it and prioritize that to be fixed.

[From the OakLeaf Blog’s Linking Microsoft Access 2010 Tables to a SQL Azure Database, 7/28/2010]