Access 2007 and Windows SharePoint Services 3.0
Author: David McMahon MVP, MCDBA, MCAD
Contents
Introduction
So we have a new version of Office out soon, in case we hadn’t heard ... 2007 Microsoft
Office system. I can imagine I hear right now some collective groans going around
the world. Company IT Managers and Budget holders will be holding their combined
breaths as they wonder is it going to be worthwhile upgrading at last those Office
2000, Office XP applications? Well, therein lays the battle that Microsoft will
be facing in the coming year. How to convince that the upgrade is worth it, that
there is a definite benefit in moving to the latest version of the now ubiquitous
workplace software that is Microsoft Office. Office is getting a severe makeover,
no more piling on the icons and piling on the functionality irrespective of whether
or not we want it, Microsoft have changed a great deal over the last few years,
and 2007 Microsoft Office system is one of the first major manifestations of those
changes, Windows Vista being another.
Getting Excited Again About Office
From my perspective as a .NET Developer, I can unequivocally say that this is the
first time since 1997 when I have actually been ‘excited’ by the prospect of a new
Office Suite. I had my interest sparked by seeing the ‘Ribbon’ for the first time
about 9 months ago, and my interest was turned into a great desire to find out more
when I attended the London Office DevCon in June of 2006 when I saw Mark Boour of
Microsoft give a talk on the possibilities of Access 2007 and SharePoint Services
working together. I am by trade a .NET and SQL Developer who tends to specialize
in XML , XML Web Services but I originally entered the IT industry as an Access
2.0, Access 95 and then Access 97 developer. Soon after Access 97 was released,
I started moving into VB6 and SQL Server and then .NET and I haven’t ever really
gone back to Access since about 1999. So I was intrigued by what I had seen Mark
Boour demonstrating and a soft spot for Access has prompted me to really take a
look at the product again and the good news is Access is very much alive and kicking.
Hopefully 2007 Microsoft Office system will breath not only new life into the product
but make it a serious contender again as a rich client front end for database applications
and for administering/content managing web applications.
Where Does Access Fit Into The Scheme Of Things?
Firstly let’s address the question of where Access fits into the scheme of things
now. If we want a simple database system surely there is SQL Server Express? Whilst
that is true, it was pointed out to me by a member of the Office Team in Reading
UK that SQL Server is really a data repository, and whilst we have the SQL Server
Management Studio it is not a tool that a non-developer would be generally be comfortable
using, neither is it a tool a DBA would wish to publish out to end-users. Nevertheless
SQL Server offers an enterprise capable database engine which is robust, scalable
and designed to be performance tuned under a number of different circumstances.
What about Access? Access is essentially a desktop database solution. The features
of the product are geared more to ‘ease of use’, ‘rapid development’, ‘ease of deployment’
rather than scalability. Access is essentially a ready-built application which can
act as a front end to a database. That database can be Access itself, SQL Server,
an ODBC enabled database, Paradox, Excel or more significantly in the context of
the new Office Suite, Windows SharePoint Services 3.0 (WSS).
So, if we are an end-user with some skills at manipulating the Office products,
our starting point would probably best be Access.
If we are a developer or part of a development team and we are building an application
which would need custom functionality, our starting point would probably be SQL
Server.
So, what’s New in Access 2007?
What are the key points to take on board about Access 2007 over previous versions?
First off, the look and feel is completely different. The ‘Ribbon’ is now the main
way to interact with our Access database. It makes the appearance of the Office
Suite completely different from previous versions, but I have found it to be very
easy to get to grips with and after only a few minutes I felt it was easier to work
with than the standard toolbars and menus. All the main Office Applications have
the Ribbon including Word. This article was written in Word 2007 and I found it
a more intuitive experience than in Word 2003 with tasks like importing and editing
images much easier than ever before. The dynamic preview feature in Word is fantastic
and allows us to see how the document will look without having to physically make
the changes first. It really is a very natural way of working. Even on XP the look
and feel is a great experience, I’ve seen it on Vista and it is fantastic looking
as well as functionally rich and easy to use.
So apart from look and feel what else is new or different? Well it gets a new file
format, the accdb file format, which is in line with the Office Open XML File format.
This provides a number of enhancements, including stream-lining its interaction
with WSS 3.0 and a bringing together of the data types in Access and SharePoint.
Access 2007 now supports ALL the WSS 3.0 data types. There are a multitude of other
features such as multi-valued lookup lists, the ability to take off-line linked
SharePoint lists and synchronize later and export to PDF natively. The best way
to get a feel of the new way Access 2007 works is to go through an example of creating
a database in Access 2007 and then linking it to SharePoint. This is what the rest
of the article will cover. We’ll also look at gathering information and inserting
it into the Access 2007 database using the new InfoPath Email Data Gathering feature
which is immensely powerful and offers up simple ways to extend the reach of data
gathering with Access 2007.
Creating a Web Based Application Starting With Access
In our first example we will create a database in Access 2007. Populate it with
some data, create some lookup tables, a simple form and a report. We will look at
how we can populate data in the database using email and InfoPath and then finally
link the data into SharePoint so that the data can be viewed and edited around the
Enterprise in a consistent fashion.
Create the Database
So let’s look at creating a new blank database in Access 2007. We can choose from
a number of database templates, but we’re going to start from scratch and point
out a few things along the way.
Figure 1 shows the splash screen for Access 2007. We can see plenty of template
databases for all sorts of useful applications, and we may simply want to tweak
one of the pre-built databases. However to show some of the neat features of Access
2007, we will build a small database from scratch.
Figure 1 : Access 2007 Splash Screen
So first off, we click on “Blank database”, and in the bottom right hand corner
of the Splash Screen we see the space to name the new database. We will name this
database SpaceNews. We are going to create a database which will contain articles
about the latest discoveries from Hubble and other Astronomical observatories. Notice
the .accdb file extension; remember this is the new Office OpenXML File Format.
Creating the First Table
So we see now the main working screen of Access 2007 as shown in Figure 2. It looks
a bit different from other opening screens in previous versions of Access, as with
this version we can get right down and start creating a table from the word ‘Go’!
Figure 2 : Opening Screen Of Access 2007
We see the new Ribbon we have already mentioned. The Ribbon is really a combined
menu/toolbar and really does make work in Office that much easier. If we decide
that the Ribbon takes up too much space in some views well, no worries, we just
double click on the currently selected tab and the Ribbon will ‘disappear’. We may
also want to see a more ‘classic’ toolbar in which case we can configure the Quick
Access Toolbar from the top right hand of the screen as shown in Figure 3 below.
Figure 3 Hidden Ribbon and ‘Classic’ Toolbar View
The Ribbon bar alters its appearance and contents to suit the context in which we
are working. Here for example it is already in the Table editing context and we
see there are groups of icons for Fields and Columns, Data Type and Formatting and
for Relationships.
In Figure 2 we also see the ‘Navigation Pane’ on the left hand side. This provides
a configurable display of objects in the database which again can be minimized if
not required, but it generally proves to be a very useful quick access to objects.
Notice also that the cursor has defaulted to the first column after an AutoNumber
column called ID. Let’s type in “Hubble Spies a New Cluster of Galaxies” and click
Enter and we see the data has gone into the field AND a new field has appeared.
To change the name of the field we have just entered data in just double-click the
cell with the name Field1 in it and change the name, in this instance to “Title”.
That’s all there really is to creating a table, just keep adding fields. To change
the data type of a field is child’s play. No more having to switch to Design View,
simply click on the field we want to change the data type of and select from the
drop down in the Data Type and Formatting group on the Ribbon. In Figure 6 we change
the data type of the AuthorId field to Number. There are significantly less screen
changes and mouse clicks to get the job done in Access 2007 than in previous versions.
Figure 4 : Changing the Data Type.
The fact that we’ve changed the AuthorId indicates we probably want to create an
Author table and link it to this column, thus creating a Foreign Key relationship.
So let’s create an Author table with an ID column and a FullName Text column and
then click on Relationships in the Ribbon, we are then prompted to add in the tables
to the Relationship Designer, but again notice the Ribbon in Figure 5. It’s changed
again and is showing us only what we need when dealing with Relationships. So let’s
go ahead and create a relationship between the Article.AuthorId and Author.ID field
as shown.
Figure 5: The Ribbon Changes According To What We Are Doing
We can then go back to our Article table (by double clicking on the Article Table
in the Navigation Pane) and switch to Design View and make the AuthorId field have
a data source of a lookup based on the Author table. This is done exactly as in
previous versions of Access. Lets then populate the AuthorId field with some data
so that each Article has an Author.
Now when we go back to our Author Table we see in Figure 6 we get the automatic
drill-down as was available in Access 2003 since Access has picked up the fact that
we have a Parent/Child relationship between Author and Article. However it took
far fewer steps to do it in Access 2007 than in the earlier versions.
Figure 6 : Automatic Drill Down
Quick Search
One nice feature we’ve not yet looked at is the Quick Search built into Access.
In Table View, Query View and Form View at the bottom of the screen we see the Search
box. Typing into this box the text we want to find will highlight any matching text
in the current record. It’s not on a par with Vista Search, but every bit helps!
Figure 7 : Quick Search Box
Creating a View
Creating Views in Access 2007 is again a simple exercise with Ribbon and uses the
well known Query Designer that we are all familiar with. Select the Create tab in
the Ribbon and then the Query Design icon. This brings up the Query Designer for
Access 2007 where again we see the Ribbon has changed its contents to match the
task at hand.
Figure 8 : Creating a View
Creating a Form
Creating professional looking forms for data entry and editing is a trivial exercise
in Access 2007. Again click on the Create tab of the Ribbon, select the table we
want to create a form for and then in the Forms Group of the Ribbon just click the
Form icon. This results in a neat looking form as shown in Figure 11:
Figure 9 : Simple Form Creation
The Form can be easily styled using the Quick Format Group that has now appeared
in the Ribbon on the far right hand side. Simply hover over the style we want and
click and our Form will be instantly restyled. We can create our own styles of course
or we can choose from a good number out of the box. We can also create with a single
click on the Ribbon , Split Forms which combine a ‘List’ and ‘Detail’ part , Repeating
Forms which are made of repeating sections, Blank Forms for Custom Designs and of
course there is a Wizard for creating Dialogs and Pivot style forms.
Just going back briefly to the Article Table, one new feature of Access and one
which moves it into line with SharePoint data types is the introduction of the Attachment
Data Type. In previous versions of Access, when images and other documents are
imported into Access they oure stored as OLE Objects which effectively converted
them into bitmaps which could result in a great increase in size. In Access 2007
such objects can be stored as Attachments and they are stored in their original
native format. This allows us to continue to be able to open the object/image in
its original state and to carry out searches on the image/object . So we will add
in an attachment field to our Article table. If we set the value of the attachment
for each record as a .jpg file and then recreate the Form shown above we get the
nice result in Figure 10.
Figure 10 : Linking in an Attachment
Creating A Report
Creating a report in Access 2007 is a simple as creating a Form. Select the Create
tab on the Ribbon and in the Reports Group, click on the Report icon. This quickly
produces a styled report which we can print and email etc.
Figure 11 : Creating a Report
Collecting Data In New Ways
So we’ve taken a look at the new look and feel of Access 2007, we’ve seen how easy
it is to create tables, view, forms and reports and how to make use of the new Attachments
data type to link into non-relational data such as images, let’s now take a look
at how we can gather data in an extended way using email.
Supposing in our scenario, one of our roving reporters wrote a great new article
for the Space News site and supposing that say for security or business reasons,
no web front end is available for content management, how then could our roving
reporter send in data to the site? Well, by making use of InfoPath forms in Outlook
we can quickly and easily set up a data gathering mechanism. The basic mechanism
is to send emails to the people who have permission to add and update articles in
the table we have selected and then when the replies come in, data is either added
or altered as required.
Here’s how it works.
In the Access 2007 database, click on the table we want to gather information for
and then select the External Data tab on the Ribbon and click on the Create Email
icon
Figure 12 : Collecting Data Through Email
This then opens up the “Collect Data Through Email Messages” Wizard. Click Next
and we have the option to create an HTML Form or an InfoPath form. In our example
we will create an InfoPath form, and our next choice is to create an InfoPath form
to either Add or Update data in our table. The ‘gotcha’ here is that in order to
update data, the email address of the recipient of the email must be stored against
the data in the table. If we only want to add data we do not need the recipient
email address. The email address must be in the table being updated or in a related
table. Our Author table has a field called EmailAddress which contains the various
emails of the authors. We then can select the fields we want to collect data for
and decide whether or not we want the data to be automatically added to the database,
and whether or not we want to restrict data collection to updates only. Next (if
we have selected to update existing data) we are quizzed about where to find the
email address of the recipient and finally we are asked to pick which email addresses
to send the data gathering emails to. At this point we are now ready to start the
data collection by email. The final step in the wizard is to send the emails in
this case the emails will come from our own logged on user which is mcmahond.
So let’s say in our example we have sent an email to weaverd@gaul.local which is
the email address of a user with a login of weaverd. When weaverd opens up Outlook
2007 he/she will see an email in the inbox sent by mcmahond . The will contain a
repeating form of records which he/she has previously edited plus a link to enter
the new data. The great thing is that if a data field is constrained by a lookup
list, the lookup list is included in the email. Once the data is edited or added,
then weaverd submits the email, which sends the email back to mcmahond If mcmahond
(i.e us) now opens Outlook 2007 we will see in the Inbox a folder called “Access
Data Collection Replies”.
Figure 13 : Receiving Data Via EMail
If we open the email we will see the article entered by weaverd. To update the database
simply click on the Ribbon Icon “Export Data To Microsoft Access”. This completes
the data collection by email cycle and either updates existing article or adds new
article to the database. This provides a fantastic feature for Access which maybe
was possible in previous versions of Access but would require a considerable amount
of bespoke programming to achieve.
Figure 14 : Completing the Email Data Collection
So we can see that data collection by email extends the reach of Access 2007 to
beyond the local environs of the company network or desktop with no real effort
on behalf of the user. Lets continue the theme of moving access beyond the desktop
and see how we can extend the reach of our desktop database to include the Intranet
and Internet by linking our Access 2007 database into WSS.
Moving Tables into SharePoint
Lets briefly examine why would we want to move our database into SharePoint and
what are the drawbacks and benefits of doing so?
The main drawback is that we lose referential integrity if we transfer our data
to SharePoint. This is simply due to the way that data is stored in SharePoint.
Our tables will be converted to what is known as a SharePoint List which exists
as a separate entity within SharePoint. This is in fact not the end of the world.
We can implement workflow rules, and data entry rules within SharePoint to effectively
re-establish referential integrity and limiting access to data to those who have
the need to alter data will mitigate most situations where this loss may have caused
issues.
The main benefit is that our deskbound data becomes widely accessible throughout
our Enterprise and is stored in a scalable back-end store (SQL Server 2005) with
all the benefits of robust concurrency and security and automatic backing up that
that database engine brings. This linking of data could be done in Access 2003 but
it is faster and there is a one-to-one mapping between Access and SharePoint data
types, including the Attachment data type described earlier in this article. So
how do we go about moving (or linking) our data to SharePoint?
Again with Access 2007 it is a very simple exercise. First we need obviously to
have a SharePoint site to link to, and in this example we have a WSS site called
Space News which is our Intranet site which publishes the latest articles on Hubble
and Space. We’ve skipped a few steps here. We created the SharePoint site before
we start from a template in Microsoft Office SharePoint Server (MOSS) and in fact
using the new SharePoint Designer , previously known as FrontPage, we altered the
layout of our default page to suit our requirements. The home page at the moment
is decidedly uninteresting at the moment as shown in Figure 15.
Figure 15 : Empty WSS Home Page Site
In order to spruce up the site and make it somewhat more useful, we are going to
do the following:
- Create some Custom Lists (Tables).
- Populate some SharePoint Web Parts using the Custom Lists.
- Link the Web Parts to filter the information as necessary.
The first of these we can drive from Access using the “Move To Access” option in
the Ribbon as shown in Figure 16.
Beta 2 Note : In the Beta 2 version this particular operation appears to have an
issue when there is an Attachment data type in a table. The online help does note
that we should only have one Attachment column in a table, but I could not get a
successful upload unless I removed all Attachment columns. Hopefully this issue
will be resolved in the final version.
Figure 16 : Starting the Move to WSS
First off in the Wizard we are asked what SharePoint site we want to export to and
whether we want to actually upload the database to SharePoint and get links to the
forms and reports in the database. This option does a complete upload and converts
Tables to SharePoint lists.
Figure 17 : Selecting the SharePoint Site
Next we are prompted to specify the library location within SharePoint. Libraries
are collection of Lists and other objects, here we will simply specify the Documents
Library which is a central repository of any type of document at the top level of
the SharePoint site
Figure 18 ; Choosing the SharePoint Library
In this case there are no documents within the Document Library at present so we
just click OK and then Next and wait for the Wizard to finish exporting to SharePoint.
Invariably when the export is complete there are some issues which could not be
resolved, it was mentioned earlier that referential integrity is not preserved,
so foreign key constraints are not exported to SharePoint. All the issues encountered
are stored in a table called ‘Move To SharePoint Site Issues’. If we are happy that
no issues have been logged which endanger our application we can choose to delete
this table. After the completion of the wizard we will see that the tables are shown
as Linked Tables
Figure 19 ; Tables are now linked to SharePoint
We can also inspect our SharePoint site by navigating in our browser to our site
then selecting Site Actions and Manage Content and Structure. When we do this we
see three new lists called Article, Author and Category which correspond to our
tables in Access. We see also that in our Document Library we have a copy of the
complete database.
Figure 20 : Newly Created Lists in SharePoint
So let’s get our data presented on our Intranet using this linked data. To do this
we need to do a few things:
- Create a new column in our List.
- Create two new views (Queries) of our data
- Upload some images to link to our Article data.
Altering Lists and Creating Views in SharePoint is very easy we simply select Edit
Properties from the drop down against the Article List as shown in Figure 23.
Figure 21 : Start to Create Views of SharePoint Article List
Once we are in the editing screen for the Article List scroll down to the middle
and click on Create Column. Now we can add a column called ImageURL. This will allow
us to link to an image which will be displayed with the Article.
Now scroll to the bottom and select Create View. This will take us to another screen
where we can select which fields in the Article List we want to see and also how
many records the order of display etc. This is very similar to creating a Query
in Access. So let’s create a View then called ‘Article Summary’ and include just
2 fields, the Title field which came from Access and the new ImageURL field which
we created just now in SharePoint. Let’s create another View which contains just
2 fields both this time from the original Access database: Title and Content. We
will call this View ‘Article Content’.
Before we can construct our home page for our SharePoint site, we need to do one
more thing and that is to upload the images we need. This again is very simple to
do. We go back to our ‘Site Content and Structure’ page and on our Images List we
select New /Item.
Figure 22 : Adding a New Image
This opens a screen where we can upload images. We can upload a number of images
using the ‘Upload Multiple Images’ option and these images will then appear in the
WSS Images Library. At this point we are ready to construct our home page.
Constructing Home Page in WSS
So to create the home page we need to select Edit Page from Site Actions menu.
Figure 23 : Creating The Home Page
Once in edit mode we can see the placeholders for the SharePoint Web Parts.
Figure 24 : WSS Home Page in Edit Mode
Clicking on the top left web part placeholder will open up the dialog box from which
we can select which List or Library to add in as a Web Part. Our Articles List will
be in the dialog so let’s select that and click OK. This will add our Article List
in as a Web Part. Unfortunately it will be shown in the default view which will
have all of our fields which we don’t want to show. We want to see the ‘Article
Summary’ view we created which contains the Title and ImageURL. So we will select
Modify Shared Web Part from the edit menu for the Web Part as shown:
Figure 25 : Modifying the Web Part
This will show the Modify Web Part window on the left hand side of the screen. In
the Views Drop down select the ‘Article Summary’ view and click OK. This will then
change the appearance of the Web Part to how we wanted it i.e. a compact list which
shows 5 article titles and Image URLS at any one time. Now let’s do the same exercise
with the bottom Web Part place holder. This time we select the ‘Article Content’
view. This will then display ALL of our article’s content in the web part, but we
only want to show the content of an article we select in our ‘Article Summary’ Web
Part. We can filter the content of one Web Part with another by means of Web Part
Connectors. Figure 28 shows how we link our ‘Article Content’ Web Part to our ‘Article
Summary’ Web Part (here called Articles[1]).
Figure 26 : Connecting Web Parts
We select from the dialog the fields within each Web Part we want to link. In this
case it is the Title field, and when we click OK we see now that our ‘Article Summary’
Web Part has Radio Buttons against it and when we select one, the content of the
‘Article Content’ Web Part changes accordingly.
All that remains to be done is to link in our images. Firstly we need to update
our Article List with some actual image data. To do this we need to select the article
we want from our ‘Article Summary’ Web Part and select ‘Edit Item’ from the drop
down list which appears. We then enter the relative URL of the image which we want,
Figure 27 shows this for an article:
Figure 27 : Entering the Image URL
Once we save this by clicking OK, we can go back to editing the home page. Now when
in editing mode, we want to add an Image Web Part into our top left hand Web Part
placeholder. We can then connect this Image Web Part to our ‘Article Summary’ Web
Part via the Image URL field. We then Check In and Publish the page in the top editing
control bar and then Approve the page (assuming we have these permissions else we
get the appropriate person to do this). When we then navigate to the home page and
select the first article in our list we see the colorful page as in Figure 28.
Figure 28 : The Finished Home Page
Now we have a fully Intranet/Internet capable application which is backed by SharePoint
Services and SQL Server 2005. The neat thing is if we go back to Access 2007 we
will see the additional field we have added in SharePoint and the data it contains.
Also, if we alter our data in Access 2007 it gets altered in SharePoint, so Access
2007 becomes a desktop Content Management application for us. The link between Access
2007 and SharePoint is a true two-way street. Data updated in Access gets reflected
in SharePoint and vice-versa. There is also an offline story too. We can choose
to take our data offline by clicking on Take All Offline on the Ribbon, work on
the data in isolation and then resynchronize our changes later. Combining Access
2007 with WSS really does make for an extremely powerful and scalable solution.
Summary
It has been quite a journey, and for me very exciting to visit the latest incarnation
of the application which led me into the IT world. We have taken a look at some
of the changes Access 2007 introduces over previous versions and taken a look at
the new layout and the way the contents of the Ribbon bar in Access changes depending
on the context of our work. We’ve seen how to extend the data gathering reach of
Access by including InfoPath email forms and how to extend the reach of the Access
database by linking it into WSS. It’s been a while since I’ve really worked with
Access, but I can’t wait to see this version released and work with some of our
clients to help consolidate their disparate databases into a central consistent
data store yet allow them the flexibility to keep on using Access as their preferred
client application. Exciting times ahead for the Access developers of the world!
References:
- Microsoft Office Access 2007 : A rich client for Windows SharePoint Services 3.0
– Mark
- MSDN Webcast: SharePoint Server 2007 Web Content Management
- Microsoft Office Access 2007 Beta 2 Offline Help
|
