Migrating Access Services from SharePoint Online to On-premises

Microsoft are ending support for Access Services in SharePoint Online from April 2018. This means anyone using Access Services has to make a choice and very soon about what they do as support ends. There are several possible choices:

Option 1: Move back to SharePoint 2016 On-Premises. Note that older SharePoint versions are not supported. This provides similar functionality but means you’re moving from the cloud back to either on-premises or a hosted SharePoint environment.

  • Using Microsoft Access (Desktop), connect to the Access Web App
  • Choose Save As, choose Snapshot to export the App and related data to a local file
  • In SharePoint, go to the App management site and import the App
  • Add the exported App into a SharePoint site in your SP 2016 farm

In addition to deploying back to either an on-premises or hosted SharePoint 2016 farm, there are other options to consider.

Option 2: Converting to SharePoint Lists. This option is really only suitable to relatively simple solutions and you lose much of the functionality Access Web Apps have that lead you to use them in the first place.

Option 3: Convert to PowerApps. This is a redevelopment and is worth considering bearing in mind that there are functionality gaps between the old and new solution that may need to be worked through. Read more here.

Option 4: Convert back to a desktop Access database. The benefits of using a web based solution are lost, but it may be the option of last resort for some.

Further information that is useful for anyone using Access Web Apps can be found in the roadmap.



SharePoint Migration Tool

Microsoft has made Migrating to SharePoint Online a little bit easier by releasing the SharePoint Migration Tool (SPMT). At the time of blogging the current release was version, the leading zero is a good clue that it’s definitely still in development and may be missing some of the things you really need. Having said that, it does solve some common migration issues and it does it for free!

Here’s a short list of things the SPMT will do for you:

·       Allows you to copy a folder on your file server to a library in SharePoint

·       If the source folder contains sub-folders it copies them too

·       Retains created and modified dates

·       Retains names of the creator and last modified

·       Does incremental copies

·       Allows setup of multiple source and destinations in a single job

·       Source can be a file server (or local disk) or SharePoint on-premises


There are a few short comings to be aware of in the release above:

·       You cannot copy photos on your source server to an image library in SharePoint Online

·       You cannot name a migration job which makes finding the job to rerun later can be hard

·       You cannot schedule a migration job

·       If you close out, you need to run the tool and log in to Office 365 again

I’m sure many of these things will be sorted out soon. Even with these limitations the SPMT is still a very useful tool and will help with some of the basic problems with dragging and dropping files to SharePoint.

Download the SharePoint Migration Tool here:



Custom Vision with PowerShell

Custom Vision is service for creating computer vision models that can be interacted with via a REST API. Custom Vision is powered by Cognitive Services.

Here is a simple demo using PowerShell to determine whether a flag is from New Zealand or Australia. These flags have a lot of similarities, including the Union Jack, blue back ground and Stars, so I thought it would be a good test of the technology.

The first task is to create a project on https://customvision.ai, upload images of flags and train the model. In this case I have added two tags, New Zealand and Australia. The minimum number of photos per tag is 5. Choose a variety of angles and shapes.


Once the images have been uploaded, choose Train from the menu to build the model.

Next, you will need to get the API URL and Prediction Key from the Performance tab.


Now for the PowerShell bit. In this example, we call the Prediction API with the URL to a flag image.

Replace the customVisionAPIURI and Key with the values from your model. Replace the ImageURL with the URL of a flag.

$CustomVisionAPIURI = “https://southcentralus.api.cognitive.microsoft.com/customvision/v1.0/Prediction/00e38c62-acfb-4b30-9d04-481b3ea5436b/url?iterationId=ebe48241-a269-4a30-9223-c80d335462ce”
$Key = “51d70eb8f69044dd89408ae4e60b01da”

$imageURL = “http://images.all-free-download.com/images/graphicthumb/australian_flag_312448.jpg”

$bod = @{url = $imageUrl };
$jsbod = ConvertTo-Json $bod
$Result = Invoke-RestMethod -Method Post -Uri $CustomVisionAPIURI -Header @{ “Prediction-Key” = $Key } -Body $jsbod -ContentType “application/json” -ErrorAction Stop

The results from the script:

TagId Tag Probability
—– — ———–
61740b10-a791-43a9-b6ae-edd2559431a8 Australia 1.0
3ecac738-a7d8-451d-98b9-7d5b36b20add New Zealand 1.67903181E-07

That’s it!

A practical application of this technology could be classifying photos in SharePoint. The script could be extended to run across a library of photos and populate data into a metadata column to classify the image. For example a library containing photos of different types of cattle, could be processed to determine if they are Angus, Jersey, Friesian, Hereford etc.

There are many more advanced applications of Cognitive Services such as medical image analysis, quality processes, identifying faces etc.

More technical detail on CustomVision API can be found here: https://azure.microsoft.com/en-us/services/cognitive-services/computer-vision/



Office 365 Productivity

I’ve had many conversations over the years about improving productivity with technology. One that sticks in my mind was a sales person at a company I once worked for telling a client “you’ll get better productivity if you upgrade from Office 2007 to Office 2010”. I thought to myself, do you? Really? I mean really?

25 years ago, I watched a sales manager using a calculator to add up a column of numbers before typing the result into the total field. I showed him the SUM formula in Lotus 123. Later he told me this saved him several hours per month. If only he had been trained earlier!

People still do this today. Businesses are littered with people creating ‘systems’  with Microsoft Office (and other products) without fully understanding the capabilities of the tools they use. This is where the opportunity to get a productivity improvement really exists and with Office 365, the opportunity is greater still for those who are currently using file server based document storage.

So how do you improve productivity? Change the way you work!

Poor Productivity

Poor productivity can have many causes. Some of the common issues I see are:

  • Not using document templates
  • Using document templates, that aren’t well designed
  • Manual document styling
  • Collating document changes (because sharing was done via email attachment)
  • Shared Spreadsheets using a file share (no co-authoring)
  • Using the wrong tool e.g. Word tables rather than Excel spreadsheet
  • Not being able to find a document
  • Having a complicated remote access solution that is only available to a few people
  • Saving to the desktop and then filing later (if you remember)

I could go on…

Better Productivity

First things first, it shouldn’t be the IT Departments role to tell people how to work. IT should understand the requirements of the users and the organisation, then provide the tools to meet those needs.

Identify document centric processes and write them down where everyone can see them. Ask questions to identify areas for improvement

  • Does the process use paper forms or document templates that could be improved?
  • Is information being manually entered multiple times?
  • Would the process workflow benefit from automation?
  • Would co-authoring reduce the number of uncontrolled copies?
  • Would co-authoring save time on document collation?
  • Is there a drafting process with approval for published documents?
  • Does the final version of the document need to reside elsewhere?

Make sure you focus on processes where the effort is worthwhile. A few minutes saved on a process that is carried out many times a day can be a big saving over time.

Implementing a solution

Office 365 comes with the applications you know (and love) with the added benefit of online document management and collaboration (SharePoint), workflow (Flow), forms and mobile apps (PowerApps) and a range of tools for communicating, organising and collaborating inside and outside the organisation.

Don’t forget to take a second look inside the box and make time to understand the tools you have. Take advantage of the new capabilities to get a real productivity benefit.



SharePoint News from Ignite 2017

The Future of SharePoint, 2017 Edition

This year’s Microsoft Ignite conference in Orlando Florida, was the place to be for anyone interested in SharePoint and Office 365. An enormous number of announcements were made covering almost every aspect of the Office and Office Servers.

Microsoft continued the 3 year release cycle for SharePoint, announcing SharePoint 2019 will be released mid-2018. That’s right, a new server release of SharePoint for On-premises users. Microsoft Exchange 2019 server was also announced, which is good news for those businesses who prefer to say on-premises.

Office 365 had more news than it is humanly possible to keep up with. Here are some of the big items to look forward to:


  • News sites – mobile notifications, save for later, news digests, publish to Teams
  • Hub sites – global activity rollups, global search scopes and more
  • Communication sites – custom layouts and Yammer integration
  • Web-parts – lots of new web-parts for Modern sites including Forms and PowerApps!
  • LinkedIn integration – better profiles and expert search
  • Dynamic Record identification and management
  • Better photo and image search – indexing written content in images
  • Multi-geo support – one tenant across multiple geographic locations
  • Improved mobile experiences
  • Conditional formatting of lists
  • New Admin console
  • SharePoint 2019 Server for on-premises users

PowerApps and Flow

  • Did I mention PowerApps web-parts for SharePoint?
  • A clear announcement that PowerApps will replace InfoPath
  • Document Approval for SharePoint and OneDrive for Business
  • PowerApps web-parts for SharePoint

OneDrive for Business

  • One place to see all your files – OneDrive, SharePoint and Groups!
  • Multi-geo support – one tenant across multiple geographic locations
  • OneDrive Client for Mac
  • End user file restore (30 day backup)
  • External Sharing without needing a Microsoft Account with one time use codes
  • Files on-demand


  • Add SharePoint pages to Teams
  • Push news from SharePoint to Teams
  • Connect Office Groups to Teams
  • Link existing SharePoint Team sites to Teams!

Security and Compliance

  • Site level conditional access policies
  • Service level encryption where the user (tenant owner) has the keys
  • End-user mass content restore – great if you need to bulk recover documents

There have been a huge number of announcements and chances are I have missed a few. It’s a really exciting time for us SharePoint and Office 365 people!


PowerApps Job Tracker Demo

