Applies to: Access for Microsoft 365, Access 2019.
Microsoft Dynamics 365 helps you to import or link to data from this programme, which is a cloud-based, business management solution that combines Enterprise Resource Planning (ERP) modules into one software package. Data from Dynamics 365 is linked across these modules, including Sales, Finance, and customer relationship management (CRM). For more information, see Microsoft Dynamics 365.
- Once you link to data, Access produces a two-way connection that synchronises changes to data in Access and Dynamics 365.
- After you import data, Access designs a single-use, copy of the data, and so changes to data in either Access or Dynamics 365 are unsynchronised.
The ability to link to or import data from Dynamics 365 is only supported in volume licenced 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, licences, and user accounts.
- Your organisation 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 organisation. 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
- Pick External Data > New Data Source > From Online Services > From Dynamics O365 (online).
- In the Enter the URL of the Dynamics 365 Site dialogue box, state the URL of the Dynamics 365 site.
- Do one of the following:
- To import, click Import the source data into a new table in the current database.
- To link, pick Link the data source by creating a linked table.
- Choose OK.
Stage 2: Select Tables to link to or import
- In the Link Tables or Import Objects dialogue box, below Tables, pick each table or view that you want to link or import, and then click OK.
Access links to or imports specified tables and any associated tables in the Dynamics 365 data model.
After a link or import operation completes, the tables display in the Navigation Pane with the same name as the Dynamics 365 table. Amid an import operation, if that name is already in use, Access applies “1” to the new table name. But you can rename the tables to something more descriptive.
In an import operation, Access always refrains from updating a table in the database. Despite you being unable to manually import Dynamics 365 to an existing table, you can generate an append query to transfer data after you have imported data from related tables.
In a link operation, if columns are read-only in the Dynamics 365 table, they will also be read-only in Access.
To view 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 edit data types in a linked table. If you want to apply design changes, do them in Dynamics 365. Design updates to Dynamics table will be mirrored after you open the link in Access. For more information, see Create and edit fields.