Chapter 10. Reporting and web applications using Access – SharePoint 2010 Site Owner's Manual: Flexible Collaboration without Programming

Chapter 10. Reporting and web applications using Access

 

This chapter covers

  • Access features and how they can be used with SharePoint
  • Linked and local tables
  • Queries
  • Reports
  • Forms
  • Access Services

 

In this chapter you’re going to complete a scenario where you’ll capture equipment/hardware requests by clients. I chose this scenario because it’s a common process that you’ll find in IT teams. Even more often I’ve seen hosting providers capture such requests to keep track of their servers. But this process can be used in any other scenario where you need to create a reservation-type system to prevent communal resources from being double booked and to keep individual users from checking out more items than they should. Another example where this could be used, apart from the IT-related scenario we have here, is for reserving a book from a communal library. Keep using your imagination; the techniques you’ll learn here can be used for many other scenarios.

In this chapter we’re going to walk through two options for capturing the information and reporting on it. The first approach, implemented with SharePoint Foundation, will work as long as your users have SharePoint Foundation and Access. The second approach will require SharePoint Server, and because you’re going to use Access Services, the advantage is that it doesn’t require your end users to have Access. Let’s start by talking about the scenario you’ve been given and how you’ll attain the solution utilizing SharePoint.

10.1. Managing hardware reservations

This section consists of three parts: situation, business priorities, and solution. The first part, situation, provides a detailed explanation of the request that you’ve received. The next part, business priorities, will extract a list of requirements based on priority to accomplish your goals. The third section will give you an overview of the solution that we’ll spend the rest of the chapter walking through and building. By completing this you’ll learn how to build dynamic reports and web-based systems hosted on SharePoint, using Access. Many of you are probably already familiar with Access and will be excited to learn that SharePoint 2010, unlike SharePoint 2007, directly integrates with Access and allows you to host the database on a SharePoint site. We’ll dive into that technology and ensure that not only will you learn some of the basics of Access, but you’ll also know how to use the integration capabilities with SharePoint.

10.1.1. Situation

In this scenario you’re part of an IT team that manages hardware that different clients can use. For example, you have rental laptops, projectors, and even servers. You need to find a way for the IT team members to capture approved requests and to report on what items are reserved. Reporting back on that data is important. Gathering data is great, but you really can’t benefit from the data until you slice and dice it using reports to extract and visually display the valuable information that was gathered. Now that you understand the situation, let’s discuss the business priorities.

Business priorities

I’ll now define the business priorities so you can put together an appropriate solution:

  1. Enable the IT team to manage information in relation to their hardware and clients.
  2. Track approved requests with the hardware and client information.
  3. Make this information dynamically available so the IT team can quickly track current reservations by hardware type and client.
  4. Advanced requests: Publish this same information dynamically for the clients who don’t have Access.

I’ve also provided a process map to help you understand the system you’re going to implement. This is shown in figure 10.1.

Figure 10.1. Process map for the solution that you’re going to build. This explains the roles of the IT team and the client. It will help you to visualize what the requirements are and how the users will flow through the system.

The next section discusses the solution we’re going to build in this chapter.

10.1.2. Solution

Once you complete the steps in this chapter, you’ll have a site that meets the situation and business priorities specified to you. For this solution you’ll create a navigational form view that contains the reports shown in the tabs at the top of the form. To manage the input of the information, you’ll use the links on the right side of the form. The solution is displayed in figure 10.2.

Figure 10.2. Navigation form that displays the different reports at the top of the form. Links to manage the input of the data are displayed on the right side of the form.

A separate page will track the information the IT team enters, including the hardware, clients, and reservations. An example of managing reservations is shown in figure 10.3.

Figure 10.3. This reservation form captures the start and end dates for hardware reservations that are approved for the clients.

Building out this solution should take less than an hour. You’ll be impressed to find how easy it is to now dynamically generate reports on information stored in SharePoint or to allow your end users to work with data in Access though a web application published to your Share-Point site. Now that you understand the solution and its benefits, let’s get started building it out.

10.2. Leveraging Access with SharePoint Foundation

This section covers our approach for implementing this scenario using SharePoint Foundation and Access. You’ll start by creating a blank site in this scenario and potentially a publishing site if you have SharePoint Server. We’ll then cover some of the core components of Access such as tables, queries, and reports. The data that you’ll be using is pulled from a SharePoint list and managed through a SharePoint site, but the reports will be generated using Access. I’m excited, so let’s get started.

