HOUR 18
Sharing Access 2010 Tables with SPF 2010

What You’ll Learn in This Hour

Benefits of having SharePoint Lists stored in Access 2010

Creating a linked Access 2010 Table from a SharePoint List

Importing a SharePoint List to become an Access 2010 Table (one-off)

Exporting an Access 2010 Table to become a SharePoint List (one-off)

Starting with an Access 2010 Table and creating a linked copy as a SharePoint List

Creating a SharePoint List while in Access 2010

The differences between using Access 2010 with SPF 2010 and using Access 2007 or Access 2003

Using Access 2010 with SPF 2010 is made perhaps unnecessarily difficult by there being several ways of doing it (and by unclear names in the ribbon). Each of these methods have their own benefits and restrictions, which is why this hour works through all the options and notes their plusses and minuses.

Benefits of Having SharePoint Lists Stored in Access 2010

The possibility of storing any SharePoint list (except surveys and discussions) as a table in Access is something that first became available in Access 2007. This hour includes a description of how to do this when using SPF 2010 and Access 2010. Differences when doing this using Access 2007 are outlined in the “Q&A” section.

The main reason for storing a SharePoint list in Access 2010 is because Access 2010’s reporting facilities can then be used on those tables. This provides a way of creating reports from one or (combining) several SharePoint lists that SharePoint doesn’t provide.

A secondary reason for storing a SharePoint list in Access 2010 is because people who want to do all their work in Access 2010 can then edit the SharePoint list in Access instead of needing to go to the SharePoint site.

Prepare for the following section by creating a Links list (ComputerCompanyLinksList) in the default site and populating it with some entries. (I use HP www.hp.com, IBM www.ibm.com, and Microsoft www.microsoft.com for the examples that follow.) You have created Lists several times by now, so I won’t give details beyond the start (Site Actions > More Options > Links).

Scenario 1: Creating a Linked Access 2010 Table from a SharePoint List

To store a SPF 2010 list as a linked Access 2010 table, follow these steps:

1. Open Access 2010; use the Blank Database option (see Figure 18.1).

2. On the right of the same screen (and lower), there is an entry box for the database name. Change the name to BookDatabase1.accdb and click Create (see Figure 18.2).

3. Select External Data.

4. SharePoint List is no longer (as in Access 2007) visible in the Import section, so you need to first access the drop-down by selecting More in the Import Section (see Figure 18.3).

5. Click SharePoint List (see Figure 18.4).

FIGURE 18.1 Using the Blank Database option

FIGURE 18.2 An empty database

FIGURE 18.3 External Data–SharePoint List

6. Change the name of the site address so that it actually is a SharePoint site. Here that means changing the name to http://SPF 1 or, depending on your working environment, to the TCP/IP address.

FIGURE 18.4 Source and destination of the data

7. You can leave the default value of Link to the Data Source by Creating a Linked Table. This sets up a two-way link that is the usual way we want to use the SharePoint List / Access 2010 table combination. Click Next.

8. You’ll be asked as usual to log in to the SPF1 site (don’t forget to make sure the domain is listed as SPF1), and then you see something like Figure 18.5.

FIGURE 18.5 A selection of Lists for import to the database

9. Here for simplicity we just import one list, although because these are check boxes, it would be possible to import several lists. Select the new Links list I suggested you create at the start of this section. Click in the Link box for Computer-CompanyLinksList and then click OK.

10. Double-click the item ComputerCompanyLinksList listed under Table1. You see Figure 18.6.

FIGURE 18.6 A new Access 2010 table from SharePoint

Let’s see what happens to it if we add a value to this table. Because the table has now been created in Access 2010, we can start a new set of actions by following these steps:

1. Click the square under HP.

2. Type in Oracle. You notice that Access 2010 automatically thinks the URL is http:/Oracle.

3. Type in www.sun.com in the next line. Access thinks this is the description.

