Using Windows SharePoint Services from Access


So far in this chapter you have read about how to work with lists within Windows SharePoint Services. You’ve learned how to edit data through different views, add new columns to your lists, and use the Recycle Bin to recover deleted records. Now that you are more familiar with how to work within the Windows SharePoint Services user interface, it’s time to discover how you can leverage the power of Windows SharePoint Services within Access 2007.

Exporting Data to Windows SharePoint Services

In some situations you might find that data stored in Access 2007 for your own personal use needs to be shared by several users in different locations. You might find, for example, that a list of contacts you maintain locally in an Access data table in Oregon needs to be accessed by your sales force in Paris. By exporting this table to a SharePoint site, both you and your sales force can view, add, and edit the contact information from anywhere in the world.

Access 2007 makes the process of exporting data from a table very easy. Let’s start by using the Contacts database template that comes with Access for an example. Open Access and click the Contacts database template icon in the middle of the screen. Browse to a location to save this file and name it Contacts. Click the Download button to download the database to your local computer. After Access opens the database, close the opening Contacts form so that only the Navigation Pane is visible. Click the Navigation Pane menu, click Object Type under Navigate To Category, and then click All Access Objects to display a list of all objects.

This Contacts database includes only one table-Contacts-with no records. To see how records are exported to a SharePoint site, you should add some records to this table. Open the Contacts table in Datasheet view and add a couple of records of contact information, as shown in Figure 22–21.

image from book
Figure 22–21: Enter some contact records before exporting the Contacts table to a SharePoint list.

Now that you have created some records, close the Contacts table. As you learned in Chapter 6, “Importing and Linking Data,” all import and export commands are located on the External Data tab of the Ribbon. Select the Contacts table in the Navigation Pane, and then on the External Data tab, in the Export group, click the SharePoint List button, as shown in Figure 22–22. Alternatively, you can right-click the table in the Navigation Pane, click the Export command on the shortcut menu that appears, and then click SharePoint List on the submenu.

image from book
Figure 22–22: Click the SharePoint List button to start the Export-SharePoint Site wizard.

Access opens the Export-SharePoint Site wizard, as shown in Figure 22–23. Under Specify A SharePoint Site, enter a valid address to a SharePoint site or subdirectory. Any SharePoint sites that you have previously imported from, linked to, or exported to are displayed in a list box. If one of these sites is the location to which you want to export the table, you can click that address and Access fills in the address text box below the list with that link. Enter a valid Windows SharePoint Services address in the text box below the list, or select a previously visited Windows SharePoint Services address from the list box.

image from book
Figure 22–23: The Export-SharePoint Site wizard helps you export a table to a SharePoint list.

Under Specify A Name For The New List, give this new list a name. Keep in mind that the name you use is exactly how it appears to users on the SharePoint site. If you name it tblContacts, for instance, that is exactly how the name is displayed to users. For our example, we kept the default name of Contacts that Access used. Also, if you use the same name as an existing list, Windows SharePoint Services appends a number to the end of the name to avoid duplication. For example, if a Contacts list is already present on our site, the new list is named Contactsl.

Under Description you can optionally enter some information to describe the use of this list. This description is shown on the SharePoint site next to the name of the list. For our example, we entered a description of “Contacts table for sales force in Paris,” which you can see in Figure 22–23. Select the Open The List When Finished check box if you want Access to immediately display the new list in your browser after the export is complete. The wizard also displays a message noting that any tables related to this one are also going to be exported to the SharePoint site. We’ll discuss this concept later in the chapter. Click OK to start the export process. If you are not logged on to your SharePoint site, you might be prompted to enter your logon information before continuing. During the export process, Access displays a message screen with progress indicators.

The duration of the export process depends on how many tables you are exporting, your connection speed, and the amount of data being transferred. In our simple test of this Contacts table using a high-speed connection, Access completed the task in only a few seconds. If you selected the Open The List When Finished check box in the Export-SharePoint Site wizard, your browser should open, displaying the Contacts list on the SharePoint site, as shown in Figure 22–24. You can see the name of the list, Contacts, as well as the optional description we entered in the wizard. By default, Windows SharePoint Services shows an All Items view for all lists; in this case it is called All Contacts, which shows both contact records. In our example, our sales force can now view, add, and edit these records in their Paris office using their browser.

