| Although SharePoint 2003 uses SQL Server database technology to store its information (MSDE is a limited version of SQL Server), Access 2003 can add value when used with SharePoint 2003 and may be better suited to storing certain types of data because many organizations have support personnel who are experienced with Access databases and find them easier to work with than the SharePoint SQL databases. Access can also be used to store information exported from SharePoint 2003 lists, as well as to store forms information, which can assist with backup and storage requirements. Access's ease of use and flexibility, combined with its web form design tools make it the ideal choice. Access 2003 also allows for the integration of multiple data sources including Extensible Markup Language (XML), OLE, Open Database Connectivity (ODBC), and SharePoint 2003. A complete overview of the capabilities of Access 2003 are beyond the scope of this section, but some high-level examples of how it can integrate with SharePoint 2003 are provided. Creating an Access Database and Linking it to SharePoint 2003 Creating a new database is facilitated by the tools that come with Access 2003. When a new database is being created, Access allows the creation of Blank Database, Blank Data Access Page, Project Using Existing Data, Project Using New Data, and From Existing File. If Blank Database is chosen, the user can then opt to use the Table Wizard to create a table, and it is easy to then create a number of business or personal database tables, including a mailing list, contacts, customers, employees, products, or orders table. A brief example is given next showing the creation of a new database in Access 2003, which is then made accessible via an HTML form to look up and enter information: 1. | In Access 2003, choose File, New from the menu and select Blank Database from the New File task pane.
| 2. | In the File New Database window, select a location and filename for the database. Click Create.
| 3. | In the window that opens, double-click Create Table by Using Wizard.
| 4. | In the Sample Tables section, scroll down and highlight Time Billed, as shown in Figure 10.13. In the Sample Fields section, highlight the appropriate item and click on the > button to add it to the Fields in My New Table section. Repeat for all the Sample fields. Click Next.
Figure 10.13. Table Wizard in Access 2003. 
| 5. | In the Table Wizard window, give the table a name (Time Billed in this example). Leave Yes, Select a Primary Key for Me selected. Click Next.
| 6. | Select Enter Data into the Table Using a Form the Wizard Creates for Me. Click Finish.
| 7. | Enter some sample information in each field of the form that appears, as shown in Figure 10.14. The forward arrow button can be clicked to enter additional information, or the right arrow on the keyboard can be pressed for the same effect. Enter some additional information, if desired.
Figure 10.14. Access 2003 table. 
| 8. | Choose View, Datasheet from the menu for a summary view of the information entered so far.
| 9. | Choose File, Save As; keep the same title and select Data Access Page from the drop-down menu. Then click OK.
| 10. | In the New Data Access Page window, navigate to a document library on a SharePoint 2003 site and verify that the file is being saved as a *.htm, *.html file and click OK.
| 11. | A Microsoft Data Access Components window appears. Click OK.
| 12. | Click OK on the next Microsoft Office Web Components window that appears.
| 13. | Exit Access 2003, saving any changes.
| 14. | Open the SharePoint 2003 document library where the *.htm file was saved and click on the name of the new item.
| 15. | Click OK on the two warnings that appear, and the view should reflect the one shown in Figure 10.15. In Figure 10.15, the pointer is hovering over the New button, which can be clicked to enter new information.
Figure 10.15. Datasheet view in SharePoint 2003. 
| 16. | Additional data can now be entered into the Access 2003 database directly from a user-friendly SharePoint 2003 document library.
|
Exporting Data to Access 2003 from SharePoint 2003 Another use of Access 2003 is to store and manage information from SharePoint 2003 lists. This section provides a quick example of the process of exporting information in a custom list to an Access 2003 database. Figure 10.16 shows a SharePoint 2003 list designed to store customer satisfaction information. Management has decided that it would be helpful to export this information to an Access 2003 database and then create reports based on the different resources who worked on the projects to try and spot trends and for employee review purposes. Figure 10.16. The Export to Access link in a SharePoint 2003 list. 
The following steps provide an example of exporting information from a SharePoint list to an Access 2003 database: 1. | From the SharePoint 2003 list in datasheet view click on the Task Pane button in the toolbar (circled in the tool bar in Figure 10.16) to reveal the task pane.
| 2. | Click on Export to Access (circled in the Office links task pane in Figure 10.16).
| 3. | Click on New Database.
| 4. | Microsoft Access 2003 opens (assuming that it is installed on the computer). Enter the name of the file and choose a location (local folder or network file share). In this case, the filename is Customer Sat db1.mdb. Click Create.
| 5. | Depending on how Internet Explorer is configured, you may be asked to enter your password to gain access to the SharePoint 2003 list information. If so, enter your password and press Enter.
| 6. | The database opens in Access 2003 as shown in Figure 10.17.
Figure 10.17. Access 2003 table. 
| 7. | From the Objects pane select Reports and then click on Create Report by Using Wizard.
| 8. | From the Report Wizard, select the available fields and click on the > button to add to the Selected Fields window (in this example, Customer Name, Project Name, Technical Sat, Soft Skills Sat, Deliverables Sat, and Consultant are selected as shown in Figure 10.18). Click Next.
Figure 10.18. Access 2003 Report Wizard. 
| 9. | Select the grouping you want; in this example, Consultant is selected, and the > arrow clicked. Then click Next.
| 10. | Enter which field(s) to sort by; in this case, Customer Name is entered as sort field number one. Click on the Summary Options button.
| 11. | Choose to display the average (Avg) for each of the satisfaction fields and click OK.
| 12. | Click Next.
| 13. | Choose how you want to lay out the report (in this case, Align Left and Landscape are selected). Click Next.
| 14. | Choose the style for the report (in this case, Compact is selected). Click Next.
| 15. | Enter a title for the report (in this case, the title Customer Satisfaction: By Consultant is entered). Click Finish. Figure 10.19 shows the results of the report generated. Some editing is usually needed to make sure that the columns are wide enough
Figure 10.19. Results of the Access Report Wizard. 
|
The old data in the SharePoint 2003 list can now be deleted, if needed, at the end of a year or quarter, and new information can be exported to the same Access 2003 database when it is time for the next management report. The reports can also be saved in HTML or XML format, or by using the Access Snapshot process. You can view, print, store, publish, distribute, and archive a report snapshot without having an Access license by using a combination of Snapshot Viewer and other programs, such as an email program or Internet Explorer. |
