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.
Click for larger image
Figure 1 – Templates that can be added to a SharePoint Project
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.
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 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.
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
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.
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 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.
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
»
See All Articles by Columnist Garry Robinson