Microsoft PowerApps for Office 365 allows you to build your own apps without needing to be a developer. It is particularly useful for building solutions that use SharePoint as a backend. Mobile users can interact with and update information from SharePoint Lists via PowerApps.

PowerApps are cross platform working on Windows, iOS and Android devices, simply download the PowerApps App from the Apple AppStore or Google Play.

Here’s a short video I made that shows some of the basic features of PowerApps for SharePoint. In SharePoint I have created two lists, one for Clients and one for Jobs. The Jobs list has a looking on the Clients list, creating a one to many relationship between Clients and Jobs. This PowerApp allows users to search for clients and then select jobs related to the client with view and edit functionality.

The only trick part of the app was linking Galleries together so that clicking on a Client filters the Jobs list based on the SharePoint Lookup Column. SharePoint stores the ID of the list item from the Lookup list, so I needed to use this formula in PowerApps:


In the video, I show the functionality and how to link the various screens and galleries together.

I’m sure you can think of lots of ways to improve this basic PowerApp but hopefully this gives you a good idea of how easy it is to build your own applications.

Learn more about PowerApps

Upgrading to SharePoint 2016 : 101

How do we upgrade to SharePoint 2016? This is a question I’ve been asked a lot lately.

Before I answer the question, I usually start by asking one of my own. Have you considered moving to SharePoint Online? Some people have a very good reason for choosing to stay on-premises but many don’t. Let’s consider both scenarios.

Scenario 1: Staying on-premises

The simplest option is a Content Database migration. This is the same tried and tested method used to upgrade from older versions of SharePoint e.g. SP2010 to SP2013. If you are moving from SP2010, you will need to do an interim upgrade to SP2013 first, just for the content database upgrades.


  1. Install a new SharePoint 2016 Farm. If you need high-availability or want to take advantage of mini-roles to reduce downtime during patching, you’ll need a minimum of 4 SharePoint servers. If that isn’t needed then a single server farm is possible, but do your homework before going down this path.
  2. You may also need to upgrade your SQL Server depending on the version.
  3. Once installed, create a new Web Application
  4. Restore the Content Database(s) from the SP2013 farm to the new SQL Server
  5. Install any third-party solutions. Mega Menus, Workflow tools, Custom web parts etc
  6. In Central Admin, attach the new database to your new SP2016 web application. SharePoint will automatically upgrade the database schema during this process, which can take time, especially if the database is big.
  8. Test everything

You may decide that an in-place upgrade isn’t practical or possible. In this case, you can setup a new farm and then use a migration tool (DocAve, MetaLogix, ShareGate etc) to move the content across. This can be a time consuming process but is worth consideration if you need to restructure content or if you have a lot of customisation that you don’t want to bring across as part of the upgrade.

Scenario 2: Moving to SharePoint Online

Moving to SharePoint Online often requires more planning upfront. There are some things you can do in SharePoint server, that can’t be done online or require a rethink. Here’s a short list of common differences, but there are others that may apply too:

–          Server side solutions cannot be deployed to the cloud

–          Site Collections can’t use explicit paths (URL’s to sites may change)

–          You cannot change the URL from https://mytenantname.sharepoint.com

–          User Profile Sync back to Active Directory is not supported

–          SQL Server Reporting Services integration is not supported

–          Email enabled document libraries are not supported

–          Many third-party mega menus aren’t supported (yet)

–          Integration with other systems may need to be updated

This is by no-means a full list, but it does give you an idea of where pain could start.

You will need to develop a strategy for migrating content across.

–          What content are you migrating?

–          How much content is there?

–          What tool are you going to use?

I highly recommend using a migration tool such as Metalogix, ShareGate or DocAve. Unless you have a trivial amount of content, these tools will save you time. They can map metadata from your old site to the new one or copy entire sites and site collections across. All of these tools handle version history and system metadata such as created date and created by.


  1. Identify what you will be migrating and determine if it includes features that may not be supported. Workarounds or alternative solutions may be needed to address those issues.
  2. Ensure Azure AD Connect is setup and syncing users and groups
  3. I recommend that you move Exchange across before SharePoint if possible. There are some things in the Delve profiles which work better
  4. Setup you SharePoint Online tenant
  5. Create site collections
  6. Use a migration tool to copy over the sites, lists and libraries from on-premises.
  7. Setup navigation
  8. Check site security

You can do this process in stages e.g. pre-copy the bulk of the content and then migrate over the changes before you ‘go live’.

I should stress that in many cases you will have other challenges to address as you migrate sites across. Give yourself time to test and find solutions for those things that don’t migrate nicely.

Moving to SharePoint Online will give you many advantages over the long term and reduce the amount of infrastructure needed for your SharePoint farm. For many of us the chances are you will move to the cloud eventually anyway, so why delay?

If you have any good tips, please share in the comments below.