In Access 2007, there was a Refresh List option. This is no longer needed because if you now go to your SharePoint site and open this Links list there, you see two additional entries (Figure 18.7).

FIGURE 18.7 The revised SharePoint List with data from the Access 2010 table

Clicking the Edit icon in the row that says the URL (description) is www.sun.com displays Figure 18.8 where the error that came because of adding a row in the Access 2010 copy of this list can be corrected.

FIGURE 18.8 Correcting a Links List item added in Access 2010

Change the description in Figure 18.8 to Sun; repeat for the Oracle item (there you change the URL field), and all is well.

Scenario 2: Importing a SharePoint List to Become an Access 2010 Table (One-Off)

To do this, you need to initially follow the steps from the previous section. Here are those same steps in short form referring to the previous diagrams:

1. Open Access 2010; use the Blank Database option (Figure 18.1).

2. Find the entry box for the database name and change the name to BookDatabase2.accdb. Click Create. (Figure 18.2, apart from the name of the database, is identical to what you see here.)

3. Select External Data.

4. Access the drop-down by selecting More in the Import Section (Figure 18.3).

5. Click SharePoint List (Figure 18.4).

6. Change the name of the site address so that it actually is a SharePoint site. Here that means changing the name to http://SPF 1 or, depending on your working environment, to the TCP/IP address. From now on things will be different.

7. This time do not accept the default value but select instead the alternative of Import the Source Data into a New Table in the Current Database.

After a possible interim request to log in to the site (if you work through this entire hour in one session, you might not need to), you again see Figure 18.5. This time select a different list—say BookContacts1—and click OK.

One different thing that happens compared to the first scenario is that this time there is the opportunity to save these steps so that they can be repeated (see Figure 18.9).

FIGURE 18.9 Save Import Steps

At first, you may wonder why this scenario has this step but the first scenario doesn’t. A moment’s reflection gives the answer.

The first scenario establishes a permanent link between the SharePoint list and the Access 2010 table. There is thus no need to repeat the action.

This second scenario is a once-only copy of a List from SharePoint to Access 2010.

If we, for instance, want to create a report from a List (see Hour 19), we might want this report irregularly; in such a case there would be no good reason for having a permanent link between the List and an Access 2010 table. So we would copy the list across, use it (in Access 2010) to create the report, and then delete the Access 2010 table. In a few months’ time we might want a new report using new data, so we would then copy the list to Access 2010 again. The import steps are not many, but not needing to do them again after saving them with Figure 18.9 can cut down on the time this would take.

The database and table combination created as a result of following the preceding instructions differs from Figure 18.6 in two respects:

It contains the contents of a different List.

Although you can add new items to the table (and edit or delete), these changes will never be visible in the original List.

Scenario 3: Exporting an Access 2010 Table to Become a SharePoint List (One-Off)

The previous two scenarios of the interaction between SPF 2010 and Access 2010 both originated at the SPF 2010 end. The following three scenarios originate at the Access 2010 end.

The first of them is roughly the opposite of Scenario 2. To start this scenario we need an Access 2010 table. The table created in Scenario 2 is suitable for our use because it is already in existence and it is not linked in any way to SPF 2010. To make quite sure of that, add a few additional contacts and make sure that they do not appear in the equivalent List in SPF1. They don’t, so we can use this completely separate table (see Figure 18.10).

Do the following steps:

1. Select External Data.

FIGURE 18.10 The new Access 2010 only Book-Contacts1 Table

2. In the Export Section, select More.

3. In the More drop-down, select SharePoint List. This displays the Export screen (see Figure 18.11).

FIGURE 18.11 Export data to SharePoint list

