Linking Microsoft Access 2010 Tables to a SQL Azure Database
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.
Creating a SQL Azure Data Source Name (DSN)
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
14. Add a new item to the table to verify updatability:
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
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).
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.
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:
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).
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;
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?
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.
Atom 1.0 site feed