10.2.1. Creating your Foundation site

To begin you’ll want to make sure you create a site using a Blank or Publishing Site template. Enter the following for Title, Description, and Web Site Address:

Property

Value

Title Hardware Reservations
Description This site enables IT to manage hardware reservations.
Web Site Address HardwareReservations

Now that you’ve created your foundation site, you’ll set up a custom list to manage the clients, hardware, and reservations. In the Share-Point Foundation section of this chapter, you’ll do this utilizing Share-Point lists. In the SharePoint Server scenario, you’ll take a different approach, which uses Access tables and publishes them via Access Services to a SharePoint site. This will allow you to meet your advanced requirement, which enables end users without Access to dynamically work with the data.

10.2.2. Custom list: creating a data store to manage clients, hardware, and reservations

You want to create a SharePoint list to manage your clients. You need this list so you can track important information about them such as how to contact them in case you need to follow up with them on their hardware reservation. Luckily, SharePoint has an out-of-the-box list that has already been configured to help you maintain your contacts. You can reuse this list to manage your clients by following the two easy steps listed here:

Step

Action

Result/Notes

1 Choose Site Actions > More Options, choose Filter By: List, and select Contacts List.
For the Name enter Clients.
Select More Options, and for the Description enter The following list stores information about our current clients.
Click Yes for displaying this list in the Quick Launch bar.
 
2 Click Create. This will redirect you to the main page of your newly created list.

Now that you’ve created this list, you need to create a custom list that will store the information pertaining to the hardware that can be reserved. If there isn’t an out-of-the-box list like Contacts, it’s easiest to start with a custom list, which has only one column, Title. Essentially you’re starting out with a blank list, and you can easily add in your own custom columns to track information. In this scenario you want to track information about the type of hardware you’re renting out and the ID for that hardware:

Step

Action

Result/Notes

1 Choose Site Actions > More Options, choose Filter By: Blank & Custom, and select Custom List.
For the name enter Hardware.
Select More Options, and for the Description enter The following list stores information about our current hardware available for reservations.
Click Yes for displaying this list in the Quick Launch bar.
 
2 Click Create. This will redirect you to the main page of your newly created list.

 

Implementing with content types

If you want to make the list more robust, you could create content types associated with each hardware type. This would allow you to have unique columns associated with that hardware type. For instance, if you’re entering a server, you may want to capture information about it such as processing power and/or RAM, and this may not be valid information to capture for a projector. Creating content types is covered in more detail in chapter 5.

 

Because this is a custom list, you need to update the columns in this list to capture the appropriate information regarding your hardware, such as an ID and the type of hardware. For simplicity, you’re going to implement a basic list that captures just those two items. If you want to capture additional information, I recommend that you read the short sidebar so you can get additional ideas to further enhance this list:

Step

Action

Result/Notes

1 Choose List Settings.  
2 Select Advanced Settings. This is located under the General Settings grouping.
3 Click the radio button to disable attachments to list items. This will remove the paper clip icon.
4 Click OK.  
5 Select Title, which is displayed in the Columns section.
6 In the Column Name field enter Hardware ID and click OK.
Check Enforce Unique Values.
You’ll now have a required field for the project. Next you’ll create the Hardware Type column.
7 In the Columns section select Create Column.
For Column Name enter Hardware Type.
Choose Choice.
For each choice enter the following options: Server, Printer, Projector, Phone, and Laptop.
Click OK.
 

Finally, you’ll create a list that manages the reservations and the associated client information and hardware information from the lists you’ve already created. When entering data regarding a reservation, you’ll want to track the contact info for the client and the hardware info for the reservation. You won’t want to track this information in multiple places, so you’ll create a lookup field that can link the reservation to the client and hardware ID. Later on when you generate your reports, you’ll combine the information from these three tables to provide the full details about the reservation in one place:

Step

Action

Result/Notes

1 Choose Site Actions > More Options, choose Filter By: Blank & Custom, and select Custom List.
For the name enter Reservations.
Select More Options.
Click Yes for displaying this list in the Quick Launch bar.
 
2 Click Create. This will redirect you to the main page of your newly created list.

Similar to the previously created custom list, you need to update the columns in this list to capture the appropriate information regarding your reservations, such as client and hardware lookups and a start date and end date for the reservation:

Step

Action

Result/Notes

1 Choose List Settings.  
2 Select Advanced Settings. This is located under the General Settings grouping.
3 Click the radio button to disable attachments to list items. This will remove the paper clip icon.
4 Click OK.  
5 Select Title, which is displayed in the Columns section.
6 In the Column Name field enter Description and click OK. You’ll now have a required field to describe the reservation. Next you’ll create the Lookup columns.
7 In the Columns section select Create Column.
For Column Name enter Client.
Choose Lookup.
Make it a required field.
For the Get Information From drop-down list select Client.
For the In This Column drop-down select Full Name.
Click OK.
 
8 In the Columns section select Create Column.
For Column Name enter Hardware.
Choose Lookup.
Make it a required field.
For the Get Information From drop-down select Hardware.
For the In This Column drop-down select Title.
Click OK.
 
9 For Column Name enter Start Date.
Choose Date and Time.
Make it a required field.
For Date and Time Format select Date Only.
Click OK.
 
10 In the Columns section select Create Column.
For Column Name enter End Date.
Choose Date and Time.
Make it a required field.
For Date and Time Format select Date Only.
Click OK.
 

Good job! Your end users now have a way to track all the vital information to get this application going. Now that you’ve built that out, we’ll jump from SharePoint to Access to show how you can pull in this data and dynamically report on the content, getting up-to-date reports as multiple end users update the tables you just created.

10.2.3. Linked tables: pulling the data into Access

In the previous section you created several tables that you can work with in SharePoint to manage your information. You now want to pull that data into Access so you can generate your reports. To do this you’ll use linked tables. There are two types of tables in Access that we cover in this chapter: local and linked. To learn more about the local tables, read the following note or skip to the implementation with SharePoint Server.

 

Note

In the next section, associated with SharePoint Server, we’ll cover a different approach to this architecture. For the implementation with SharePoint Server you’ll use local tables instead of linked tables and maintain the information using web forms. This can also be done with SharePoint Foundation. Linked tables have many advantages over local tables, but they can’t be used with the SharePoint Server’s Access Services.

 

A linked table can dynamically share data between Access and Share-Point. This allows you to pull in the data from SharePoint so you can generate real-time reports. Let’s walk through the steps to create your linked table. These steps will need to be completed in Access 2010, not SharePoint. So go ahead and open Access, but make sure you have a connection to your SharePoint environment:

Step

Action

Result/Notes

1 Open Access 2010.  
2 Select the External Data tab.
3 Select More in the Import & Link section. This is next to the ODBC Database option.
4 Select SharePoint List. A dialog box will appear asking you to specify how you want to store the data in the current database. Make sure the radio button Link to the Data Source by Creating a Linked Table is selected and the URL for your hardware reservations site is entered.
5 Click Next. You may be prompted for credentials.
6 Click the check boxes for the Clients, Hardware, and Reservations lists and click OK.

You now have the data in a format that Access understands, and you didn’t have to re-create the tables in Access. You just had to follow some simple steps to pull in the data. This is useful if you need to start reporting on data that’s already been stored in various SharePoint sites. A common example of this would be user information stored in a Contacts list across projects. If you need to consolidate that information, one easy approach would be to pull in the information using this method and create a query, which creates a union between the tables. This would prevent you from having to maintain the data in multiple places but still pull it into a central location to report on. In the next section you’re going to create a query so you can pull together the data from the different tables and use it to report useful information to the end users.

10.2.4. Client query: integrating the data in the different tables

You now have access to the data using your database, but you need to query across the tables so you can later generate a report containing information about the client, hardware, and reservation that was made.

For the first report you want to show the hardware that’s reserved grouped by type. The fields that you want to display are Hardware Type, Hardware ID, Client Name, Email, Phone, Reservation Start Date, and End Date. To do this you’ll need to pull information across all three tables:

Step

Action

Result/Notes

1 In Access 2010 select the Create tab.  
2 Select Query Wizard in the Client Queries drop-down list.
3 A dialog box will appear; select Simple Query Wizard and click OK.  
4 In the Tables/Queries section, select Table: Hardware from the drop-down list, and move Hardware Type and Hardware ID to the Selected Fields column.
5 Move Full Name, Email Address, and Business Phone to the Selected Fields column.  
6 Move Start Date and End Date to the Selected Fields column.  
7 Click Next twice. For the Title enter Hardware Reservations by Type and click Finish.