image from book
Figure 22–24: Your new Contacts table is now displayed as a list on the SharePoint site.

When the export is complete, Access displays a confirmation message on the last page of the wizard, as shown in Figure 22–25. This page also offers you the option to save the export steps you just performed if you plan to repeat these steps on a regular basis. You can execute saved exports by clicking the Saved Exports button in the Export group on the External Data tab of the Ribbon. If the export process encounters any problems, Access displays a message on this page informing you of the errors and creates a local table of those it encounters. We’ll discuss this error table later in the chapter. Click Close to close the wizard.

image from book
Figure 22–25: Access confirms whether the table export was successful on the last page of the wizard.

Importing a List from Windows SharePoint Services

Importing a list into Access 2007 from a SharePoint site works in much the same way as exporting a table. In this case, you are downloading data from a SharePoint site and saving a local copy of the data in an Access table. After Access creates the table and imports the records, you can use all the powerful tools at your disposal in Accessqueries, forms, and reports-to analyze the data.

Continuing with our Contacts table example, let’s import the Contacts list from the SharePoint site to the Contacts database we created earlier. You already have a Contacts table in this database, so if you import the list, Access appends a number (in this case, 1) to the name of the imported table-Contacts1. You can either temporarily rename the existing Contacts table in your database to a new name, perhaps OldContacts, or just delete the table. We deleted the existing Contacts table by right-clicking the table in the Navigation Pane and clicking Delete on the shortcut menu.

Begin the import process by opening the Contacts database in Access and on the External Data tab, in the Import group, clicking the SharePoint List button, as shown in Figure 22–26.

image from book
Figure 22–26: Click the SharePoint List button to start the import process.

Access opens the first page of the Get External Data-SharePoint Site wizard, as shown in Figure 22–27. You can use this wizard to either import or link to Windows SharePoint Services lists. We’ll discuss linking in the next section. Under Specify A SharePoint Site, enter a valid address to a SharePoint site or subdirectory. Any SharePoint sites that you have previously imported from, linked to, or exported to are displayed in a list box. If one of these sites is the location from which you want to import the table, you can click that address and Access fills in the address text box below the list with that link. Enter a valid Windows SharePoint Services address in the text box, or select a previously visited Windows SharePoint Services address from the list box. Select the first option, Import The Source Data Into A New Table In The Current Database, to import the list and records to a local table and then click Next.

image from book
Figure 22–27: You can import or link to Windows SharePoint Services lists using this wizard.

The second page of the wizard displays all the lists found in the SharePoint site directory that you specified on the previous page, as shown in Figure 22–28. Select a check box in the Import column to specify which list to import to Access. The Type column displays icons representing the different types of lists. User-defined lists, for example, are shown in orange, and built-in Windows SharePoint Services lists are shown in gray. The Name column displays the names of the lists on the SharePoint site. The fourth column, Items To Import, shows a list of views. If the list has more than one view defined in Windows SharePoint Services, you can select which specific view you want to import The default view, All Contacts, is the only view defined in our example. The last column, Last Modified Date, displays the date the list was last modified.

image from book
Figure 22–28: Select which lists to import to Access on the second page of the wizard.

Near the bottom of this page is an option to import the display values from any lookup fields instead of the actual lookup field ID. If you think a list has one or more related lookup lists, and you want to fetch the linking ID instead of the lookup value, clear this check box so that you fetch the actual ID value. For example, if an Orders list is related to a Customers list, clearing this check box fetches the Customer ID instead of the customer name that might be defined in a lookup. If you leave this item selected, you’ll see the customer name imported in the Customer ID field. In this case there are no related or lookup tables for Contacts, so this option does not apply.

