Thursday, June 3, 2010

Access 2010 Web Databases: Uploading to SharePoint

(Note: The information below is applicable to Access 2010 web databases. It may not be correct for Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)

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 http://www.accesshosting.com/. 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.

.

2 comments:

Lori said...

When opening my DB from sharepoint i receive an error that states "failed to start macro 'VerifyUsers'. That macro is not listed in the macros list within the db.

Anonymous said...

Is there a way to do this in sharepoint 2010 standard server?