Using SharePoint 2013 with Excel and Access
- 8/15/2013
- Importing data from an Excel spreadsheet to a list in SharePoint
- Exporting a SharePoint list to an Excel spreadsheet
- Exporting an Excel table to a SharePoint site
- Building an Access app
- Creating a table in an Access app
- Working with an Access app in the browser
- Exporting data from an Access desktop database to a list
- Importing a list
- Linking to a list
- Moving data from a desktop database to a list
- Working offline
- Key points
Exporting data from an Access desktop database to a list
Traditionally, Access uses database objects to manipulate and display data, tables, reports, and queries, where the Access database is stored on a file system or in a SharePoint library and requires the use of Access on the computer. When you create such a database with Access 2013, the same file format that was used with Microsoft Office Access 2007 or Access 2010 is created—an .accdb file; these files are known as a desktop database.
Access consists of a number of tabs, many of which provide a quick way to work with SharePoint websites and lists, as summarized in the following table.
In the following table, note that “External Data” in column 1 goes with both row 2 and row 3, that is, “External Data” applies to both “Import & Link” in column 2 and “Export” in column 2.
Tab |
Group |
Description |
External Data |
Import & Link |
Use the More drop-down list to import from or link to data on a SharePoint list. |
Export |
Use the More drop-down list to export the selected object as a SharePoint list. |
|
Database Tools |
Move Data | Use to move your tables to a SharePoint list and create links to those tables in your database. |
Access allows you to export a table or other database objects to a number of formats, such as an external file, an Excel workbook, a text file, a PDF or XPS file, email, an Extensible Markup Language (XML) document, an Open Database Connectivity (ODBC) data source, or a Hypertext Markup Language (HTML) document. You can also export a table to a SharePoint site, where a new list is created.
To export the data to a SharePoint list, you use a wizard that builds an export query, which Access uses to query the Access table for data, and it then copies the data to the SharePoint list. You can save the export query, which you can do without using the wizard. Your saved exports can be found under the External Data tab in the Export group. Similarly, you can save your export as a Microsoft Office Outlook task, which you can then configure to remind you to run the export query.
When a SharePoint list is created from an Access table, the list does not automatically appear on the Quick Launch. To create a link to the list on the Quick Launch, you need to go to the General Settings page by clicking List name, description and navigation on the list’s Settings page.
In the following exercise, you will export a table from within an Access 2013 desktop database into a SharePoint site by creating a new SharePoint list, and then save the export query.
Under Tables, click FurniturePrices, if it is not already selected, and then, on the Access ribbon, click the External Data tab. In the Export group, click More, and then click SharePoint List.
In the Export - SharePoint Site dialog box, in the Specify a SharePoint site area, select the site where you want to export the table, or type the URL in the text box; for example http://wideworldimporters.
In the Specify a name for the new list box, type exportFurniturePrices.
Leave the Open the list when finished check box selected.
Click OK to export the data and display the newly created list, exportFurniturePrices.
Return to the Save Export Steps page of the Export - SharePoint Site dialog box in Access.
Select the Save export steps check box to display the Save As and Description text boxes, as well as the Create an Outlook Task areas.
Click Save Export to close the Export - SharePoint Site dialog box.
)
)
)
)