Select the check box for the Contacts list, leave the other options set to their defaults, and then click OK to begin the import process. Access creates a new local table in your database and then imports the records. After the import process is complete, Access displays the last page of the wizard, as shown in Figure 22–29. A message at the top of this page indicates whether the import process was a success or if any problems were encountered. The wizard also displays an option to save your import steps in case you want to perform the exact import procedure again in the future. You can execute saved imports by clicking the Saved Imports button in the Import group of the External Data tab on the Ribbon. Click Close to dismiss the wizard.

image from book
Figure 22–29: The last page of the wizard asks if you want to save the import steps.

Access now displays the new Contacts table in the Navigation Pane. Open the table in Datasheet view to confirm that the table includes the two contacts records, as shown in Figure 22–30. You can now analyze the data using queries and reports or build data entry forms for adding records to the table or editing them. Note, however, that you’ve made a copy of the data stored on the SharePoint site. Any changes you make to the local copy won’t be reflected in the Web site list. If you want to be able update the data in the list directly from Access, read on to the next section.

image from book
Figure 22–30: The Contacts list from the SharePoint site has now been imported as a local table into Access.

Troubleshooting

image from book

Why doesn’t my imported Windows SharePoint Services list include all the records?

Earlier in this chapter, you learned how to create different views of a list in Windows SharePoint Services. You can define filters, include only certain columns, and assign sort orders to a custom view. If your custom view restricts the number of records returned, Access follows those rules and imports only those specific records. So, for example, if you define a custom view that shows only contacts whose last name equals Viescas and then import that view into Access, the only records imported are ones where the last name equals Viescas. If you need to import all the records into Access, make sure you import a view that returns all the records in the list.

image from book

Linking a Windows SharePoint Services List into Access

As you might recall from Chapter 6, we discussed the differences between deciding to import from or link to another data source. If you need to share your data with other users or if the data changes frequently, you should consider linking to instead of importing from another data source. You just imported a Contacts list from a SharePoint site to an Access database. If you add new records, edit existing records, or delete records in this table, these changes are not reflected in the list on the SharePoint site. This can be problematic if all users need to have the most up-to-date data available to them. You could make changes to your local table and then export the table to the SharePoint list, but what if another user had also made changes to the records in the a list? You can see the dilemma this causes when trying to keep accurate data.

Fortunately, with Access you can link to a SharePoint site just as you can to other data sources. If you export an Access table to Windows SharePoint Services and then link it back, this allows both your desktop application users and authorized members of your Windows SharePoint Services team to work with and update the same data. To link to a SharePoint list from Access, click the SharePoint List button in the Import group on the External Data tab, as shown in Figure 22–31.

image from book
Figure 22–31: Click the SharePoint List button to start the Get External Data-SharePoint Site wizard.

Access opens the first page of the Get External Data-SharePoint Site wizard, shown in Figure 22–32. This particular wizard is the same one you used for importing lists from a SharePoint site in the previous section. Enter a valid Windows SharePoint Services address in the address text box below the list of previously visited sites or select a previously visited Windows SharePoint Services address from the list box. Select the second option, Link To The Data Source By Creating A Linked Table, to link to an existing list on a SharePoint site and then click Next.

image from book
Figure 22–32: Select the link option on the first page of the wizard to link to a list.

The second page of the wizard displays all the lists found in the SharePoint site directory that you specified on the previous page, as shown in Figure 22–33. Select a check box in the Link column to specify which list you want to link to Access. The Type column displays icons representing the type of list. User-defined lists, for example, are shown in orange when clicked, and built-in Windows SharePoint Services lists are shown in gray when clicked or not clicked. The Name column displays the names of the lists on the SharePoint site. The last column, Last Modified Date, displays the date the list was last modified. Select the Link check box next to Contacts and then click OK to start the link process.

image from book
Figure 22–33: Select the list you want to link to on this wizard page.

Note 

You’ll notice in Figure 22–33 that you cannot select any views on a SharePoint site as you can when you are importing a list. Access allows you to link only to the full list as opposed to views created from lists.

image from book Access creates a link to the Windows SharePoint Services Contacts list and marks the icon for linked Windows SharePoint Services tables in the Navigation Pane with a blue arrow, as shown in Figure 22–34. If Access finds a duplicate name, it generates a new name by adding a unique integer to the end of the name as described earlier. Because objects such as forms, reports, macros, and modules might refer to the linked table by its original name, you should carefully check name references if Access has to rename a linked table.

