Whenever the subject of new web apps introduced with 2013 or web databases introduced with 2010, one common lamentation bought up by people is that they both lack VBA. It is certainly an understandable lamentation; almost all serious Access developers have used VBA and it’s VBA that makes it possible to use Access as the glue of the Office. Any complex Access application will have varying automation such as emailing out an email via Outlook, generating a letter in Word or creating a chart in Excel, you name it. Then there’s of course the import and export processes where we have to take in text files, change it from data to information and maybe format it in another CSV, XML or whatever for someone else’s consumption.

But here’s the trouble — the examples I cited are automation rather than features of a language. Let’s get it clear — I do think that we would be better off having some programmability in web apps whether it be JavaScript for UI manipulation and light querying necessary for data display or T-SQL for the backend. But even if we had both, those will not solve the automation problem. Here’s why – automation is fundamentally a client technology and not a web technology. In order to email from Outlook, Outlook must be first installed, and there must be an object model available for manipulating Outlook objects then of course some kind of custom scripting to leverage those objects. Same goes for other products.  At one point, Microsoft made all those possible inside Internet Explorer. If you’re aware of security flaws of Internet Explorer 5 and 6, well, this is basically why it was flawed in first place. Since then, all major web browsers severely curtain the degree of what scripting language (e.g. JavaScript) can access and it will not expose the computer’s filesystem and/or any other objects that are installed and available for use. This sandboxing of the web browser is a good thing in general but it also means that web browser becomes a very poor candidate for automation. There are some ways that circumvent the sandbox, for example using Flash or SilverLight but they both defeat the whole point of running it in a web browser — both products are a plugins that you must install on the computer to run. If you’re going to install something on the computer, you’d probably be better off to install Microsoft Access runtime instead of SilverLight.

If you look at new tools that Microsoft introduced with Office 2013 and SharePoint, named “Apps for Office”, which are applets that uses HTML5 and JavaScript and can be used to add into certain Office documents such as Excel spreadsheet or Outlook task pane. One advantage of using those tools is that they can also run inside web browser when you view an Excel spreadsheet inside the Excel web app, it still just works. It also comes with a limited object model which can be used in JavaScript and can be made to act on some events, but again, for security reasons, it cannot just access anything anywhere on the computer. I think that reinforce the notion that automation is fundamentally a client technology.

But those are web applications out there that can send emails and download Excel spreadsheets!

No doubt, there are. However, none of that is happening on your web browser. What is most likely happening is that when you click a button in web browser to send an email, it submits a request to the server hosting the web application which runs some code on the server side and uses its email server to send it on your behalf. Same thing with Excel spreadsheet; it is most likely generating CSV or OpenXML files server-side and then putting them in a location that’s available for download then tell your web browser where to go to get that file. So, that takes us to a new problem. Who owns the server? Who can put their custom code on it? If you’ve used hosted services, you most likely know that they lock down many avenues of customizations and not just always in the name of security. It’s downright difficult, if not impossible, to maintain a server that could be running a number of unknown, unreviewed, and untested code. In no time, you’re going to end up looking at hosting your own server and taking on all responsibilities of keeping it running. All this just to send an email?

There is also another problem with running automation server-side. If you’re observant, you may have noticed I said “OpenXML files” instead of “Excel spreadsheets”. Though Office makes automation possible, it was simply never designed to run without user interaction. You don’t have to take my word for it. Microsoft sez so here. So, while you could ignore the warning Microsoft offer and still install Office on your server but then you have to deal with the hassles of getting it to run and be sure there is no UI dialogs that may block code from running and taking down your server. Or, you could just avoid the Office automation model although and write OpenXML documents using standard XML which requires you to have intimate knowledge of Excel XML schema. Here’s an example and a reference. Neither is simple and pain-free solution. This is why several websites out there typically simply make a CSV file available to avoid the hassles associated with such low-level file construction. Going back to the email example, maybe people won’t want the emails to come from “myserver@myhost.com” and want it to come from themselves, which means more coding to get it to send on the behalf and change the Reply-To address. Those are things we simply never had to deal with when we were doing automation right there on the user’s computer because we’re running using a user’s profile which simplify so many things.

Hopefully, it helps to illustrate what kind of can of worms we’d open with a server-side automation. I’m sure that there’ll be a day where server-side automation becomes seamless and easy as we had it with VBA & client-side automation. I do think we have a long way to go, however.

Do not forget hybrid solutions

For right now, I expect that if there are web apps in Office 2013 that will need additional features, they should continue to be supplemented with Access runtime and client database; use web browser to provide easy access for CRUD operations. With Access 2013 using SQL Server as the backend for the apps, it’s very easy to have a client application that can link to the server and still have it work even across large distances and because it’s client, it can do all automation you need and more importantly simply without all the hassles I outlined for server-side. You also still have the option of using SharePoint lists as linked lists for your Access client application which offer you a way to work offline. There is also a 3rd option; use Visual Studio to create an ASP.NET application that can connect to the web app’s SQL Server database and perform all the automation. One advantage of using Visual Studio is that it’s also easy to work with web services and thus has potential to offer you more resources than inside VBA and is worth considering for complex projects. Of course, you’d need to host the applet yourself but once it’s out there, you can expose it as a button placed on the Access web apps or inline the content generated by ASP.NET application in a web browser. It’s not as easy as having the Access clients automate using a local install of Office but it is something that will work in a web browser.

So what’s the point?

The only thing to take away from this is simply this: Going to web changes all the rules of the game. Instead of thinking about how to automate Outlook to send an email, we should be thinking about using a web service that enables sending email. Instead of doing a TransferSpreadsheet to populate the Excel spreadsheet, we should be using External Data Connection in the Excel spreadsheet to get a live snapshot of the data directly from SQL Server and then displaying it online. There are many many web services from many many providers.

But I still need X, Y, Z…
For starters, you can tell Microsoft directly. They want your feedback and you can check their blog post and share your feedback out. Furthermore, I hope you will share few useful web services you’ve found with us that help solved automation over web.