Use external data with Access

Microsoft Access 2013 is a landing pad for all types of data and external data is no exception. You can import the BDCM file containing an external content type and link to the external data in your Access database on your client computer. The data is read-only, but you can periodically refresh the data.

Note:  In this case, unlike other Office products, the Business Connectivity Services Client Runtime cache is not used to synchronize with the external list when you access the external data.

Important:  You must have . Microsoft .NET Framework 4 or higher installed on your client computer. You can download it from Microsoft .NET Framework 4.

For more information, see Find content about external data.

In this article

Before you begin

Export the BDCM file from SharePoint products

Import the BDCM file into Access 2013

Create a linked table based on the imported BDCM file

Before you begin

Working with external data requires several pre-requisite tasks to enable secure access to the data. The following information can help you plan your next steps. Also, if you experience problems trying to work with external data, this information can help you identify the issue. To access external data, you or an administrator must do the following:

Prepare the external data source    An administrator may need to create an account and provide permissions to the data source to ensure that the right people have access to the data and that the data does not end up in the wrong hands. In an external database, the administrator may also want to create specific tables, views, queries, and so on to limit the results to just what is needed and to help improve performance.

Configure SharePoint services and accounts    An administrator must activate Business Data Connectivity Services and Secure Store Service.

Configure Secure Store Services    An administrator must do the following: determine the best access mode for the external data source, create a target application, and set the credentials for the target application.

Configure Business Data Connectivity Services    An administrator must ensure that the user who creates the external content type has permission to the Business Data Connectivity metadata store and that appropriate users have access to the external content type that the external list is based on.

Create an external content type    A user must define an external content type which contains information about connections, access, methods of operation, columns, filters, and other metadata used to retrieve the data from the external data source.

Top of Page

Export the BDCM file from SharePoint products

A BDCM file is an XML file that contains all the information defined for an external content type. Keep in mind the following when using a BDCM file with Access 2013:

• Only flat data structures are supported, hierarchical data structures are not.

• The following .Net Types are supported: System.Boolean, System.Char, System.String, System.Int32, System.Int16, System.Double, System.DateTime, System.GUID. Any other .Net Type is converted to text.

To create a BDCM file do one of the following:

Microsoft SharePoint Designer 2013

1. In Microsoft SharePoint Designer 2013, open the SharePoint site that contains the external content type.

2. On the left pane, under Site Objects, select External Content Types.

3. Select the external content type you want to use.

4. Right-click the selection, and then click Export BDC Model.

5. In the Export BDC model dialog box, enter the name of the model, and click OK.

6. In the File Save As dialog box, browse to a location, and then click Save.

Business Data Connectivity metadata store

Note: To do this task, you must be a farm administrator, an administrator of the Business Data Connectivity service application, and have Edit permission on the model and on all external systems that are contained in the model.

1. On the Central Administration Web site, in the Application Management section, click Manage service applications.

2. Click in the Name column of the row that corresponds to the Business Data Connectivity service application.

3. On the ribbon, in the View group, click BDC Models.

4. On the BDC Models page, select the model.

5. On the ribbon, in the BDC Models group, click Export.

6. On the Export page, in the File Type field, select Model and then in the Advanced Settings section, select the one or more types of resource to export:

   • To export localized names for the external content types in a particular locale, click Localized names.

   • To export properties for external content types, click Properties.

   • To export permissions for external content types, click Permissions.

   • To export an implementation-specific proxy that is used to connect to the external system, click Proxies.

7. If you saved a set of resources that can later be exported for use in a particular environment, in the Use Custom Environment Settings field, type the unique name associated with the environment-specific settings to export.

8. Click Export.

The location on your network where the file is located is displayed.

Top of Page

Import the BDCM file into Access 2013

1. In Access 2013, click External Data > Web Service.

2. At the bottom of the Create Link to Web Service Data dialog box, click Install New Connection.

3. In the Select a connection definition file dialog box, browse to and select the BDCM file.

Top of Page

Create a linked table based on the imported BDCM file

1. In Access 2013, click External Data > Web Service

   The Create Link to Web Service Data dialog box displays the tables of data that the Web Service provides. For example, if you connected to the AdventureWorks Web Service, you might see something like this:

   

2. If available, under Specify optional parameter values, enter a parameter value for each parameter that you want to use to filter or limit the external data.

3. Select the table that you want to use.

4. Click Create Linked Table.

Top of Page