Sunday, April 2, 2017

Access 2010 Web Databases: Uploading to SharePoint

Unfortunately, as of Access 2019, Access Web Apps have been discontinued.  From the March 2017 announcement from Microsoft:

“We no longer recommend Access Services for new apps. This feature will be retired from Office 365. We will stop creation of new Access-based apps in SharePoint Online starting June 2017 and shut down any remaining apps by April 2018.”

In this blog series, I'm discussing how to convert an existing Access application to a web application (the series starts here: How Can I Put My Access Database On The Web?). As with any database project, I'm starting with the data.

When I attempted to upload my existing tables into SharePoint, I got a number of errors (
Uploading the Data to SharePoint). I dealt with some of them (primary keys and unique indexes) in my last post (How can I create a Compound Unique Index?). Last time I discussed errors in creating relationships in a web application (Creating Relationships).

I'm finally ready to upload the tables to SharePoint. Before I do that, however, I need to check the compatibility again (see Compatibility Checker). Assuming there are no more errors to correct, I can Publish To Access Services.

Access Services

Obviously, I need a SharePoint 2010 site with Access Services installed. While it's always possible to set up my own server, there are various hosting companies that provide Access Services, and there will be more in the coming years.

Important Note: Hosting services for this blog series are provided free of charge by They are one of the first SharePoint providers specializing specifically in Access Services. If you'd like to try anything from this blog series, AccessHosting provides a 30-day free trial. They also have a Developer Sandbox option for developers to use Access Services and prototype solutions for customers beyond the 30 day trial.

So once I have my hosting account, I can fill in the appropriate information: the Server URL and the Site Name. The Server URL is my account on the SharePoint host and the Site Name is the name I want the site to have. In my case I chose the name of my database.

Once the information is filled in, the Publish to Access Service button will become active. Clicking it will bring up a login window.

After entering my password and clicking OK, I'll see a dialog box that shows the various object being uploaded to SharePoint

When it's done, I'll see the following:

Publishing to SharePoint produces a number of irreversible changes to the database, which I'll discuss presently. Fortunately, Access makes a copy of the original, pre-upload database with a "_Backup" suffix, so if you have problems, you can easily go back to your original version.

Database Changes

The first and most obvious change is that my tables now look different

The icon in front indicates they are now SharePoint Lists. As such, you can no longer go into Table Design view to modify your table. You must make any changes in the Table Tools Ribbon.

Between them, you have most of the table design ability of the Table Design View, limited, of course, to those properties that SharePoint list support.  Creating new objects like Queries, Forms, and Reports are more restricted.

Some queries like Aggregate Queries and Union Queries (to name just two) are not supported and forms can only be created in Layout View. Certain query, form, report, and code types are only supported in the "Client", that is, it will only run when used in Access as opposed to a Web Browser. This is useful in a Hybrid Web Application, but not in a Browser-Only application (which is what I'm hoping to develop here).

Many of the Database Tools are also absent in a Web Application. These include the Relationships Window, Performance Analyzer, Table Analyzer, and uploading data to SQL Server and SharePoint.

There are other differences that vary depending on the specifics of your database, but once you've uploaded your database to SharePoint, you need to be aware that some Access features are limited.

In my next post, (
Viewing In Sharepoint), I'll look at the tables in SharePoint.


No comments: