I’ve been late getting on board with Microsoft’s latest renditions of Access. Not only Access even, but the entire Microsoft Suite. It just isn’t what it used to be. When I first found Access, looked at it and said to myself oh yeaa, I can use that for all SORTS of awesome stuff! Access Web Apps though? Not so much… I mainly disregarded 2010 Web Databases and when I was getting involved in 2013 Web Apps – even through the MVP program at Microsoft and having a lot of insight – I still wasn’t really buying it. It’s just hard for me to look at this new thing and envision people finding real, practical uses for it.

Ok, well maybe that’s not quite right either. I suppose it’s easy enough to think of uses for a cloud-based database application. That’s not really the problem. The problem really is that when you go to make one… to be blunt, it’s a nightmare. It’s nothing at all like traditional Access desktop development. The greatest thing about client databases with Access is that you don’t need to know anything to start! Go try and make an Access 2013 Web App though, and you’re faced with decisions like SharePoint vs. Office 365 (and Azure… what’s this? SQL Server but… different? …?), and troubled by what hosting plans you need and so little documentation about what’s what… it’s entirely discouraging. With a traditional desktop database in Access, a person with middling computer skills could open Access their first time and be working – doing something productive – in a matter of 5 or 10 minutes. It may not be the best, but it was something, at least. I tried Web Apps a few times and within a short amount of time was getting reprimanded by my loving wife for repeatedly cursing at the computer (hi hun!)

The problem with Web Apps is that it requires a person to have a fair amount of background knowledge in concepts or technologies that the average person who would might pick up a desktop database project just isn’t going to have. SharePoint? Isn’t that some big thing that corporate IT people use? Azure? Isn’t that Spanish for blue? Seriously… I’m trying to run a small business here and my IT is ME and my IT team can barely handle plain old regular Access and now I’m being told THIS is what’s next? Sound familiar? Then you try to do a little bit of research on SharePoint and what it might mean to you… good luck with that, unless you have umpteen hours free to learn and decipher and toss away the 90% that you don’t need in order to start making some progress.

But that’s enough complaining from me… I’m not here to complain, which is good because I’d go on all day if that were the case. Traditional Access had a slow sloped learning curve and jumping into Web Apps is like looking up the face of a brick wall. I’m going to try to take this mess and put it in layman’s terms, to knock it down or put a ladder up or something, because really, once you get past that and can see not only the how, but also the why… it’s not all that bad.

SharePoint – What does it mean to me?

If you’re not familiar with SharePoint, which is probably most of us, you probably want to know what it means to you in terms of an Access user. The name is thrown all around these days, particularly in connection with the new Office/Access and as such, you’ll need a basic understanding of it.

What it is

  • A very large and complex tool that can handle more things than you’d ever care to know about.
  • Multiple products/technologies covering a vast array of things (SharePoint Online, SharePoint Foundation, SharePoint Server, SharePoint Designer, etc.)
  • An online service/application/whatever-you-want-to-call-it where companies can go and set up various things like websites, document sharing, applications that run within SharePoint, tools for workflows, scheduling and various other collaboration needs.
  • Possibly built on the idea that a company can have a strong management suite for their business that is not reliant on a physical location: e.g. – a tool that anyone from the organization can tap into from any area or device.
  • A thing with “lists” that somewhat resemble an Access table in that it’s a list of information, which Access can connect to – however, for the most part, these lists or references to them can be largely ignored because we really don’t need them for most things except possible corner cases where you would already know what they are anyway. I mention them here only because you’ve likely come across the term SharePoint Lists already and don’t want you to be too worried about them.

Ok, so we know that SharePoint is a huge complex thing that’s capable of a ridiculous amount of tasks, fit for use by large corporations. Wonderful.

The heart of what we need to understand about SharePoint is that it provides the platform that our Access Web App will sit on, and it provides some security and authentication measures to keep people out of our app unless we want them in there.

We don’t really need to know much more, and have no reason to care about the greater majority of its capability. You will have some small amount of interaction with it when working with your Web App, but for the most part, details of SharePoint can be ignored for our purposes.

What about Office 365 – how does that fit in?

In creating a Web App, we have the choice of hosting it on SharePoint or on Office 365. Office 365 is different than SharePoint. It’s Microsoft’s hosted version of SharePoint and Office rolled into one. It’s a subscription to the Office Suite that we’re familiar with, but the applications are hosted online and streamed to our computers, and with our subscription we can have access to the Office 365 applications from any location or device (again, Microsoft’s high-level goal is being able to access everything from anywhere). So basically, it’s Office through a webpage.

O365 has a number of subscription levels available, each varying in cost and in what features and applications are provided. Some O365 subscriptions allow the creation/hosting of Access Web Apps, some don’t. (note to self, find a decent link of the comparisons and put it in here).