In this section you created a SQL query using the wizard to pull the information together across the three tables. This will make it easy for you to generate your reports because you now have the logic in place for the information that you want to pull together. Let’s see just how easy it is by diving into the next section, where you’ll create the report.

10.2.5. Client reports: displaying the data

Reporting is crucial to mining data and getting together valuable information that your end users can understand. If you were to show your clients or manager the raw information in those three tables, it would be cumbersome to look up. If you were to store it all in one table, it would be frustrating and error prone to have to enter the same client contact information for each reservation. Reports help you take the data from various tables and display it dynamically. Some of this can be done with lookup fields and views in SharePoint, which was covered in chapter 3, but I encourage you to play around with these Access reports as well. You’ll quickly find that they can provide additional functionality to pull information across sites and site collections as well as enhanced reporting options to create appealing reports and to share the data. In this example you’ll create a simple report using the wizard, but I recommend you experiment more once you finish this exercise to continue learning about the power of Access 2010:

Step

Action

Result/Notes

1 In Access 2010 select the Create tab.  
2 Select Report.
3 Select Query Hardware Reservations and add all available fields by clicking >>.  
4 Select to view your data by Hardware Type and click Next.  
5 Click Next twice more and select Landscape for the orientation.  
6 Click Next again, enter Hardware Reservations by Type, and click Finish.  

At this point you’ll need to do some formatting to get your report to fit the page. I’m not going to walk you through this step by step; play around with the boxes in the layout view, shown in figure 10.4. You can select the boxes and resize them until you get your report in a format that you want.

Figure 10.4. Hardware Reservations report by type in layout view. Using this view you can select boxes and move them around and/or resize them until you get the design that you’re aiming for.

With six simple steps you should now have a report that looks similar to figure 10.5.

Figure 10.5. The Hardware Reservations by Type report can dynamically pull data from the three SharePoint lists and display it as shown.

See how easy it is to create reports? I suggest that you run though the Report Wizard again and see what other reports you can create by doing some brainstorming and playing around with the additional features. Once you save the report you generated, you can open this Access database at any time to rerun the report on the information that’s been entered into your SharePoint list.

Now that you understand how to create linked tables, queries, and reports, we’ll discuss how you can take this further using Access Services in conjunction with SharePoint Server. Many of the features other than Access Services, which lets you publish the information to SharePoint, are still available to the end users as long as they have Access 2010. So I encourage you to continue reading even if you have SharePoint Foundation, so you can get a better understanding of Access 2010 functionality.

10.3. Using Access Services with SharePoint Server

We’ve discussed how to create this scenario with SharePoint Foundation and Access Client, and you can continue to use this approach even if you have SharePoint Server. An alternate approach would be to manage the data using local tables instead of a SharePoint list. You might be wondering why that would provide you an advantage and how you could provide an easy way for your end users to enter the data. The advantage of using a local table is that you can then use the web reports to publish the information to SharePoint and allow your end users to dynamically access this information without having to own Access 2010. This can be extremely helpful when you need to provide Access functionality to end users but you aren’t certain what software they have installed. If they don’t have Office or a version with Access installed, they won’t be able to run the reports. You could also use web forms, which can be published to SharePoint so your end users can enter data into the local tables. Let’s begin by creating your local tables to manage hardware, clients, and reservations.

10.3.1. Local tables: managing the data in Access

You want to create a table to manage your clients. To do this you create a local table in Access. Then you create the web forms that will allow your end users to easily maintain the data in these tables, without having to open Access:

Step

Action

Result/Notes

1 In Access 2010, choose File > New > Blank Web Database.
Click Save, and for the table name enter Clients.
 
2 On the Click to Add drop-down list, select Text, and Access will add a text column that by default will be labeled Field1.
3 Double-click Field1 to select it and enter FullName for the label.
4 Repeat steps 2 and 3 twice, but name the two new columns Email and Phone.  

Now that you’ve created this list, let’s create another local table that will store the information pertaining to the hardware that can be reserved:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Table.
Click Save, and for the table name enter Hardware.
 
2 On the Click to Add drop-down list, select Text, and Access will add a text column that by default will be labeled Field1.
3 Double-click Field1 to select it, and enter Hardware Type for the label.
4 Repeat steps 2 and 3 one more time, but name the new column Hardware ID.  

You’ll now create your last local table. This one manages the reservations and the associated client and hardware information from the list you’ve already created:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Table.
Click Save, and for the table name enter Reservations.
 
