SharePoint and Microsoft Access - Getting Motivated

March 16, 2009

Summary

I wrote this article to inspire Access specialists to have a go at Microsoft SharePoint and find out what it can do.

What is SharePoint and What does it mean to an Access Specialist?

SharePoint is a Microsoft server product that is primarily accessed using a browser. It is Microsoft's biggest "New" money earner in a while and they are working hard to integrate it into the Microsoft Office suite. In fact, if you read the ‘What's New’ section in the Access 2007 help, you will find SharePoint mentioned as an important new collaboration tool.  Apart from that mention, there was also a lot of behind the scene changes to Access to make it function with SharePoint, like multi-valued fields and macro improvements.

So what will SharePoint mean to you? That probably depends on where you work I guess.  First, there will be people who work in organisations that already have SharePoint.  If that is you, and if you don't understand SharePoint, it’s time to ask for permission to experiment in a SharePoint project and start to find out what it can do.

If you belong to a large company that doesn't have SharePoint, your only port of call will be to try and utilize SharePoint at Microsoft Live or convince your boss to pay for a SharePoint service to try it out for a while.

For the rest of you, you can either install SharePoint on your computer (tricky) or sign up for a SharePoint service like the one I use at http://www.sharepointhosting.com/. These online environments are quite cheap at $20 a month or so and come with enough functionality to undertake substantial trials of SharePoint.

Working with SharePoint Projects

The first way that an Access specialist can work with SharePoint is to value add the many templates that come built into SharePoint. If you look at Figure 1, you will see some of the many templates that can be used.

Now to illustrate what happens when you select a template, let’s choose the Issue Tracking template. This creates a SharePoint list. In Figure 2, you can see that I have added the first record using a web browser a few minutes after creating the template. To add a record, choose the New button (shown in a green circle in Figure 2) and you will be presented with what looks like a standard Access form (Figure 3).  You also have the option, on computers running newer browsers, to enter the data using a DataSheet form (Figure 4).  It is important to note that both Figure 3 and Figure 4 are SharePoint forms that work straight out of the box. It is also good to know that under the hood of the forms and lists are many properties that can be modified to come up with a better custom solution. Access developers will feel right at home when they dig into the properties.

Lets Link to the SharePoint List/Table

In all my time using Access, there is one thing that has always been valuable and that is linked tables.  Now you will be pleased to know that linked tables with read/write access to SharePoint lists are fully featured in Access 2007. This means that you can integrate your online SharePoint Lists into your own Access database forms, queries and reports.

So let’s find out how to link to the Issue Tracking List we just setup in SharePoint. Firstly, you need to choose the SharePoint List button on the External Data tab in the Ribbon as shown in Figure 5. This brings up a list of all of the SharePoint sites that you have been connected to in the past. If the project isn't in the list, copy and paste the HyperlinkAddress in your browser Address bar for the SharePoint project that your List is stored in. Figure 6 shows you a list of all of the Lists in the SharePoint project that you can link to.

At the end of the Linking Wizard, which will involve username and password and site location questions when you first run it, you will end up with a linked table as shown in Figure 7. This process, from start to finish, should take a  few minutes once you understand where things are entered.

Lets Export a Table to a SharePoint List

A different way of working, that you may find really handy with SharePoint, is exporting a table* that you would normally store in your database into a SharePoint project List. You will find that as SharePoint grows to be more popular, this approach will become popular too because you can solicit data entry/management from anyone who has a browser and uses the SharePoint site. This audience can be much bigger than the Access audience.

To export a table to SharePoint, choose the External Data tab in the Ribbon and select the table that you want to export. Now identify the SharePoint project that you want to export into and the table will be created in SharePoint. Once you have checked that the table is OK in SharePoint, delete** the table in Access and relink to SharePoint as per the previous instructions. You can see a table that I have exported from Access in Figure 8.

** NOTE If you wish to Rename the table in Access, choose the Office button in the top left corner of the Access window and choose Access Options. Look for Current Database and scroll down to Name AutoCorrect Options. Now unselect the Perform name AutoCorrect checkbox. This will ensure that the rename of the Access table doesn't affect other objects in the database. You can now safely rename the table. This will mean that the linked table can have the same name as the old Access table and your database should function as before. Remember to Select the AutoCorrect option at the end of the renaming exercise if you like using Access automatic renames.

Conclusion - How will SharePoint Help You?

As you can see from my samples, SharePoint has a lot in similarity to Access and that means Access specialists can help in this environment. It has database tables (called Lists) that need basic table design skills, the forms are similar and through linked tables, you can apply your Access reporting skills. Most importantly is that the Access developer can take the Access skill set of good business understanding and rapid development and incorporate that into a framework that feels a bit like Microsoft Access. Given that you can buy six months of online testing websites for less than $200, you really don't have any excuse not to give SharePoint a jolly good try.

Good luck to you and keep working hard on those skills, everyone needs an edge to stay employed these days.

Background Reading

At Microsoft, you can find the following articles, which will encourage you to go further with Access.

Introduction to integrating data between Access and a SharePoint site
http://office.microsoft.com/en-us/access/HA101314631033.aspx

Moving Tables To A SharePoint site
http://office.microsoft.com/en-us/access/HA101314681033.aspx?pid=CH101741461033

Creating Forms and Reports to use as views in a SharePoint site
http://office.microsoft.com/en-us/access/HA102093061033.aspx?pid=CH101741461033

» See All Articles by Columnist Garry Robinson