Wednesday, July 28, 2010

Linking Microsoft Access 2010 Tables to a SQL Azure Database

image 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.

Update 7/28/2010: Changed author name from Ryan McMinn to Russell Sinclair, added more details about (and a workaround for) SQL Azure’s lack of support for extended properties (required for Subdatasheets and Lookup fields), and added demo of pass-through queries.

image 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)

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

image

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

image

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:

image

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):

image

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:

image

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:

image

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

image

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:

image

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

image

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:

image

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:

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:

image

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:

image

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:

image 

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

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

image

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

image

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

image

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

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

image 

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

image

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).

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

image

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.

image 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:

image 

This approach enables cascading subdatasheets, as shown here:

image

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:

image 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.

image 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).

image24. 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:

image 

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;

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

image

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?

image 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.

blog comments powered by Disqus