SharePoint and Microsoft Access - Getting Motivated

by  Garry Robinson

Summary

I wrote this article is 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 primary accessed using a browser. It is Microsoft's biggest "New" money earner in a while and they are working really hard to integrate it into the Microsoft Office suite. In fact if you read through 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 where you work I guess.  First there will be people who work in organisations that already have SharePoint.  If that is you then if you don't understand SharePoint, its 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 functionallity 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.

Figure 1 - Templates that can be added to a SharePoint Project

Now to illustrate what happens when you select a template, lets 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 better custom solution. Access developers will feel right at home when they dig into the properties.

Figure 2 - The initial data entry form in the Issue Tracking template

Figure 3 - The standard (one record) data entry form for Issue Tracking

 
Figure 4 - The standard datasheet data entry form with a combo box highlighted to show that they exist.

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 lets find out how to link to 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 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 the Lists in the SharePoint project that you can link to.

At the end of the Linking Wizard, which will involved 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.

Figure 5 - The standard datasheet data entry form with the combo box highlighted

Figure 6 - The standard datasheet data entry form with the combo box highlighted

Figure 7 - The SharePoint list viewed through a linked Access table

 

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 to because you can solicit data entry/management from anyone who has a browser and get use the SharePoint site. This audience can be much bigger than the Access audience.

* NOTE: It is my view that anyone running a large Access database will not want to put all of the database into SharePoint. Rather I believe it will be far more prudent to put just a few tables into the SharePoint site.

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 to 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.

Figure 8 - The SharePoint list viewed through a linked Access table

** 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

 

Garry Robinson from Sydney, Australia

Author Bio.

Garry Robinson runs GR-FX Pty Limited, a company based in Sydney, Australia. If you want to keep up to date with the his latest postings on Access Issues,  visit his companies popular web site at  http://www.vb123.com/  or sign up for his Access email newsletter by sending a blank email here.   The web site features many Access resource sand software  that are used by  more than 10,000 readers a month. To find out about Garry’s book which is called “Real World Microsoft Access Database Protection and Security”, point your browser to http://www.vb123.com/map/. You can find Garry’s contact details at  … www.gr-fx.com