Link to or import data from dynamics 365

You can import or link to data from Microsoft Dynamics 365, which is a cloud-based, business management solution that integrates Enterprise Resource Planning (ERP) modules into one software package. Data from Dynamics 365 is unified across these modules, including Sales, Finance, and customer relationship management (CRM). For more information, see Microsoft Dynamics 365.

• When you link to data, Access creates a two-way connection that synchronizes changes to data in Access and Dynamics 365.

• When you import data, Access creates a one-time, copy of the data, and so changes to data in either Access or Dynamics 365 are not synchronized.

Note    The ability to link to or import data from Dynamics 365 is only supported in volume licensed instances of Access 2019 or the following Microsoft 365 Enterprise plans: Microsoft 365 Apps for enterprise, Office 365 Enterprise E3, and Office 365 Enterprise E5. For more information, see Compare Microsoft 365 Enterprise Plans.

Before you begin

Want things to go smoother? Then make the following preparations before you link or import:

• Identify necessary connection information, including the URL of the Dynamics 365 site and an account with security access. For more information, see Create users and assign Microsoft Dynamics 365 (online) security roles and Manage subscriptions, licenses, and user accounts.

• Your organization may already have a Dynamics 365 account. If that's not the case, you can sign up for a 30-day trial account at Try Dynamics 365 (online).

• Consider the number of columns in each table or view. Access does not support more than 255 fields in a table, so Access links or imports only the first 255 columns.

• Determine the total amount of data being imported. The maximum size of an Access database is two gigabytes, minus the space needed for system objects. If Dynamics 365 contains large tables, you might not be able to import them all into a single Access database. In this case, consider linking to the data instead of importing.

• Secure your Access database and the connection information it contains by using a trusted location and an Access database password. For more information, see Decide whether to trust a database and Encrypt a database by using a database password.

• Identify the tables that you want to link to or import. You can link to or import more than one table in a single operation. All Dynamics 365 tables have a primary key that is defined as the first column in the linked or imported table.

• Plan for making additional relationships. Access links to or imports selected tables and any related tables in the Dynamics 365 data model. For more information on this data model, see Browse the metadata for your organization.

  Access attempts to automatically create relationships between these related tables. But, you may need to manually create additional relationships between new and existing tables by using the Relationships window. For more information, see What is the Relationships window? and Create, edit or delete a relationship.

Stage 1: Get started

1. Select External Data > New Data Source > From Online Services > From Dynamics O365 (online).

2. In the Enter the URL of the Dynamics 365 Site dialog box, enter the URL of the Dynamics 365 site.

3. Do one of the following:

   • To import, select Import the source data into a new table in the current database.

   • To link, select Link the data source by creating a linked table.

4. Select OK.

Stage 2: Select Tables to link to or import

• In the Link Tables or Import Objects dialog box, under Tables, select each table or view that you want to link or import, and then click OK.

  

Access links to or imports selected tables and any related tables in the Dynamics 365 data model.

Results

When a link or import operation completes, the tables appear in the Navigation Pane with the same name as the Dynamics 365 table. During an import operation, if that name is already in use, Access appends "1" to the new table name. But you can rename the tables to something more meaningful.

In an import operation, Access never overwrites a table in the database. Although you cannot directly append Dynamics 365 to an existing table, you can create an append query to append data after you have imported data from similar tables.

In a link operation, if columns are read-only in the Dynamics 365 table, they are also read-only in Access.

Tip    To see the Dynamics URL, hover over the table in the Access navigation pane.

Update the linked table design

You can't add, delete, or modify columns or change data types in a linked table. If you want to make design changes, do them in Dynamics 365. Design changes to Dynamics table will be reflected when you open the link in Access. For more information, see Create and edit fields.