The day after Thanksgiving turned out to be a pretty good day. It was the day I discovered that sometime in the last couple of weeks, Microsoft had turned on a really significant functionality in the 2013 version of Office 365. Specifically I'm referring to support for creating and deploying Access 2013 "hybrid" databases on Office 365 as well as in on-premises installations.
This is a positive step for Access developers who look forward to having a broader range of tools with which to meet the needs of our clients. I have to acknowledge we're not all the way to the Emerald City, but at least we now know that, when we do get there, we'll find someone other than a phony wizard on the other side of the curtain.
Let me explain by reviewing past history with Access 2010 web apps running on SharePoint.
The Way We Were
In the 2010 environment, we could publish a set of Access tables to a SharePoint server as an Access web database.
A Quick Side Trip Around the Thicket of Database Terminology
In the previous sentence I referred to "an Access web database." In the last couple of years, a lot of my professional consulting work has involved Access Front Ends with SQL Server Back Ends. Along the way I've gotten used to thinking of the Access Front End as the interface to the SQL Server database. In Access that terminology is less common. Both files are accdbs and you have to crack them open to see which is which even though the names "BE" and "FE" are a strong clue, of course. Call it an FE or an interface, whichever suits you best. Call it a BE or the database, whichever suits you.
Conceptually, it is true that the tables are really what we think of as a "database", and the remaining objects are the interface through which we interact with the database. Thus, when I refer to "a database" in this discussion, I'm primarily thinking of the tables, not the interface, which CAN be created with Access, but isn't necessarily.
This Looks the Point Where we Left the Main Path
So, having published our tables as a SharePoint "database", we could create one or more interfaces for it using standard Access forms, queries, VBA and reports which connected to those SharePoint lists, just we would any other linked tables. Here's where it gets interesting again.
I'm a Little Bit Webbie; I'm a Little Bit Classic
When it came time to build the interface for that database, we had two choices.
First, we could create and publish a set of web forms, queries, macros and reports to work with those tables. Those objects ended up residing on the same SharePoint site where the database itself lived. That web app ran directly in a web browser. See that cord tying you to the desktop? Just go ahead and snip it. No problem. You're in the cloud now, cowboy.
Moreover, we could also create a traditional, client-side Access interface to work with those tables. That interface was deployed onto a Windows workstation for users. Solid, dependable, good old Windows workstations.
That ability to create two kinds of interfaces for a single database (technically a group of SharePoint lists), was a slick way to leverage the strength of traditional Access interface design against the new web capabilities. One database could support both the client side user and the web side user. Pretty slick. We called it a hybrid database. And it worked for both on-premises SharePoint installations and for Office 365 hosted solutions. It did have its limitations, and most of us aren't going down that path any longer, but it is still a great design for some situations, and, IMO, it was probably under-exploited for reasons that have little to do with the technology.
In retrospect, it seems that many of us took that O365 functionality for granted; it was part of the package. I, for one, didn't even think about it all that much. Then came Access 2013 and things got REALLY interesting again.
With the launch of Access 2013, though, we were given a whole new set of tools with which to build Access web apps based on a very different design paradigm. Gone are SharePoint lists functioning as pseudo-relational tables. We're publishing our tables to SQL Server databases now. Fully relational SQL Server databases. I'm not going to go into the details here, because there's really only one point that matters to this discussion and I'm quite seriously afraid I'll go wandering off down some garden path of no relevance.
That point of importance to me here is this: When you publish a web app in Access 2013 to a hosted account on Office 365, the database is a SQL Azure database. If you have an on-premises installation, you might be publishing to your own local SQL Server database, but with Office 365, it's SQL Azure--the database in the cloud.
Web is Web and Client is Client and Never the Twain shall Meet
That advance on the database side, unfortunately, came along with a take-back in the first release. We lost the ability to connect a standard Access client side interface to those tables when they were published to a hosted SQL Azure database via Office 365. If you had the on-premises installation, that limitation wasn't in effect. However, for those of us who run small development shops, and whose clients are all small- and medium-sized organizations, the lack of connectivity from the desktop to the database was a significant hurdle. Maybe a deal-breaker if you look at it from a certain perspective.
Together Again, Naturally
For months I've been whining to anyone at MS who would listen, and I've been getting promises that this problem was going to be resolved. Not that I'm impatient, but, well, it took a while, guys.
The good news, of course, is that this functionality has now been enabled for Office 365 accounts, and YES, I can publish an existing Access database ("Back End" to you Access developers) to my Office 365 account, get the connection information for that database, and connect to it directly from any standard accdb interface (that's the "Front End"). And with that configuration in place, we're now able to deploy a hybrid database again. It's different from the hybrids we built with Access 2010, but I still think of it that way.
You Take the High Road and I'll Take the Low Road
In future blogs I'll dive into more of the details, and any snags I run across as I explore this functionality. Here's a quick overview, though, of what I have found so far.
The first step is to publish the database itself. For my first attempt I selected the time and billing application I use for my own work. I blogged about it last year, when I first migrated the data from a local SQL Server instance up to SharePoint. You might get some insight into that process by reviewing the three blogs in that series.
This time I told Access 2013 to make me a new web app database by importing those SharePoint Lists.
Surprisingly to me, the total amount of time needed to create tables on SQL Azure and move the data into them seemed to be less than when I first published them to SharePoint last year--and there was another full year's worth of data to move. A foggy memory could be at work, as well as other technical factors, though. It's something that bears further research if someone is so inclined. I found one puzzling problem in this step, but it wasn't a deal breaker. I'll tell you about it next blog post.
The next step is to copy the existing Access accdb interface into a new accdb and delete the linked SharePoint Lists from it, leaving only the interface elements in that accdb.
The final step was to use the connection information now exposed in the Access web app to create a new ODBC connection to the SQL Azure database from the plain accdb and link in the tables. I ran into one snag here and I'll tell you about it next time.
Oh, yeah. The final, final step was to rename the tables. If you have worked with ODBC linked tables, you're familiar with the fact that Access prepends the schema to table names, so instead of, for example, tblCustomer, you have dbo_tblCustomer. Well, in the new web app environment, your tables will be prepended as Access_tblCustomer. That reflects the fact that the schema in which Access published tables are created is called "Access", instead of "DBO".
At this point, you have a local, working, client side Access interface that can run down here on your desktop along with a rudimentary web app that can run in the cloud, both connected to your SQL Server database. I'll blog about the design of the web app interface in future installments.
In coming blog installments I'll also go into other details about what I've found, the good, the bad and the puzzling. And we'll have to come to grips with some of the other limitations that still have to be resolved.
However, the fact that this brand of hybrid functionality is now available to anyone with a $6-a-month Office 365 account makes my little toes tingle and my little heart thrill. For $6 a month, you can build and deploy an Access interface for your clients' workstations, and add on a web app that extends the reach of that application to any browser.
The Fine Print
In addition to the technical problems still to be resolved, I've only brushed the surface of the pricing model. We still have to learn how MS plans to make this really a scalable solution cost-wise. Still, at the risk of reading too much into what I know so far, I am highly confident that's just a matter of time, now that the first Ruby Slipper has finally fallen.