SharePoint is absolutely required to have Access Web Apps, as SharePoint’s Access Services is what ultimately hosts the App and does all the magic. With an O365 subscription of the correct license level, O365 takes care of the SharePoint stuff for us so we don’t have to worry about it.

How do I Choose?

We’re given a choice between being able to host our Access App on SharePoint or on O365, but we’re not given a lot of insight on which we need in a practical sense. In most cases, we’re going to be going with the O365 subscription. The SharePoint option is when your company (or you, if you’re nuts) has an On-Premise SharePoint installation already there, in which case you don’t need to purchase the O365 subscription. However, purchasing SharePoint for an On-Premise installation would probably run you somewhere in the tens of thousands of dollars range. O365 probably is a little more affordable than that.

Another plus to the O365 subscription route as that O365 is under the umbrella of Microsoft’s new update cycle: instead of major version updates every year or two, we get smaller, partial feature and fix updates on a much more regular basis (quarterly, I believe, though don’t quote me on it). SharePoint itself isn’t part of this, so with direct SharePoint hosting there’s still the long wait for feature upgrades. Results of this can be demonstrated in this post from The Access Blog, where a new Web App feature was made available, but only for the O365 hostings, to start: Introducing a new user experience feature in Access web apps: Cascading Controls

Where’s my Jet?

(Jet, if you’re new enough to Access to not be aware, was the database engine responsible for working with Access data for many, many years. In 2007 this technology was updated and renamed ACE. If you’re not familiar with it, don’t worry – it’s just the tool that turns your data from your forms into data that gets stored in the tables and vice-versa).

Access Web Apps do not use Jet/ACE as the database engine. In fact, data isn’t even stored in an Access database anymore (not for Web Apps anyway, traditional applications remain the same). Instead, data is stored in SQL Server – Microsoft’s flagship database engine that has been around for a long time as well.

Is This a Bad Thing?

Absolutely not. Nor is it anything new. For a long, long time, Traditional Access applications that have “outgrown” their Access backends have been ported to the much more robust SQL Server back end. This coupling is nothing new and nothing to be afraid of. SQL Server is a superb service that handles data far better than Jet/ACE ever did (not that Jet was ever bad, but SQL Server is just designed to do more). Amongst a slew of other things it does better, I’ll point out specifically that SQL Server can handle WAN connections with no problem, unlike Jet/ACE where this is a recipe for corruption.

So Now I Have to Learn SQL Server?

Nope. Not even a little bit. When you design your Web App, you use tools that are very similar to those you’re used to for creating tables, etc. When you publish the App, the hosting environment (SharePoint/O365) converts everything you’ve designed so it works behind the scenes with SQL Server. You don’t need to do anything (in fact, you don’t even really need to know that it makes use of a SQL Server backend, but you’ll hear about it at some point if you haven’t already, so I’m throwing it out there).

And Azure? What’s this?

Still not done… We’ve got this other thing called Azure floating around out there also. Offhand I don’t actually recall what the formal name for it is – MS has changed that half a dozen times already and I don’t really feel like looking it up right now, so Azure works (because all the official names are/were Azure-somethings or something-Azures).

Like SharePoint, Azure is a large cloud-based platform/service provider, however what it offers and what it does is a bit different than SharePoint. First and foremost, you can have SQL Azure, which is online hosting of SQL Server farms (a server farm being a whole bunch of servers designed to work together, so with SQL Azure we can probably support thousands of concurrent connections to an Access Web App… that’s cool). Other things that Azure offers which we don’t have much interest in is that it can provide Virtual Machine hosting so you can run various applications in the cloud, and also storage space.

When we hear Azure in connection with Access, we’re hearing about the SQL Azure hosting, which is most often where our actual database will end up. Earlier, we covered SharePoint vs. O365, and for most people O365 is going to be where you’ll be hosting Web Apps. SQL Azure – simply speaking – is the primary back end for O365 hosting, so when you hear or read about Access Web Apps with SQL Azure, you can boil it down to regular old Access Web Apps, because Azure just happens to be the underlying type of SQL Server that it’ll connect to. Again, we don’t really need to know anything at all about SQL Azure, because the whole usage can be (and usually is) completely transparent to us as Access developers. However – yet again – there’s a lot of content out there that mentions the two together and makes things confusing when we’re not sure what we’re hearing.

As a side note, it’s entirely possible to take a traditional Access desktop application and give it an ODBC connection to the SQL Server database that the Web App is running on. Fellow MVP Daniel Pineault explains here: How to ‘Hybridize’ your MS Access Database in Office 365 Azure Database

