Click Here to Install Silverlight*
United StatesChange|All Microsoft Sites
MSDN
The Beta Experience

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:

  1. Microsoft Office Access 2007 : A rich client for Windows SharePoint Services 3.0 – Mark
  2. MSDN Webcast: SharePoint Server 2007 Web Content Management
  3. Microsoft Office Access 2007 Beta 2 Offline Help

© 2014 Microsoft Corporation. All rights reserved. Contact Us |Terms of Use |Trademarks |Privacy & Cookies
Microsoft