image from book
Figure 22–34: Access now has a link to the Contacts list on the SharePoint site.

On the status bar at the bottom of the Access window shown in Figure 22–34, you’ll notice that Access displays Online With SharePoint. This message appears on the status bar whenever you have any active links to a SharePoint site.

Inside Out-Do Not Delete the User Information List Link! 

You might have noticed that Access created a link to the User Information List in addition to the Contacts list, even though you did not explicitly link to that list. Access adds links to other related lists such as those used for lookup values. Every list in Windows SharePoint Services includes hidden columns for Created By and Modified By dates, and this data is stored in the User Information List in Windows SharePoint Services. If you delete this link, Access displays error messages, like this one, whenever you attempt to update records in the list.

image from book

You can now use this Contacts list just like the original table in this application. In Figure 22–35, you can see that we opened the Contact List form in Access bound to the linked table, and both contact records are there. If you add a new record to the Contacts table using this form, the Contacts list on the SharePoint site is also updated. Note that using data from a SharePoint list as a linked table in Access requires a high-speed Internet connection or local area connection to your intranet server. Performance will be poor over a dial-up connection.

image from book
Figure 22–35: You can update a linked SharePoint list just like local tables or tables linked to other data sources.

Also note that Windows SharePoint Services doesn’t enforce referential integrity. If you use Windows SharePoint Services lists as the tables in your application, your application must perform additional checks to ensure that data integrity is maintained. For example, you might have a Contact Events list that includes the Contact ID field from the Contacts list. Before allowing a user to delete a Contacts record, you should check to see that no related records exist in the Contact Events list. If records exist, you can either delete them or disallow deleting the Contacts records. You’ll learn more about working with recordsets in code behind forms in Chapter 20, “Automating Your Application with Visual Basic.”

Using SharePoint List Options with Linked Lists

When you have a linked list, Access 2007 offers several options for interacting directly with the SharePoint site interface through a shortcut menu. If you’ve been following along to this point, you should still have an active link to the Windows SharePoint Services Contacts list. Close the Contact List form if it’s still open, and then right-click the Contacts table link in the Navigation Pane, as shown in Figure 22–36.

image from book
Figure 22–36: You can interact directly with the Windows SharePoint Services interface from within Access by using commands on the shortcut menu of a linked list.

You can do any of the following from the SharePoint List Options submenu:

  • Open Default View   This option sends a command to the SharePoint site and opens the default view for the list. In our example, Windows SharePoint Services displays the All Contacts view.

  • Modify Columns And Settings   This option opens a page where you can modify the design of the list. Similarly to modifying a table in an Access database, you can rename columns, change data types, change the sequence of columns, delete columns, or add new columns.

  • Alert Me   This option opens a page on the SharePoint site where you can set options to be notified via e-mail if any data is added to this list, if data is modified, or even if data is deleted.

  • Modify Workflow   This option takes you to the Workflow page for this list where you can modify existing workflow rules or add new workflow rules. You can use workflow rules to attach business logic to items in a SharePoint, similarly to setting validation rules in an Access table.

  • Change Permissions For This List   This option opens a page on the SharePoint site where you can view and change the users and their permissions levels.

  • SharePoint Site Recycle Bin   This option opens the Recycle Bin page on the SharePoint site where you can restore items (lists, views, rows, and so on) to your site or permanently delete them. Windows SharePoint Services stores elements in the Recycle Bin for 30 days and automatically purges anything left in the Recycle Bin longer than that.

  • Relink Lists   This option opens the Relink Lists To New Site dialog box so that you can relink your lists to a new SharePoint site location. This dialog box, shown here, works in much the same way as the Linked Table Manager for relinking tables when the data source location has changed. You might need to use this facility if you have moved your lists to a new Web site.

    image from book

  • Refresh List   This option causes Access to refresh the list from the SharePoint site.

  • Delete List   This option deletes the selected list from the SharePoint site. Access displays a message asking you to confirm the deletion. If you accidentally delete a list by mistake, you can recover it in the Recycle Bin.

