Friday, June 11, 2010

Access 2010 Web Databases: Viewing Tables In SharePoint

(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information:

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?). I discussed errors in creating relationships in a web application (Creating Relationships). Last time, I talked about loading the tables into SharePoint (Uploading To SharePoint).

This time, I want to take a look at the tables in SharePoint itself. It's not really necessary. You can and should do everything you need to do in Access. However, viewing your application in SharePoint can be instructive.

Go to File > Info:

[click to enlarge]

And click the link. The opening screen will look like this:

[click to enlarge]

The Design With Access link will allow you to make changes to the application in Access. I should point out here that you should always make any changes in Access and not directly in SharePoint. You can easily mundge your database into uselessness. Clicking the link will download a new copy of the application (with an ACCDW extension) to your hard drive. Make your changes and then save it back to SharePoint.

If you want to delete the application, under Settings, click the Delete This Site link. You won't do this often with a production database, of course, but if you're experimenting, you might need to do this. In the course this series, I did it quite a lot.

Table Properties
 I can look at my table properties as well. Navigate to your lists/table by going to a URL like so:


You should see your table/list in datasheet view. Click the List Settings button on the List tab of the SharePoint Ribbon.
[click to enlarge]

Clicking AuthorID, I can view the properties of that table:

[click to enlarge]

From this, I can see that all the properties I set up in Access – including the relationships – made the transition to SharePoint. However, I want to re-emphasize that although you can view your tables in SharePoint, you should never modify them there. With Access Services, everything begins and ends in Access.

Next my next post (Other Compatibility Errors), I'll finish up the data portion of this series by looking at other data related issues that you need to watch out for.


Simon F said...

Hi Roger
You directed me to this from a thread in LinkedIn. VERY informative thank you.
As of now I'm even more scared of Access Sharepoint
Simon Fenna
Lancaster UK

Simon F said...

Hi Roger
You pointed me at this blog from a LinkedIn thread.
Thank you - very informative.
Access Sharepoint is not for me at the moment - which is a shame. I'll keep my eye on it - maybe it will get more slick
Lancaster UK

Karen Maxwell said...

With the hosted SharePoint solutions, you can create custom themes, customize site navigation, create Wiki and web pages, put up new pages based on template or custom layouts. You can also set up content management systems with drag and drop and many other convenient features. For more information have a look at