Sunday, April 2, 2017

Access Web Databases: 2010 vs. 2013

Unfortunately, as of Access 2019, Access Web Apps have been discontinued.  From the March 2017 announcement from Microsoft:
“We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.”
Access web databases are dead.  But there’s no cause for alarm.  Now we have Access web “apps”.
With the introduction of Access 2013, Microsoft has made substantial changes to its vision of how to put Access database applications on the web. The differences between Access 2010 web databases and Access 2013 web apps are major, and you should consider them carefully before embarking on an Access web project.
Before I go further, I should point out a relatively minor terminology change.  Microsoft refers to 2010 web projects as “web databases”, while it refers to 2013 web projects as “web apps”.  I don’t think this will help much to differentiate the two products, but it’s at least worth noting. I’m going to continue prefacing the terms with the Access version, just to be clear.
So what are the differences, and why are they important? 
The most important difference is where and how the data is stored.  Both 2010 web databases and 2013 web apps require SharePoint (although different versions)  However, 2010 stores the tables and application in SharePoint lists, while 2013 stores them in SQL Server tables.  The minimum SQL Server version for working with Access 2013 web apps is SQL Server 2012. No earlier versions of SQL Server will work in this setup.
Secondly, both require SharePoint Access Services, but 2010 uses Access Services 2010 while 2013 requires Access Services 2013.  These services are very different and mutually incompatible. However, you can have both services running on the same SharePoint site, so you can host both access 2010 web databases and 2013 web apps as long as you have both services running.
This incompatibility means that 2010 web databases and 2013 web apps are entirely incompatible.  It that if you create a 2010 web database there is no upgrade path to 2013.  You cannot convert a 2010 web database to a 2013 web app.  It will have to be re-created from scratch.  You will still be able to maintain it in 2013, but you cannot create a new project.  There is no guarantee that future versions will even be able to do that.
On the other hand, you can easily migrate your data (structure and data) from a 2010 web database to a 2013 web app, but the application (forms, etc) will have to be rebuilt from scratch.
There are, of course, many other differences between Access 2010 and Access 2013, just as there are between any two versions of a product.  But in terms of deciding which is right for you, these are the most important.
If you already have an Access 2010 web database, there’s no need to immediately redevelop it in 2013.  But if you’re planning on using it in the long term, you should begin making plans to move to 2013.  All of Microsoft’s future development will be in the 2013 web app arena and future versions of Access may not even support 2010 web databases.
If you are starting a new project, there is no question you should be using Access 2013 web apps.


Patrick Wood said...

Excellent article. Your explanation of a difficult subject is clear and to the point and your advice is spot on. It helps clear up a lot of the confusion about the new Access 2013 Web Apps and the 2010 Web Databases. I look forward to reading more about this subject.

Andres Dominicci said...

I have been developing in Access since version 2.0. And I must say I feel lost with the Access Web Apps. This is clarifying some misconceptions I had. Thanks. Please keep posting your journey

Crystal said...

Roger, this is great. You have a gift for researching, sifting to essentials, and writing.

I was doing dev with 2013 using Win 8 on my laptop ... remember the one you helped me write the letter for a couple years ago? Well, anyway, it bit the dust and I'll never trust Asus (or their lousy service) again. So now I have Win8 and Office 2013 on another boot drive ... but I rarely switch over. Hoping for bucks I can divert to another laptop this summer -- then it will be easier to develop in the newer environments. But I digress ...

This article makes things clear for anyone wondering about Access web apps and web applications in the mass of confusion. I have saved this link to pass on. Thanks again, Roger.

Warm Regards,

Data Dictionary, Display Control
by Crystal on RogersAccessLibrary

(: have an awesome day :)

Unknown said...

I guess my most pressing question about this would be, since the data lives in SQL Server now instead of SharePoint lists, does that open up the development of 2013 web apps to use some of the more advanced features of SQL?

One of the biggest difficulties I've had in developing 2010 web databases was the pure lack of features. Some much-needed nesting (like using data macros to populate another table from data in an existing table, using a "CreateRecord" inside a "ForEachRecord") was impossible by design.

You could build functional simplistic systems using it, but very quickly you would learn doing anything more with your data is nearly impossible without a dedicated desktop app.

Part of me wishes there was more warning to users before encouraging them to jump into a web app/database development project without realizing they've locked themselves into a corner, the only way out being back to the beginning to start all over again.

Roger Carlson said...

Hi Grant. Thanks for your comment.

Unfortunately, the answer is no, you can not extend your web app through SQL Server stored procedures.

Macros you create in the Macro Designer are, in fact, converted to stored procedures behind the scenes, but the only way to modify them is through the Access macro designer. You also cannot create your own, custom stored procedures and have them available to the web app.

I wish I had better news for you.

I can't really talk much about what I know of Access' future, but, I can say the next version should not repeat the incompatibility issues as between 2010 and 2013.

Unknown said...

Hi. Sorry for the late post.

Your article clearly explains some of the differences between 2010 web databases and 2013 web apps. But your conclusion is wrong:

No one in their right mind should do serious web development in Access.

Microsoft has had a recent history of instability in their peripheral development platforms: dumping 2010 'web databases', abandoning Silverlight, and their de-emphasis/possible abandonment of LightSwitch. Developers investing in these technologies lost out.

But, looking at the scant information available, it appears Access 2016 will continue with the 2013 'web app' platform, and provides a small list of incremental improvements, to be rolled out in conjunction with Sharepoint 2016.

Still, in light of Microsoft's previous actions, it is fair to ask these questions: Is there a reasonable expectation that the 2013 'web app' platform will be around 5 years from now? It it worth the risk to develop something significant in this platform?

Is there any indication that there will even be a version of Access after Office 2016?

I maintain that Access, whatever version, is hopelessly outclassed as a web development platform. It's obvious lack of traction in the web development arena attests to this.

Successful open source development platforms have proven to be far more stable over the long run than Silverlight, Lightswitch and 2010 web databases have been. .NET languages now enjoy strong cross-platform support, and are likely to have long lifespans.

I strongly feel your readers will be far better served if they choose to develop their web apps using Visual Studio MVC or MVVC, or a major open-source platform. The initial learning curve is steeper, but that is outweighed by the long-term benefits, and the much higher probability that these platforms will be around in 5 years.


Roger Carlson said...

I should clarify my conclusion. I meant that IF one wanted to start a new Access web project, 2013 should be your choice as opposed to 2010.

Your concerns about Access as a web development environment certainly have validity and should be considered before beginning a new project.

Thanks for your comments