Wait, I know what you’re thinking: Access, isn’t that dead yet? Well, no – and if you’ve been reading the blogs about Access 2013 that it’s undergone something of a transformation, one that’s very interesting from a BI point of view. The key change is mentioned here:
http://blogs.office.com/b/microsoft-access/archive/2012/07/20/introducing-access-2013-.aspx
One of the biggest improvements in Access 2013 is one you may not even notice—except that you’re whole app will be faster, more reliable, and work great with large amounts of data. When Access databases are published to SharePoint—whether on-premise or through Office 365—a full-fledged SQL Server database is automatically created to store the data. Advanced users who are already familiar with SQL Server will be able to directly connect to this database for advanced reporting and analysis with familiar tools such as Excel, Power View, and Crystal Reports. Everyday users can rest assured that their apps are ready for the future if they ever need to enhance them with advanced integrations or migrations.
So while Access 2013 is still a desktop database, the Access Web App is essentially a power-user-friendly tool for creating SQL Server/Azure SQL database applications. As Tim Anderson points out here (in a post that’s also worth a read) there seems to be a bit of an overlap with LightSwitch; but that’s incidental here. The real point I wanted to make is that this is another key piece in the Microsoft self-service BI stack in place. By the time users are working with Office 2013 for real, I can imagine some quite sophisticated self-service BI solutions being built where data is loaded into a SQL Server database designed in Access (maybe using Data Explorer?) before it gets to Excel/PowerPivot, a much more robust approach than loading data direct from the original source into Excel/PowerPivot. I’m sure there’ll still be plenty of opportunity for SQL Server DBAs to look down on the work of Access developers, but it looks like this will give Access a new lease of life.
Unfortunately it looks like Access 2013 Web Apps won’t support OData just yet. Here’s a comment from Todd Haugen, a program manager on the Access team, on the first blog post referenced above:
Sorry to say we did not get to enable support for OData at RTM. This is a key area we are looking at for the next release. In the near-term SQL Azure will be turning on ODBC access which will allow you to hook Excel and PowerPivot together with Access. This feature will be available by RTM.
I had hoped to be able to write up a demo of PowerPivot connecting to a database created with the Access Web App, but this comment (and my inability to get it working, even though I can see the server name and database name I’d need to connect to in Access) means you’ll just have to imagine what might be possible…
Further reading:
http://msdn.microsoft.com/en-us/library/office/jj250134(v=office.15)
http://blogs.office.com/b/microsoft-access/archive/2012/07/30/get-started-with-access-2013.aspx
check out new post on Access Blog http://blogs.office.com/b/microsoft-access/archive/2012/08/08/access-2013-and-sql-server.aspx..I hope you like it.
Thanks, it’s very interesting.
Chris you should note that Access 2013 is deprecating pivot tables that means that for some BI users who use this feature they are going to have to integrate their system probably with excel either in the cloud on the desktop. Pivoting is a central concept of SQL and previously of most databases as such this is quite surprising.
Access 2013 doesn’t even WORK with SQL Server any longer. I shouldn’t need to buy a SHAREPOINT license in order to build client server apps with SQL Server. And Linked Tables/SQLPassthrough.. uh.. there’s a different connection string on every single object, LINKED TABLES are just a stupid friggin architecture. Access 2013 is a step backwards… just like Access 2007 was a step backwards.
Microsoft treats Access developers like they’re non-programmers.. because only crybabies use Jet databases for any reason.
Chris, I’m curious if you have ever revisited Access Web Apps since the time of this post. I’ve set up an Access Web App in SharePoint Online, and use SQL credentials via Power Query, but all SharePoint lists are returned as blank tables. Has anyone seen or fixed this?
I haven’t looked at the Access Web App recently, sorry. It’s on my list of things to do…
I’ve lightly explored, and from what I understand, when you create an access web app, a sharepoint list is not created, but rather, an Azure SQL DB. Therefore, if you are attempting to connect via PowerQuery/PowerPivot/PowerBI, you should use the option “Microsoft Azure SQL Database” from the Get Data list option. If you open your Access Web App in Access 2013 (open in Access using the gear icon in the upper right hand side of the Sharepoint location), and go to File, Info (be sure you have the read only rights active on the connection settings), you can pick up the Server & DB name to input into PQ/PP/PowerBI.
The issue I’m now running into though (again, from what I’ve read), is by default, no IP addresses can access the Azure DB. Your admin has to go into the DB Settings and add your IP address. My company hasn’t subscribed to Azure though, so our Admin currently doesn’t know how to get in and enable my IP.
Read more here on the Azure side:
https://azure.microsoft.com/en-us/documentation/articles/sql-database-configure-firewall-settings/
If anyone has any ideas, please do post — until we can clear this hurdle, this functionality is useless to us.
Hmm, do the instructions here help? http://blogs.technet.com/b/the_microsoft_access_support_team_blog/archive/2014/03/24/how-to-make-external-connections-to-an-access-web-app-new.aspx