Moving Data from an Access 2007 Database to a SharePoint Site

Many Access 2007 applications grow from the need to manage and aggregate data. These data-centric applications often prove useful to more than one person in an organization, and thus the need to share them increases. However, Access 2007 is not truly meant for concurrent use. As Access 2007 database applications grow and become more complex, it is necessary to consider upsizing them to a data repository that can support more users while increasing availability, reliability, and manageability. Beginning with Microsoft Office Access 2000, various tools and wizards have helped with this process. With Access 2007, you can now upsize your Access database to Windows SharePoint Services, which is known as moving or publishing your Access 2007 database.

When you move data from an Access 2007 database to a SharePoint site, this process creates a SharePoint list for each Access 2007 table. Data from Access 2007 tables move into these SharePoint lists, and each data row becomes a list item in a SharePoint list. Tables in the Access 2007 database are replaced with linked tables that point to the newly created SharePoint list(s). Optionally, the Access 2007 database can then be loaded into a SharePoint document library. The Access 2007 database now becomes a user interface to the data by retaining views, reports, and relationships between tables. Access 2007 views also appear as SharePoint list views.

Because the data are now in SharePoint, you can use SharePoint functionality. For example, you can restore deleted list items from the Recycle Bin and apply workflow rules to data items. Changes to the list are versioned by default. If you choose to save the database in the document library, users who wish to use the database can navigate to the document library in their browser, where the database can be opened in Access 2007.

Prior to Access 2007, multiple users kept their own copy of an Access database and amended it separately, often not viewing others’ amendments until they were included in official documents and the need to amalgamate the changes was recognized. To allow users to keep their own copy of a database, a business process would need to be introduced to maintain the data integrity of the database and distribute updates to the appropriate users. By using the process outlined in the previous paragraph, Access 2007 users can add and modify data by using either SharePoint or the linked tables within the Access 2007 database. New views, data relationships, and reports maintained in the Access 2007 database file can be managed as any other document when saved in SharePoint, including check-in and check-out facilities. Security on the data and the Access 2007 database can be maintained using SharePoint security. To take advantage of these new features, you must move your data from within your Access database to SharePoint.

In the following exercise, you will move data from within an Access 2007 database to a SharePoint site by uploading the Access 2007 database into a document library.

USE the  MoveWideWorldImporters.accdb database located in the Documents\Microsoft Press\SBS_WSSv3\Chapter 13 folder. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercise.

BE SURE TO start Access 2007 before beginning this exercise.

OPEN the MoveWideWorldiImporters.accdb database.

1. In the MoveWideWorldImporters: database window, click the External Data tab.

2. In the SharePoint Lists group, click Move to SharePoint. The Move to SharePoint Site Wizard dialog box is displayed.

3. In the What SharePoint site do you want to use? textbox, type http://wideworldimporters.

4. Click the Browse button. The Location dialog box is displayed.

5. Click Browse Folders. The Favorites and Site Content areas of the dialog box appear.

6. Double click Shared Documents. The contents of the Shared Document library are displayed.

7. Click OK. The URL of the document library is displayed in the Move to SharePoint Site Wizard dialog box.

8. Click Next.

The Move to SharePoint Site Wizard dialog box then displays the progress of the move operation, eventually stating that the tables are successfully shared and noting whether any issues were encountered.

9. Select the Show Details check box.

The Move to SharePoint Site Wizard dialog box displays the tasks it completed during the move operation. Note that a backup of the database is made.

10. Click Finish. The three Access 2007 tables-Customers, Employees, and Opportunities-are now linked tables.

11. Under SBS 2007 Navigation, in the Unassigned Objects group, double-click the Move to SharePoint Site Issues table.

Access 2007 opens the Move to SharePoint Site Issues table in Datasheet view, where you can view the issues that Access 2007 encountered during the move.

Tip 

If you make changes to the Access 2007 database, such as creating a new view or report, then the database file can be saved to the document library on the server by clicking Publish Changes.

CLOSE the  MoveWideWorldImporters.accdb database and the browser. Choose Yes if prompted to save changes.