Using SharePoint 2013 with Excel and Access

Moving data from a desktop database to a list

Many Access 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 is not truly meant for concurrent use. As Access desktop 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 Access 2000, various tools and wizards have helped with this process. Starting with Access 2007, you can upsize your Access desktop database to SharePoint, which is known as moving your Access database.

When you move data from an Access desktop database to a SharePoint site, this process creates a SharePoint list for each Access table. Data from Access tables is moved into these SharePoint lists, and each data row becomes a list item in a SharePoint list. Tables in the desktop database are replaced with linked tables that point to the newly created SharePoint list or lists. The Access database now becomes a user interface to the data by retaining views, reports, and relationships between tables.

Because the data is 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. If you choose to save the desktop database in the library, users who want to use the desktop database can go to the library in a browser, where the desktop database can be opened in Access.

Prior to Access 2007, multiple users kept their own copies 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 here, users can add and modify data by using either SharePoint or the linked tables within the Access database. New views, data relationships, and reports maintained in the desktop database file can be managed as any other document when saved in SharePoint, including check-in and checkout facilities. Security on the data and the desktop database can be maintained using SharePoint security. To take advantage of these features, you must move your data from your desktop database to SharePoint.

In the following exercise, you will move data from within an Access desktop database to a SharePoint site, and then save the desktop database in a library.

1. On the Access ribbon, click the Database Tools tab. In the Move Data group, click SharePoint to display the Export Tables to SharePoint Wizard.

2. In the What SharePoint site do you want to use? text box, type the name of your SharePoint site, such as http://wideworldimporters.

3. Click Next to start the move operation.

4. When the message stating that “Your tables have been successfully shared” appears, select the Show Details check box to check which lists were created and the name of the database backup.

   

5. Click Finish, and then click File to display the backstage view of Access.

6. In the left navigation pane, click Save As. Under File Types, verify that Save Database as is selected, and then in the right pane, in the Advanced section, select SharePoint.

   

   Click to view larger image

7. Click Save As.

8. In the Save to SharePoint dialog box, in the File name box, type the URL of the SharePoint site that contains the library where you wish to save your desktop database, such as http://wideworldimporters, and then press Enter to display the libraries of the SharePoint site.

   

9. Double-click Documents, and then click Save to complete the save process.

   

   Click to view larger image