With more and more features being added to the SQL Server I started thinking that its friendship with MS Access 2013 began to fade away. Originally, their long relationship had been solidified through the course of both products maturity, from SQL Server 7.0 to 2008 R2 and MS Access 2000 to 2010 with the introduction of ADP (Access data project). However recent changes in Access 2013 introduced some challenges to those who heavily invested their application infrastructure into this couple of SQL Server and MS Access ADP. Many people got really upset about this and I understand both parties of this situation (you can read all the comments from the last link).
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!
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!
Thanks for providing such useful information, keep updating like this Azure Online Training
ReplyDelete