Thursday, August 23, 2012

Data Siloing in Access 2013 Web Apps on Office 365: the spade that could finally bury Access

Sometimes, you gotta call a spade a spade. Spades, as you should know, are the tools used to bury things. I’m sitting here staring at my monitor trying to come to grips with the possibility that Access 2013 web apps on Office 365 are the spade that could finally bury Access.

I’ll explain the three inter-related problems I see with Access 2013 web apps on Office 365 in a moment. First, I want to make it clear that I am now convinced that, taken together, these three problems make it unlikely well-run organizations will adopt Office 365 and Office 2013 as a solution for their database needs. These three interconnected problems are the result of a single strategic Microsoft decision which appears to be based on a particular view of their customer base, now and in the future.

Also, it’s important to acknowledge that I’m not talking about enterprise-based SharePoint installations, i.e., those organizations which have installed SharePoint server. For them, the story should be quite different. And despite my current pessimism, it is possible that web apps running on enterprise-based SharePoint servers in large organizations may extend Access’ life indefinitely. I’m not betting on that, but it’s possible.

This is painful for me to admit, but I think the primary reason for Microsoft’s strategic failure is that they seriously underestimated the expectations of their primary users—the “Information Workers”, or “Power Users” who have always built Access databases for their organizations. For some time now, a few of us have been using the name “Johnny Poweruser” to refer to them.

Here’s what I think. Microsoft has decided two things about Johnny.

A. Johnny wants and needs apps that run on smart phones and other mobile devices. Simply having the ability to create such apps trumps all other considerations.

B. Johnny, for the most part, doesn’t actually need or want to share the data in those apps with other users.

I am absolutely certain that proposition A is true. Mobile devices are the future, and apps that run on them are part of that future. In fact, I had been very excited by the possibility that Access would someday be a major factor in the mobile device environment.

However, after several weeks of struggling to “get” how Access web apps on Office 365 work, I’m finally facing up to the possibility that the real reason it’s been so hard for me is not that I’m an idiot, or that I have inadequate hardware and software for testing, or that this is just a Preview version with some bugs left in it. Rather it is probably the case that Microsoft simply didn’t expect, and therefore didn’t plan for, data sharing across non-enterprise apps, which are exactly the kind of apps that would go on Office 365. And because of that strategic decision to “silo and seal” the data, it’s going to be a steep uphill slog to make Access web apps on Office 365 the viable alternative we had hoped--prayed--it would be.

So, to the three reasons why I’ve come to this low point. I’ve reached this conclusion on the basis of a couple of months of experience with the Preview version. We’ve been assured that the RTM version will address at least one of these concerns—fully sealed data siloing. I trust the people who have told me that will change, so I have no doubt it will happen. For the time being, though, I’m still in “wait and see” mode.

1. Siloed and sealed data. There is no way to share data across web apps. At present, the only way to get data out, at all, is good old copy/paste to Excel.

2. Inter User Security for an Office 365 web app isn’t possible — there is none.

3. Trying to restore, or share, an app on an Office 365 site has been a baffling, frustrating, and fruitless effort so far.

I suppose it could just be that I’m not that smart. But something tells me my difficulties reflect more the design and implementation of the Access web app tool on Office 365 than they do any shortcomings I have. So, let me elaborate on the reasons I’ve reached the conclusions I have.

In a typical desktop, or client database, there is almost always a presumption that two or more people need to share the data from a central data store. Access to multiple data sources (spreadsheets, SQL Server, Oracle, text files, you name it) has been one of Access’ great advantages since Version 1.0. Getting data into or out of Access wasn’t always easy, but it was always possible. Web apps have lost one half of that transaction.

As of today, with an Access web app on Office 365, using external data is a one-way street. You can IMPORT data from an amazing variety of sources, even SharePoint lists. You just can’t share that data back outside that single app*.

Think about the life cycle of the data in such an app for a moment.

You create a web app on Office 365 and import a substantial amount of data into it from an existing Access database, also from some spreadsheets, and from a SharePoint list. Then, you set about adding new data, which you do only in one of two ways.

You can open the web app in a browser window and add new records one at a time through an input form.

Or, you can import data into NEW tables within the app and create data macros to append the records from those tables into the production tables. And finally, you would then delete those tables because they are useless from that point forward. They can’t be flushed and refilled, as temp tables could be.

However, I cannot see a mechanism for sharing the data in that web app with any other web app. Sharing the data can only be implemented by giving other people credentials to use that web app. And that leads to the second problem: inter user security.

If you give a user the credentials to use that web app, they get the interface and the data. For many simple databases that’s not a problem, of course. However, if the app contains any kind of sensitive data, such as personally identifiable information like birthdates, every user gets it all. Once again, if a specific situation warrants, that’s okay. However, the problem, as I see it, is that you don’t have a choice about how to implement it. If you need inter user security, web apps aren’t a choice. Coupled with the fact that you can’t share even SELECTED data across apps, this is a major hurdle. While many simple, simple apps will be built that way, I can’t see any serious app being designed as an Access web app on Office 365.

And finally, perhaps the biggest problem I’ve encountered so far is that sharing apps doesn’t appear to be possible on Office 365. At least, I’ve not found a way to do it. Theoretically, there will be an “app store” where developers can publish apps for others to use. If it’s in Office 365, I’ve not found it yet. I’ve not found a way to create an app store, much less publish an app to it. Maybe that will also be available in the RTM version, but so far, I’ve seen and heard nothing about that. So, even if we could selectively share data across web apps, it’s a moot point for Office 365 users since the ability to create a second copy of the app seems to be pretty obscure, if it exists at all.

And that leads me to the biggest problem of all, from my perspective as a professional database developer. There’s no such thing as backups, version control, or a development life cycle with web apps. It’s a single app that can’t be copied. The data in it is siloed and sealed.

I know from personal experience that, if something goes wrong, that app is at least partly lost. The interface objects, and the data. Gone. Well, some parts of it may still exist, but for all practical purposes, that’s irrelevant. Of course, if the data is critical, you can copy the tables and paste them itno Excel as the first step in a do-over. But if you can’t copy a form out of the app and paste it into another, it doesn’t matter that the form is still ther. It’s only good as a reference to see what you did before. Better than nothing, I guess.

It’s possible to create app packages from web apps. That part isn’t all that hard. However, as noted above, having an isolated, disconnected “.app” file on your local hard drive is more of an insult than a helpful tool if there is no way restore it.

Until Microsoft shows us how to create and restore app packages on Office 365 accounts, I am afraid no serious Access developer is going to invest a lot of interest, or time, in web apps for Office 365. Over time that will lead to two consequences. First, there will be no serious Access developers ready to step in when Johnny gives up and calls for help. And second, Johnny is likely to start looking around for other alternatives as soon as he realizes how limited he is.

Again, if there is enough strength in the enterprise to carry Access forward on their on-premises SharePoint servers, Access will be around for a while. If we have to rely on Office 365 to ensure the future of our beloved Access? Well, a spade by any other name digs as deep.  

* We’ve been promised that will change with the RTM version. And when that is available, I certainly will circle back and reevaluate that point. Coupled with the other two problems, though, it’s a show-stopper for now. Moreover, the cross app sharing problem will not be addressed.