Note 

When you open a database that has tables linked to Windows SharePoint Services lists and if you’re not currently logged on to your SharePoint site, you’ll see a standard Web site logon dialog box where you must enter your user name and password and click OK.

Creating a New Windows SharePoint Services List from Within Access

Access 2007 also includes a powerful new feature that lets you dynamically create new lists on a Windows SharePoint Services site. With only one click on a Ribbon button and a few wizard options to set, you’re essentially creating a new table as a list directly in Windows SharePoint Services. We’ll continue using our Contacts database and our existing SharePoint site to demonstrate this functionality. If you have any objects open in this database, close them so that you see only the Navigation Pane. On the Create tab, in the Tables group, click the SharePoint Lists button. Access displays a menu of six options, as shown in Figure 22–37.

image from book
Figure 22–37: You can create new lists on a SharePoint site from within Access.

Using an Existing List Template

The first four options-Contacts, Tasks, Issues, and Events-create a new list on the SharePoint site and a table in Access that links to that list using a built-in template. These first four options are commonly used list styles on a SharePoint site. The Custom option, discussed in the next section, creates a new list on the SharePoint site with only three visible columns-ID, Title, and Attachments. The Existing SharePoint List option opens the Get External Data-SharePoint Site wizard to import or link to a Windows SharePoint Services list.

Let’s step through creating a new Events list on the SharePoint site so that we can coordinate vacation scheduling with our sales force in Paris. Click the Events option on the SharePoint Lists menu, as shown in Figure 22–37. Access opens the Create New List wizard, shown in Figure 22–38. Under Specify A SharePoint Site, enter a valid address to a SharePoint site or subdirectory on that site. Enter a valid Windows SharePoint Services address in the text box or select a previously visited Windows SharePoint Services address from the list box.

image from book
Figure 22–38: To create a new list, specify a name for the list and the location of the SharePoint site.

Under Specify A Name For This List, give this new list a name. Keep in mind that the name you use is exactly how it appears to users on the SharePoint site. We decided to name our new list Vacation Calendar. Under Description you can optionally enter some information to describe the use of this list. We entered a description of “Sales force vacation calendar,” which you can see in Figure 22–38. This description appears on the SharePoint site next to the name of the list. Leave the Open The List When Finished check box selected so that Access immediately displays the new table in Datasheet view after you create the list. Click OK to create the new list.

Access sends commands to the SharePoint site to create the new list and then creates a new table linked to the Vacation Calendar list. Access also opens the new table in Datasheet view, as shown in Figure 22–39. This new table has 21 fields, such as Location, Start Time, End Time, and Description, which you can use to describe an event.

image from book
Figure 22–39: Access created a new list on a SharePoint site and a table linked to the list.

If you use Internet Explorer to navigate to the address you specified for the list, you should now see the new Vacation Calendar list on the SharePoint site. This type of list is perfectly suited for display in a Calendar view. As you can see in Figure 22–40, the default view for the Vacation Calendar list is Calendar. The Vacation Calendar list also has two other views-All Events and Current Events-that display the records in a more traditional Datasheet view. At the moment, the Calendar view is empty because no records have been created in the list.

image from book
Figure 22–40: By default, the new Vacation Calendar list is displayed in Calendar view.

Switch back to Access, and let’s add a new record to the table to see how easily we can interact with the Calendar view on the SharePoint site. Our sales manager, John, is going to be on vacation from October 30 through November 3. You can enter this data directly into the table’s Datasheet view in Access, or you could build a data entry form. As you learned in Part 3 of this book, you have a lot more control over how data is entered if you use a form instead of entering data directly into a table datasheet, but for this simple exercise we entered John’s vacation schedule into the table’s Datasheet view, as shown in Figure 22–41. (If you’re following along with this example, select a start and end time within the current month.)

image from book
Figure 22–41: Enter a vacation record into the table to see it displayed on the SharePoint site.

After you move from the record or save it by clicking the Save button in the Records group on the Home tab, Access saves the information on the Windows SharePoint Services site. Go to the Windows SharePoint Services site using your browser, click the Refresh button, and navigate to the appropriate month and year to see the changes reflected in the Calendar view, as shown in Figure 22–42. John’s vacation schedule can now be seen by any users of our Windows SharePoint Services site.