2 On the Click to Add drop-down list, select Lookup & Relationship, and a dialog box will appear. Leave the first page at its default settings, and click Next.
3 Select the Clients table, and go to the next step.  
4 Select Name, and go to the next step. Leave the defaults, and click Next twice more until you’re on the final screen.
5 For the label enter Client.
6 Repeat steps 2–5, but name the new column Hardware ID, and connect to the Hardware table and select Hardware ID.  
7 For the last two fields select Click to Add and select Date & Time. This will add a Date & Time column, which by default will be labeled Field1.
8 Double-click Field1 to select it, and enter Start Date for the label. Do this one more time to create the End Date column.

You’ve now created local tables in Access similar to those you created in the previous section as lists when you were implementing SharePoint Foundation. The benefit of using SharePoint is that it provides a nice web interface for your end users to work with the information. A local table in Access won’t do that without some additional configuration. In the next section we’ll walk through how to do this by creating web forms. It’s an additional step, but don’t worry; it doesn’t take much effort.

10.3.2. Web forms: entering data using a web form

Because we don’t want our end users opening the database to work with the data, we’ll provide a friendlier way of managing the information using web forms. We’ll begin by creating a web form that’s linked to each of the tables that we’ve created. Later we’ll publish these forms to our SharePoint site, so end users can quickly get to them to enter the client, hardware, and reservation information. Let’s get started with creating your first web form:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Blank Form.
2 Click Show All Tables to get a list of tables that can be added to the form on the right side of your screen.
3 Expand Clients and double-click Name, Email, and Phone. This will add these fields to your form. Once your web form is published to the web, additional controls will be available so you can manage the data.
4 Save the form as Manage Clients.  

Once you’ve finished creating the form, it won’t show the management features to let you flip though the records and add a new record. Later when you publish the form and save using Access Services, these features will appear for the end user, as shown in figure 10.6.

Figure 10.6. Record Management options in Access 2010 when publishing your tables using web forms

Now that you’ve created your form to manage the client information, you need to do something similar for hardware and reservations. You’ll create the Manage Hardware form next:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Blank Form.  
2 Available tables that can be added to the form will display in the Field List shown on the right side of your screen.
3 Expand Hardware and double-click Hardware Type and Hardware ID. This will add these fields to your form. Once this form is published to the web, additional controls will be available so you can manage the data.
4 Save the form as Manage Hardware.  

The final form that you need to create is the form to maintain the hardware reservations. This is important so you can capture the information from the hardware and client tables and associate it with start and end dates for the requested reservation:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Blank Form.
2 Available tables that can be added to the form will display in the Field List shown on the right side of your screen.
3 Expand Reservations, and double-click Client, Hardware ID, Start Date, and End Date. This will add these fields to your form. Once the form is published to the web, additional controls will be available so you can manage the data.
4 Save the form as Manage Reservations.  

That extra step wasn’t too bad, was it? Now that you’ve done that, you’ll be able to use these web forms in your Access web application to publish to a SharePoint site. This will allow your end users to leverage the power of Access without having Access. You now want to prep the data from these tables for your reports. To do this, you’ll need to create a SQL query that pulls the information together so you can then view it in a report.

10.3.3. Web query: generating a web query

This section guides you through some simple steps for creating a web query. A query provides the capability to pull together the data from the three local tables that you created. This will be used for your report in the next section, so you can easily report on the reservation and pull in important client information and hardware information:

Step

Action

Result/Notes

1 In Access 2010 choose Create > Query.
2 A dialog box will pop up and list the tables that you can select. Make sure you add Clients, Hardware, and Reservations.
3 Save the query and name it Hardware Operations Query.  
4 In the Hardware table doubleclick Hardware Type and Hardware ID.  
5 In the Clients table doubleclick Name, Email, and Phone.  
6 In the Reservations table double-click Start Date and End Date. Save your query.

You now have the tables, the web forms to manage the tables, and the query to pull all the information together. This is a good time to start creating your reports. In this next section you’ll create two web reports so you can see your reservations by type and client.

10.3.4. Web reports: generating reports to see reservations by type and client

The first report you’ll create will be grouped by hardware type. This will help you to quickly look at how many servers have reservations, for example. The other type of report that you’ll create will be grouped by client. This will allow you to quickly see how many reservations a client has made:

Step

Action

