My first MS Access ADP project was developed in 2001 with SQL Server 7.0 and Access 2000 and at that time I realized all the potential that this framework had despite to all the skeptical criticism. During my last engagement with a different client (approximately 10 years after) I've built a solution using SQL Server 2008 R2 and MS Access 2010 and it was a very stable couple I would say 🙂
Nevertheless, I've decided to test a proof of concept, what if a Windows Azure SQL Database can still find a friendship with MS Access 2013, and if yes to what level.
1) I need to create a Windows Azure SQL Database, let's just name is Windows_Azure_Access:
this is how I see this database in my Windows Azure portal:
2) Then let's add a new dbo.TableTest table and populate it with some values:
I especially like the new look of a query execution plan: less colors and more details I think:
3) So now we have the data in the Windows Azure SQL Database; let's try connect to the table we've created from a desktop MS Access application. Since the MS Access ADP technology is no longer available in 2013 version, we will go with the ODBC way of connecting to data.
We create a blank MS Access 2013 database. Then through the ODBC connection console I need to add a new SQL Server connection: provide Server name for my SQL Azure database with account and password to access it (make sue to use a 32-bit version of the odbcad32.exe file, for some of the reasons the 64-bit version didn't allow me to create new DNS).
4) Then I create a new ODBC connection within the MS Access database for my dbo.TableTest table from the Azure; and after I accomplish this then I can see both the table and its data:
The same was as I can see this data in SSMS:
and in the native Azure environment:
For sure the T-SQL in Windows Azure SQL Databases has some limitations, but still provides a way to build database solutions; and as result of this simple test I can say that Windows Azure SQL Database and MS Access 2013 can be friends, even not that close as they used to be before, but still Azure data can be retrieved.
Enjoy your day and happy data adventures!