4. Change the Site address if necessary (here to http://172.27.89.106).

5. Change the name of the List to exAccessBookContacts1.

6. Leave Open the List When Finished selected.

7. Click OK.

The Team Site opens, and there’s a new List (see Figure 18.12).

FIGURE 18.12 The newly transferred List

Following the creation of the site, a screen similar to that shown in Figure 18.9 appears; only this time the screen is for saving Export steps (just close it).

Logic says that this, like in Scenario 2, means that there is no synchronization between the List exAccessBookContacts1 and the Access table BookContacts1, but let’s do a couple of brief tests just to make sure. Perform the following steps:

1. Add a new contact in exAccessBookContacts1.

2. Go to Access 2010. The BookContacts1 table is probably still open and looks like Figure 18.13.

FIGURE 18.13 The revised (in Access 2010) table

3. Try Refresh All (Figure 18.13). Nothing happens.

4. Try closing the database, opening it again, and then selecting BookContacts1. Still no change.

So we have now satisfied ourselves that this third scenario means that there is a once-only SharePoint list copy of an Access 2010 table that is created when using Share-Point List from the More drop-down in the External section of the External Data menu item. We need to look elsewhere for a linked (and synchronized) list.

Scenario 4: Starting with an Access 2010 Table and Creating a Linked Copy as a SharePoint List

This is the second scenario starting at the Access 2010 end. It’s roughly the opposite of Scenario 1; that is, after this scenario is complete, there will be synchronization between an Access 2001 table and a SharePoint list.

To start this scenario, we again need an Access 2010 table. The table created in Scenario 2 is still suitable for our use because it is already in existence, and it is still not linked in any way to SPF 2010, despite copying it to become a SharePoint list in the Scenario 3.

In Scenario 3, we added a few additional contacts, so Figure 18.10 (or Figure 18.13) that includes those extra contacts is our starting point here again.

Here are the steps to accomplish this:

1. Open the latest database in Access 2010. (It might still be open if you haven’t taken a break.)

2. Click the menu item Database Tools. (Not exactly obvious is it?)

3. There’s a SharePoint icon in the move section; look at the following By the Way (and Figure 18.14) and then move on to step 4.

FIGURE 18.14 A clear (?) information box

There is no alternative. We’ll have to continue the steps and see what the end result is.

4. Click SharePoint.

You now see the usual kind of request to specify the site where the List should be created. Figure 18.15 also gives us some information that to a certain extent clears up the confusion.

FIGURE 18.15 Move your data

What is moved is only the data not the database table itself. (However, does this mean the database will be empty at the end of the process?)

5. Enter the site you want to use.

(I’m going to use http://192.168.1.2 here because I’ve switched networks between Scenarios 3 and 4.)

6. Click Next.

The next screen is just a confirmation screen enabling you to click Finish to, well, finish, but it also contains a check button for Show Details. You might as well select this because all that happens is that a text appears in the same Finish screen (see Figure 18.16).

FIGURE 18.16 Export Tables Details

7. Click Finish.

At the end of this process, you are still in Access 2010, and you can double-click Book-Contacts1 to see that indeed no Data has been lost. It might have been Copied to a SharePoint List, but it hasn’t been moved.

The next step is to check the SharePoint site to see if there is a new List that has been created there. One thing that was different in this scenario was that we were not asked for the name of a List. So either data has been added to the List of the same name, or we see two Lists with the same name.

There’s a new set of steps to follow (now in the browser):

1. Open or refresh the site (at the home page).

What you’ll see is a list of Lists in the Quick Launch section that includes only one List called BookContacts1. This doesn’t fool us into thinking that this means there is only one list with that name because previously in this hour, we saw that you can have Lists created that are not listed in Quick Launch without some manual action on our part.

2. Click All Site Content (below the Quick Launch area).

Scroll down the page, and you’ll see something like Figure 18.17.

FIGURE 18.17 An extract from All Site Content

So that solves both questions. There are two lists, and the way to tell them apart is that the new List created in this scenario has _1 at the end of the name.

Scenario 5: Creating a SharePoint List While in Access 2010

The final thing you can do with SharePoint when in Access 2010 is to create a SharePoint List. For completion, I’ll cover this method by giving a brief guideline to it although, in my opinion, people who don’t create SharePoint lists in SharePoint are odd.

The steps in brief (and with no images) are the following:

1. (In Access 2010) Click Create in the Menu line.

2. Click the SharePoint List icon; there is a choice of the List types (not all) that can be created.

3. Select Events.

4. On the next screen, specify the address of the SharePoint site and give the list a Name (compulsory—without a name OK is not accessible) such as EventsfromAccess.

5. Click OK.

At this point, we are still in Access 2010 where there is an EventsfromAccess table. So we need to go to the site again.

6. Access the site.

7. Click All Site Content. EventsfromAccess is listed in the Lists section.

8. Click EventsfromAccess. An empty calendar appears.

9. Specify an event over several days.

10. Click OK.

The calendar now shows the new Event.

Now to see if this new List is linked to the Database table we also created with the first few steps:

11. Open Access again.

12. Select the EventsfromAccess table.

13. Right-click.

14. Click More Options (see Figure 18.18).

FIGURE 18.18 DB table right-click plus More options

15. Ignore the small pop-up asking for parameter values by pressing OK.

I still don’t like the idea of creating a SharePoint list in Access 2010, but there is one advantage to doing so, provided you can accept the restrictive list of List types you can choose. The advantage is that, with one action, you both create an Access 2010 table and a SharePoint 2010 List and also link the two.

Finally, what about the final option in the list of List types you can choose? Existing SharePoint List.

This is actually nothing new. All that happens is that you get the same next screen that you also get when following Scenarios 1 and 2 via External Data > More > SharePoint List (refer to Figure 18.4).

The Differences Between Using Access 2010 with SPF 2010 and Using Access 2007 or Access 2003

The options when using Access 2007 are similar to those when using Access 2010. Access 2007 also had the ribbon, and the SharePoint icons were in equally odd locations and had equally confusing “helpful” pop-ups.

The five scenarios that were dealt with in detail in the Access 2010 + SPF 2010 section are also available in Access 2007. The major difference is that Scenario 4 when using Access 2010 includes the synchronization of data, whereas the equivalent scenario in Access 2007 synchronizes only heading name changes but not data changes. Access 2003 does not have any functionality for linking tables to SharePoint.

Summary

In this hour, we looked at both options for creating Access 2010 databases from SPF 2010 and at all three options for creating SPF 2010 Lists from Access 2010. We also looked closely at in which cases the resultant tables or lists were linked and could thus be synchronized between products.

Q&A

Q. Why did you spend a whole hour on connecting Access 2010 and SPF 2010?

A. You need to know how to do this to create quality reports from SharePoint Lists (refer to Hours 19 and 20). The creation options in both products are rather confusing and difficult to find, so it is important to know when to use a method that links the two products and when to use a one-off transfer solution.

The names of the icons and screens and even the supposedly helpful explanatory texts provided by both products (use of the word Move, for example, when Copy is all that takes place) means that a detailed guide is necessary.

Q. If I already have an Access table, why would I want to have a copy of it in SharePoint?

A. You do it so that your data is stored in a server and accessible from different locations via a browser. The users of the data don’t need a copy of Access 2010 installed on their PC, which would otherwise be necessary.

Using a synchronized list would usually make the most sense, but a nonsynchronized SharePoint list would be a better alternative if the intention were to provide, for instance, monthly summary information.

Workshop

Quiz

1. What is the reason (in one word) why it is good to have a synchronized copy of a SharePoint list stored as a table in Access 2010?

2. Why could the creation of a SharePoint List while in Access 2010 not be a good idea?

3. Why could the creation of a SharePoint List while in Access 2010 be a good idea?

Answers

1. Reporting. This is covered in detail in the next hour.

2. Because there is a limited number of List types that can be chosen.

3. Because in one set of actions you create an Access 2010 table and a SharePoint List and also link them.