Wednesday, April 26, 2017

Whew! SharePoint Lists are Going to Be Just Fine, Thank You.

Kudos to the Access Team in Redmond.

When I learned that Access Web Apps were going to be terminated, one of the first things I did was try out the feature that allows you to export the tables from an AWA into SharePoint Lists in the Office 365 site where they reside. This is the default procedure suggested by Microsoft. Actually, I can see a few advantages to it, which I'll sum up  below. It's not going to be my first choice for most AWAs, but for a lot of situations, it ought to work just fine.

MS did a really good job of explaining the process, so I don't need to duplicate that here now.

The Good, the Bad, and the Ugly

(I know, I know. I used a cliché in public. I'm sorry.)

I had mixed results that first week.

100% Success

One of the AWAs I support for a client worked exactly as it was supposed to work. The process took only a few minutes and the end result was a set of SharePoint lists that mirror the source tables exactly. If I had wanted, I could have been designing a new interface for that application within a half hour of starting the export.

Mixed Success

A second AWA of my own, one with two very large tables in it, partially worked. The SharePoint lists were created, the data was exported, but the Lookup Fields which identify the relationships between tables failed. The error message was not detailed, but the gist of it was that the number of records I was trying to update was too great. Not the end of the world by any means. Nonetheless, a cautionary experience to anyone depending on getting their data out of the AWA into a safe, new home. Plan ahead, practice the procedure until you are certain you can get your data for reals when the final day of doom descends.

Oh No!

The third AWA I tried to export to SharePoint lists, however, failed repeatedly. I was baffled. I shared my experience with the Access Team, who responded immediately. They published a fix. It partially worked, but still no joy for me.

I kept trying to export that same AWA to SharePoint Lists, doing so off and on for a couple of weeks. Today, I had a beautiful thought. What if I've overloaded my O365 site? I do have some serious work on the site, but there was an awful lot of left-over "experiments" laying around. I cleaned up a lot of it, focusing primarily on deleting old AWAs and lists. It was somewhat surprising, in fact. I counted 42 separate subsites, many with at one moribund AWA in it. After getting rid of most of them, I tried the Export to SharePoint Lists function again.

Bang! It went smooth as silk, and much faster than I would have expected.

So, I touched bases with the Access Team. They let me know they had pushed another fix out just recently. I don't know if it was a coincidence, but whether it was their fix, or my house-cleaning, as of now, I have a 100% success to smile about.

Why Would You Even Do That?

Let me wrap up with my impressions of why you might want to export the tables from your AWA's SQL Azure database into SharePoint lists.
  1. You already have an Office 365 site. You don't need to find a new hosting solution. It's not a great transition, but it's a relatively smooth one.
  2. SharePoint Lists in Office 365 are available either directly in a browser or as linked tables in Access accdbs. They are versatile.
  3. Unlike any other method of remote hosting for Access linked tables, SharePoint lists support off-line processing. The resynch process between accdbs and SharePoint is very good.