MicrosoftВ® Office Access(TM) 2007 Inside Out (Microsoft Office Access Inside Out)

In the process of writing this book, we used an Office Live Web site running Windows SharePoint Services Version 3 to test and demonstrate how Access 2007 and Windows SharePoint Services can work together. A full discussion of Windows SharePoint Services Version 3 and all its features is beyond the scope of this book, so our goal in this section is only to familiarize you with some of the user interface elements relevant to Access 2007. Figure 22–1 shows the main page we created to demonstrate the Housing SharePoint sample you’ll see later in this chapter.

Figure 22–1: A Windows SharePoint Services Web site allows you to collaborate and share information through a Web browser.

The left side of the page is a navigation bar similar to the Navigation Pane in Access 2007. Clicking one of these links takes you to another part of this Web site. In our example, we clicked the Housing link to access the HousingSP database. Horizontally near the top of the page is a dashboard that displays the internal contents of the Housing folder using tabs. You can see the name of the Housing SharePoint database (HousingSP) and some of the lists within that database-tblDepartments, tblEmployees, and tblReservationRequests.

Click the Common Tasks button to see a list of customization options, as shown in Figure 22–2. You can customize the dashboard, assign permissions to various parts of your Web site, view settings for your other applications and workspaces, create new elements on your Web site, and modify the current workspace settings.

Figure 22–2: Click Common Tasks to create, edit, and customize the various elements of your Windows SharePoint Services Web site.

Editing Data in Lists

In Windows SharePoint Services terminology, a table is referred to as a list that stores information about a single subject. In a list you have columns (fields) that contain the different kinds of information about the subject. Similar to how you work in Access 2007, you can work with lists in different views for adding and editing records. Figure 22–3 shows the default view of an Employees list on our test site that looks like Datasheet view in Access. (This list was created as part of one of the sample business applications that Office Live loaded when we set up the Web site.) You can see the column headers-such as Last Name, First Name, and Job Title-and one employee row below the column headers. This view is also set to display all the employees.

Figure 22–3: A list displayed on a SharePoint site resembles Datasheet view of a table in Access.

Although this view resembles a datasheet, you cannot edit any of the columns or records from this specific page because this is a read-only view. In the Windows SharePoint Services user interface, you’ll notice that there are no record indicators or blank rows for new records. To add a record to the Employees list, click the arrow to the right of the New button and then click the New Item command, as shown in Figure 22–4.

Figure 22–4: Click the New Item command to add a new record.

Windows SharePoint Services opens a new page that resembles a blank data entry form in Access, as shown in Figure 22–5. All the column headers for this list are displayed on the left side, and text boxes for the columns are on the right side. (You can’t see all the columns for the Employees list in Figure 22–5.) This single-item edit form displays a red asterisk next to any required columns-for this list, you must enter a value in Last Name.

Figure 22–5: You can add new records via a view of the list that looks like a form.

As with a data entry form in Access, you enter the information for this record into the various text boxes. In Figure 22–6 you can see we are entering John’s employee information. Just like Access 2007, Windows SharePoint Services supports adding attachments to individual records. In this particular view, we can click the Attach File link, shown in Figure 22–6, to browse to a location and upload an attachment for this employee’s record.

Figure 22–6: You can see John’s record details being added to the form.

Windows SharePoint Services also supports Rich Text Format for text and memo fields. The last column in the Employees list, Notes, includes formatting buttons you can use to apply different fonts, font sizes, bolding, alignments, and colors for your text, as shown in Figure 22–7. After we click OK to save this new record, we return to the default view of the Employees list, which now displays John’s record, as shown in Figure 22–8.

Figure 22–7: Windows SharePoint Services supports Rich Text Format for text fields.

Figure 22–8: John’s record has been added to the Employees list.

Creating New Views

Within the Windows SharePoint Services user interface, you can create new views of your lists. If a particular view shows too many columns or too few columns, you can create a custom view to display only the columns you need. You might find this feature handy for hiding certain columns from specific users or groups. You might also want to set up a Datasheet view of your list for quick data entry and editing many records. On our test site we created a Datasheet view of the Employees list. As shown in Figure 22–9, we clicked the arrow to the right of View and then clicked the Create View command to begin creating a new Datasheet view.

Figure 22–9: You can create new views of your lists within Windows SharePoint Services.

The Create View page opens, where you can choose from several existing view formats, as shown in Figure 22–10. This page includes built-in views such as Standard, Calendar, Access, Datasheet, and Gantt. You can even use an existing view as a basis for creating a new view. Not all views would be appropriate for certain types of lists. For instance, a list of products would not function at all if displayed in Calendar view.

Figure 22–10: Windows SharePoint Services offers several built-in views for your lists.

We want to create a Datasheet view of the Employees list, so we clicked the Datasheet View button to open a new page to customize our new view, as shown in Figure 22–11. On this page we can choose from any of the following options for this new list view:

• Name   Assign a name for this new list view.

• Audience   Designate whether this view can be seen by others or only by yourself. Public is the default.

• Columns   Select which columns will appear in the list view and in what order. All data columns appear in the sequence defined by default. You can also choose to display other columns maintained by Windows SharePoint Services such as attachments, date created, date modified, and version.

• Sort   Assign sorting options for one or two columns. By default, no columns are sorted-the data appears in the order entered.

• Filter   Apply one or more filters to the list view. By default, no filters are defined.

• Totals   Calculate totals for any of the columns. No totals are defined by default.

• Folders   Choose to show items inside folders or in a flat view without folders. The default is to display items inside folders.