Another side note, Patrick Wood has what’s probably the best current collection of detail information for SQL Azure with Access, which can be found here: http://gainingaccess.net/SQLAzure/AccessAndSQLAzureInfo.aspx

A Quick Reflection

I’ll stop for a second here and let us reflect on this: Access Web Apps are placed into a controlled environment in which they execute. That reside in or on SharePoint or O365 and they use a backend that we can’t even directly see from our end unless we really dig a lot for it.

In order for Microsoft to make this work, the design of the Access Web App needs to be extremely tightly controlled so that they can be absolutely sure that there are no errors whatsoever in converting your design into what it needs to be so it can integrate behind the scenes and make everything run nice and smooth.

Desktop development for Access is a wide open field where we can do pretty much whatever we want. It is unfortunate, though necessary, that Access Web Apps are at the other end of this spectrum. As you get started with them, you’ll very quickly realize that the rich customization we’ve grown so accustomed to will no longer be there. We’re restricted in many ways, but understanding how Web Apps work is the key to coming to grips with the loss of what we were used to. Later, we’ll cover the capabilities of Access Web Apps. It’s not so bad, actually, but first we’ll figure out why MS did things the way they did. Because insight is a good thing.

But… Why???

I’ve always been a believer in that understanding the reasons behind actions makes for easier acceptance of them, especially when those things are such drastic changes as we’re seeing with Access. I’ve only fairly recently been able to answer this question myself…

Microsoft’s aim is to get away from desktop-saddled information and make it accessible from pretty much anywhere, synced across all devices that a person may use. If you’ve ever travelled for work, you can likely relate to that. It’s a bold move that they’ve spend a number of years on, and for the most part, have done a decent job with it, despite lots and lots of griping from the traditional user base (because who likes to change?)

Putting Access on the web is a particularly difficult task: a custom database application with designable forms and some resemblance of a programming language that will work in any browser on any device is an ambitious undertaking and quite difficult. Web developers will know the ridiculous problems faced in cross-browser management. In fact, this is probably a core reason why we have Access in the web, because while we can get a more robust web application, we can’t get it easily, without learning an entirely new set of technologies and lots of hair pulling because Internet Explorer is backwards.

This ties in with a larger goal though. Most Access databases are used in businesses, and most small businesses don’t really have a decent IT department to handle setting up servers and managing upgrades and so on. The idea is that a business can make use of SharePoint and an O365 subscription to handle their IT/Application needs. Microsoft handles all of the gritty IT details by providing the service and taking care of the servers on their end, and the company is free of the significant burden and just uses Office or SharePoint as if it were running off a computer in a back room somewhere. The other goal of this setup is continued maintenance of the applications. With the “new world” of cloud computing, Microsoft has switched their upgrade cycles to smaller, more frequent updates that don’t come with their own dedicated version numbers like Office 2010. Instead, it remains O365 and is administered automatically. Theoretically it all sounds fine – practically, it might even work also.

So, given a cloud-based platform that’s geared towards self-handling its administration and letting people access it from anywhere, we can see why the Access Web Apps are thrown in with this mix. On the conceptual end, they want Access accessible from everywhere. On the technical end, they have overcome a number of obstacles to provide it to us, and the result is something with not as much customization features as we’re used to, but quite functional nonetheless (the next entry on the topic will cover capabilities… this one is too long already).

Office Store vs. App Catalogs

Two last things to touch base on quickly… MS provides two major areas that we can publish our Web Apps to so other people can use them. One is a public repository where anyone can purchase and download them (if you choose to charge), and the other is geared to a company’s internal use.

The Office Store is the public-facing place where you can publish an app. It can be free or you can charge for it, and the store handles all the e-Commerce details in some way that I’m not clear on because I currently have little interest in it.

The App Catalog is different – this is a repository within SharePoint that contains various apps that a company can use. If you create App XYZ for your company and it gets hosted on SharePoint or O365, Joe Schmoe from the next cubicle looks in the App Catalog to find it. Technically speaking you don’t even need the App Catalog to use a Web App, but the App Catalog does give you some minor distribution and upgrade capabilities, as well as usage monitoring which may be of interest to IT administrators.

Well, that was fun…

… wasn’t it? No, no I’m not a sarcastic person in the least bit, why do you ask?

Anyway, hopefully you’ve been able to get a little bit of insight to how all this stuff works together. I’m thinking that this post will be the first in a series, because there’s lots of other questions that go along with Web Apps, and this only focuses on a small portion. Next up I’ll probably take a look at overall capabilities… yes, we’re restricted, but what can we actually do?

Extra Credit

Because I’m not comfortable enough writing about all this without some sort of review and don’t want to send people down the wrong path, a big thanks goes out to Ben Clothier and Doug Yudovich for having an eye over my shoulders and valuable input on the content posted above.