image from book
Figure 22–42: John’s vacation schedule now appears as a block of time in the Calendar view.

If you click on the block of time in the Calendar view, Windows SharePoint Services displays a single-list view showing the details of John’s record that we entered in Access, as shown in Figure 22–43.

image from book
Figure 22–43: The single-list view shows the details of John’s schedule.

Creating a Custom List

You can also create a new custom list on a SharePoint site through Access 2007. When you choose this option, Windows SharePoint Services does not use one of its built-in templates for the list structure. In Access, if you have any database objects open, close them now so that only the Navigation Pane is visible. On the Create tab, click the SharePoint Lists button in the Tables group, as shown in Figure 22–44.

image from book
Figure 22–44: Click the Custom option to create a custom list on the SharePoint site.

Next, click the Custom option. Access opens the Create New List wizard, as shown in Figure 22–45. Enter a valid Windows SharePoint Services address in the text box or select a previously visited Windows SharePoint Services address from the list box. Under Specify A Name For This List, we entered Chapter Status. In the Description box we entered “Status of chapters for Microsoft Press,” as you can see in Figure 22–45. Leave the Open The List When Finished check box selected and then click OK to create the new list.

image from book
Figure 22–45: Enter a name for the custom list and the location of the SharePoint site.

Access instructs Windows SharePoint Services to create a new default list, creates a table linked to the list, and opens the new list in Datasheet view, as shown in

Figure 22–46. A Windows SharePoint Services custom list by default contains only ID, Title, and Attachments columns.

image from book
Figure 22–46: By default, the new list includes three columns.

If you go to the SharePoint site in your browser, you can see the new custom list, as shown in Figure 22–47. The only view created so far is the All Items view, and the list is empty. Windows SharePoint Services displays the description of the list that we entered in the wizard beneath the list name. In this view, the Attachment column is to the left of the Title column, and the ID column is hidden.

image from book
Figure 22–47: The new custom list shows up in the browser window.

Go back to Access, and let’s take a closer look at the table that Windows SharePoint Services created. Right-click the Chapter Status linked table in the Navigation Pane and click Design View on the shortcut menu. Access displays a warning message that Chapter Status is a linked table and that you cannot change any of its properties. Click Yes in the message box to open the table in Design view, as shown in Figure 22–48.

image from book
Figure 22–48: Access displays all the hidden columns of the list in the table’s Design view.

You’ll immediately notice that instead of seeing only three fields as you did in Datasheet view, Access displays 14 fields in Design view. These extra fields are hidden columns that Windows SharePoint Services uses to maintain the list. You can also see in Figure 22–48 a new group on the Design contextual tab under Table Tools called SharePoint List. Access displays this group only when you are working with tables that are linked to Windows SharePoint Services lists. In the SharePoint List group, you can do the following:

  • Open Default View   This option sends a command to the Windows SharePoint Services browser and opens the default view for that list.

  • Refresh List   This option causes Access to refresh the list from the SharePoint site. If any column properties have changed, Access closes the table.

  • Modify Columns And Settings   This option opens the SharePoint site to a page where you can modify the design of the list. Similar to modifying a table in an Access database, you can rename columns, change data types, change the sequence of columns, delete columns, or add new columns. Just as with other linked tables, to make changes to the source table, you must make them in the source data store, in this case on the SharePoint site.

  • Alert Me   This option opens a page on the SharePoint site where you can set options to be notified via e-mail if any data is added to this list, if data is modified, or even if data is deleted.

  • Modify Workflow   This option takes you to the Workflow page for this list where you can modify existing workflow rules or add new workflow rules.

  • Permissions   This option opens a page on the SharePoint site where you can view and change the users and their permission levels.

After you create a custom list, you can use these commands to modify your list according to your specific needs. Close the Design view for this table now.




Microsoft Office Access 2007 Inside Out
MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)
ISBN: 0735623252
EAN: 2147483647
Year: 2007
Pages: 234

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net