• Item Limit   Assign a maximum number of records to display in the view. The default is to display all records.

Figure 22–11: On this page, you can choose options to customize your new Datasheet view.

In our example, we named this new view Employee Datasheet and kept all of the other options set to their defaults. In the Access world, what we are building is conceptually a query and a data entry form for the Employees list. For the query, we are deciding which fields to display and in which order to sort the fields. For the form, we are choosing the form layout (Datasheet, in this case), and specifying any totals to display for the columns. After clicking OK to save our new view, Windows SharePoint Services displays our Employee Datasheet list view, as shown in Figure 22–12.

Figure 22–12: You can now see your new Datasheet view of the Employees list.

This view now looks very similar to Datasheet view of a form you might create in Access. You can easily move through the columns and records using the Tab and arrows keys. You can click the arrows on the column headers to apply additional filters to the records. You can see a blank row at the bottom of this view for entering a new record. Windows SharePoint Services even displays an Access icon in the upper-left corner of the view!

Adding Columns to Lists

In the Windows SharePoint Services user interface you can also add new columns to your lists. For our example, we might want to add a Middle Name column between the Last Name and First Name columns and reorder the columns to First Name, Middle Name, and Last Name. This is a three-step process-add the column to the list, add the column to the Employee Datasheet view, and then reorder the columns. To add the new column to the Employees list, we clicked the arrow to the right of Settings and then clicked the Create Column command, as shown in Figure 22–13.

Figure 22–13: The Create Column command adds a new column to the list.

The Create Column page opens, on which you can assign properties for the new column, as shown in Figure 22–14. In Windows SharePoint Services, you assign the following properties for the new column:

• Name and Type

• Description

• Required

• Field Size

• Default Value

• Add To Default View

Figure 22–14: On this page you can set properties for the new column.

Depending on the data type you choose, this page displays additional options, such as the list of values for a Choice data type or the minimum and maximum values for a Number or Currency data type. After you assign your column properties, click OK and Windows SharePoint Services adds the new column to the list.

Now that we have added the column to the list, we must add the column to our Employee Datasheet view. (However, if the Datasheet view is the default view-which it isn’t in this case-and we selected the Add To Default View check box to add the column to the default view when we created it, we can skip these steps.) We have already indicated which columns to display in the Employee Datasheet view so, by default, Windows SharePoint Services does not add this column to our custom view. To add the column to the custom view, we clicked the arrow to the right of View and then clicked the Modify This View command, as shown in Figure 22–15.

Figure 22–15: To display the new Middle Name column, you have to add it to the Employee Datasheet view.

On the Edit Datasheet View page, you can modify the layout of the Employee Datasheet view, as shown in Figure 22–16. This page displays the name of each column with a check box to its left. Select the check box next to Middle Name to add this column to your custom view. A Position From Left box to the right of the column name lets you position each column in the view. In Figure 22–16, you can see we assigned the First Name column to the 1 position to have it appear first in the column order. When we changed the Position From Left value for the First Name column, the sequence of the other columns changed automatically. We then changed the position of the Middle Name column to 2. Windows SharePoint Services changed the sequence the other columns and assigned the Last Name column to the 3 position.

Figure 22–16: You can adjust the display positions for the columns on the Edit Datasheet View page

Note that you can change the name of the view on this page. You can also specify the Web page address, which by default will be the same as the name of the view. Finally, you can select the Make This The Default View check box to make this the view users see whenever they open the list. After you click OK to save the changes, you can see your revised Employee Datasheet view with the new Middle Name column, as shown in Figure 22–17. Windows SharePoint Services correctly positions the new column between the First Name and Last Name columns and places the First Name column first in the column order.

Figure 22–17: The new Middle Name column now appears in the Employee Datasheet view.

Recycle Bin

One of the great advantages to having lists stored on a Windows SharePoint Services site is the Recycle Bin. In most cases, if you delete something in Access, it is gone for good. If you delete one record (or a thousand) by mistake and then close the database, those records are lost. In some situations it is possible to retrieve deleted database elements using professional recovery services, but retrieval is not always guaranteed and these services can be costly. If you’re lucky, you might have a backup of your database that you can use to restore deleted records, but you still might lose some very important data depending on when you made the last backup.

Windows SharePoint Services has a built-in Recycle Bin where you can easily recover deleted records and other Windows SharePoint Services Web site elements. Suppose, for example, we accidentally deleted John’s record in our Employees list that we have been working on. We can go to the Recycle Bin on a SharePoint site by navigating to the site settings (click Common Tasks and then Modify This Application Or Workspace) and clicking Deleted Items under Recycle And Restore, as shown in Figure 22–18.

Figure 22–18: Windows SharePoint Services includes a Recycle Bin so you can recover deleted items.

After you click Deleted Items, Windows SharePoint Services displays the Recycle Bin for the Employee Directory, as shown in Figure 22–19. The Recycle Bin shows the type of object deleted (in this case a record), the name of the deleted record, the original location of the list from which the record was deleted, who created the record, when the record was deleted, and the size of the record.

Figure 22–19: John’s record can be restored from the Recycle Bin.

The Windows SharePoint Services Recycle Bin works in much the same way as the Windows Recycle Bin. The one difference is that Windows SharePoint Services automatically empties deleted items that were deleted longer ago than 30 days. To restore John’s record, we selected the check box next to his record and then clicked the Restore Selection button. Windows SharePoint Services restored John’s record to the correct list, as shown in Figure 22–20.

Figure 22–20: John’s record has now been completely restored.