Result/Notes

1 From the design view of the Hardware Operations Query select Run.
2 Select Create > Report.
3 Select Group & Sort from the Ribbon under the Design tab.
4 Select Add a Group. This option will display at the bottom of the screen.
5. Select Hardware Type for the field to group on.  
6 Resize the fields so they fit nicely on the page.  
7 Click Save, and enter Hardware Reservations by Type for the name.  

Now that you’ve created a report by hardware type, we’ll focus on doing something similar but group it instead by client. To do this follow the previous steps, but select Client for step 5, and for step 7 save it as Hardware Reservations by Client. You now have all the individual components for your Access web application. You just need to tie it all together, and you’ll do this in the next section using a navigation web form.

10.3.5. Navigation web form: tying your reports and forms together

The navigation web form that you’ll create will have tabs on the top and on the right side of the screen. This will allow you to display your reports and web forms to manage the information using two menus. It will consolidate the information so your end users need to remember only one URL, which defaults to the navigation web form. From there they can navigate to the individual components:

Step

Action

Result/Notes

1 Select Create > Navigation > Horizontal Tabs and Vertical Tabs, Right.
2 Locate Hardware Reservations by Type in the Reports section, and drag and drop it onto the first horizontal tab, labeled [Add New].
3 Repeat step 2 for the report Hardware Reservations by Client.  
4 Locate the Manage Reservations form, and drag and drop it onto the first vertical tab, labeled [Add New].  
5 Repeat step 4 for the Manage Hardware and Manage Clients forms.
6 Double-click Navigation Form and enter the title Hardware Reservations.
7 Save the form as Hardware Reservations.  

Now that you’ve created your navigation web form, your finished form should look like the one in figure 10.7.

Figure 10.7. The Navigation web form will provide horizontal and vertical menu navigation to allow your end users to navigate between the reports and web forms that you created.

You’re almost finished. Although you have everything in place, you still need to publish this to SharePoint so your end users don’t have to own Access 2010 to start working with the data. It’s also a good idea to not give them direct access to the database so they can’t start mucking around. To do this, you’re going to use Access Services. Let’s get started.

10.3.6. Access Services: publishing your data to SharePoint

Access Services will allow you to publish the web application that you create in Access 2010 so your end users can work with the reports and dynamically manage the information within the tables. This doesn’t require them to own Access 2010 and can be very useful when you don’t have control over what software your end users have. To publish you need to complete the following four simple steps:

Step

Action

Result/Notes

1 In Access 2010 choose File > Options.
2 Select Current Database.
For Web Display Form select Hardware Reservations.
3 Select Save & Publish from the File menu and under Publish select Publish to Access Services.  
4 Enter the server URL of your SharePoint site and the site name Hardware Reservations. Select Publish to Access Services.  

Congratulations! You’ve just created an Access web application and published it to SharePoint so any of your end users will have the ability to get to these reports to dynamically work with the data. Now that you’re finished with this, let’s discuss what you’ve learned.

10.4. Summary

Following is a summary to help you understand the functionality that you should now be comfortable with implementing from completing this scenario:

  • Creating a custom list and working with the contact list in Share-Point
  • Pulling the data into Access from SharePoint using a linked table
  • Creating local tables in Access
  • Creating web forms in Access to manage the data in the local tables
  • Generating web and client SQL queries using Access 2010’s friendly user interface
  • Providing web and client reports on the data stored in the local and/or web tables
  • Leveraging a navigational web form
  • Publishing your Access web application to SharePoint using Access Services

Let’s test your knowledge. A couple of questions that you should be able to answer are these:

  • What are some of the advantages and disadvantages of using a linked table over a local table to SharePoint? Advantage: A table linked to a SharePoint list allows you to connect to data that already exists. Advantage: A linked table enables you to take advantage of the SharePoint user interface to work with the data. Disadvantage: A linked table can’t be used in a web report or query, but a local table can.
  • What are some of the advantages and disadvantages of implementing with Access versus Access Services? Advantage: If you’re running the client, you’ll have more capabilities in creating client reports and queries than if you’re using a web report or query. Disadvantage: To leverage the client features, your end users must have a valid install of Access to use the system.

This chapter along with the information you learned in chapter 4 regarding the use of document libraries and views will empower you with the tools to report on the information that’s being stored. In the next chapter we’ll cover search, My Sites, cross-site configuration, and site-collection capabilities.