Migrate Access data to Dataverse

The combination of Microsoft Access and Microsoft Dataverse opens up a world of opportunities for the citizen developer. You can share Access data with Dataverse, which is a cloud database upon which you can build Power platform apps for the web, a phone, or a tablet in the following ways:    

  • Create a Power Platform environment and add a new Dataverse database.

  • Migrate Access data to Dataverse, either the full Dataverse or Dataverse for Teams.

  • Continue using your Access desktop solution to synchronize the editing of the data based on linked tables and using existing front-end forms, reports, queries, and macros.

  • Create a low code Power apps, automated workflow, AI-driven virtual assistant, or Power BI dashboard that works across many devices, while simultaneously viewing and editing the same underlying Dataverse data.

A visual of what you can do between Access and Dataverse

Furthermore, Dataverse is like a primary connector for the Power Platform and can store common data shared across multiple applications. This ability provides additional cross-platform opportunities for the interaction and management of shared data in Microsoft 365, Azure, Dynamics 365, and standalone applications.

Note      The terms "column" and "field" are used interchangeably in both Access and Dataverse. The term "table" and "entity" are used interchangeably in Dataverse.

Before you begin

Want things to go smoother? Then first obtain required software licenses, set up your Dataverse environment, plan what to do with keys and relationships, and anticipate data capacity and validation issues even before you start migrating.

Important    Backup your Access database. We strongly recommend that you do this. For more information see Protect your data with backup and restore processes.

BETA TESTERS: Obtain credentials from the product team. For now, consider this background information.

Make sure you obtain the necessary licenses:

Access      A Microsoft 365 plan that includes Access. For more information, see Microsoft 365 with Office apps.

Dataverse      A Power Apps plan. For more information, see Power Apps plans and pricing.

Dataverse for Teams      A Microsoft 365 plan that includes Teams. For more information, see Microsoft 365 with Office apps.

Additional licenses may be required if your apps include Power Automate, Power BI, and Power Virtual Agent. For more information, see Power Apps plans and pricing.

BETA TESTERS: Using credentials from the product team obviates the need to follow these instructions. The following is background information.

There are two ways to use Dataverse, the full version and Dataverse for Teams.

Dataverse  

The full version of Dataverse provides all available data types for cross-device, low-code apps and supports many users. Once you have your Dataverse license, you can either access a current Dataverse environment or create a new environment to migrate your Access data.  For more information, see What is Microsoft Dataverse?, Get started using Dataverseand Add a Microsoft Dataverse database.

  1. Ensure that you have the necessary permissions to import data into Dataverse. You need the security role of "Environment Maker" and app users need the security role of "Basic User". For more information, see Grant users access in customer engagement apps.

  2. Sign in to Power Apps from this browser page, https://powerapps.microsoft.com/.

  3. Select Settings alternate text  > Admin Center.

  4. On the Admin center page, select New. The New Environment pane opens.

    Creating a new Dataverse environment

  5. In the Name box, enter the environment name.

  6. In the Type box, select Trial from the drop-down list.

  7. In the Region box, keep the default United States selection.

  8. In the Purpose box, optionally enter a description.

  9. In Create a database for this environment, select Yes.

  10. When you start the migration process in Access, the Global Discovery Service should discover the correct Instance URL. For more information, see Migrate: select data source and destination.

Dataverse for Teams

Microsoft Teams facilitates messaging, chats, meetings, webinars, and timely communication between work groups and colleagues. You can also improve productivity and customize Teams by adding a variety of apps including Power Apps based on Dataverse for Teams. This effectively provides a built-in low code data platform for Teams and one-click solution deployment.

Once you have a Microsoft 365 plan with Teams, no additional license is required for apps, workflows, and virtual agents within Teams, but Power BI apps require a separate license. Also, access to the Dataverse environment is limited to the Teams owners, members and guests and there are some feature limitations to the underlying Dataverse.

For more information, see Microsoft Dataverse for Teams overview, Power Apps and Microsoft Teams integration, and Get started with Microsoft Dataverse for Teams.

To provision the Dataverse for Teams environment, you first need to install an app into Teams.

  1. Open Teams and select Apps in the lower-left corner of the window.

  2. On the App Marketplace window, use the search box to find "Power Apps", and then select Power Apps.

  3. To add the Power Apps application to Teams, select Add. The Power Apps window appears.

  4. Select Create an app.

  5. Select the team for your app.

  6. To create a Dataverse for Teams environment, select Create. Watch for a pop-up window with a message letting you know that the process has finished.

  7. In the Power Apps editor, enter an app name, and then select Save.

  8. When you start the migration process in Access, the Global Discovery Service should discover the correct Instance URL. For more information, see Migrate: select data source and destination.

Manually discover the correct Instance URL

If for some reason the Global Discovery Service can't discover the correct instance URL, you can locate it in Power Apps and then manually enter it during the migration process.

Dataverse      Select Settings alternate text  at the top right of the page, select Session Details, and the correct Dataverse URL is listed as the Instance URL.

Dataverse for Teams      After creating an App for your Dataverse database, select About, and the correct Dataverse URL is listed as the Instance URL.

It isn’t just data that you’re migrating. Thinking ahead about your goals with respect to primary keys and table relationships helps you streamline and understand the best decisions for a successful migration.

Understanding primary keys and primary names

For primary keys, Access uses the AutoNumber data type, which auto-increments a unique number, and Dataverse uses a Globally Unique Identifier (GUID) data type, which is auto-generated as a unique identifier and required in each table. Dataverse also has another required column, called a Primary Name, but which is not required to be unique. For example, a customer name could be the Primary Name that corresponds to a unique Customer ID. There may be two customers with the name "Jones”, but each would have a unique Customer ID.

Dataverse doesn’t require imported data to have a primary key, it just automatically creates a GUID. But during the migration process, you need to make choices about how to handle the Access primary key and which column becomes a Primary Name:

Primary key      If an Access primary key (AutoNumber) is also a business key, such as a part number, then you most likely want to keep it because it’s used to identify objects in the real world. But if an Access primary key (AutoNumber) is used exclusively to create relationships, then it could be replaced by a Dataverse Primary Key (GUID).

Primary name      You must also set an Access column as the Dataverse Primary Name column. A Primary Name can contain text or numbers, including whole numbers. But unlike text, you can’t edit those numbers. Conveniently you can set the Access primary key (AutoNumber) as the Primary Name column in the migrated Dataverse table. This effectively maintains current relationships without using the Dataverse primary key (GUID). Alternatively, you can set a text column as the Primary Name column. By default, the first text column from the right is used as the Primary Name column. For more information, see Create a primary name column.

Comparison of primary keys and primary names

Description

Access Primary Key

Dataverse Primary Key

Dataverse Primary Name

Data type

AutoNumber

GUID

Editable text or a read-only number

Contents

A simple, sequential number

Randomized numbers and letters

Human-readable business key, or a whole number

Example

234

123e4567-e89b-12d3-a456-426655440000

A name, such as "Jones".

A whole number, such as 234.

Requirement

Often used but not required

Every table must have one

Every table must have one

Modifiable

Yes

No

Yes, for text
No for whole numbers

Unique identifier

Usually

Always

Optionally

Compound key

Supported and often combined with AutoNumber

Not supported

Not supported

Visibility

Usually, the first table column

Usually hidden but can be displayed.

Usually visible

Indexing

Can be indexed.

For more information, see Create and use an index to improve performance.

Indexed based on the Alternate Key.

For more information, see Work with alternate keys.

Not applicable

Comments

Once defined and you enter data, you can’t change any other field to an AutoNumber data type.

Can’t be created by a user or during a data import operation.

Also used with auto-generated fields in data cards, model-driven forms, and with lookups in form creation.

Understanding Access and Dataverse relationships

It’s important to understand how you want to manage table relationships along with primary keys. For the tables you select to migrate, you can choose to automatically move all related tables with the selected tables or ignore them during migration and just leave the related tables in Access. Either way, you can also choose to link the tables you migrate to maintain the current relationships in Access.

During the data export operation, Access tables with one-to-many relationships can be re-created as one-to-many relationships in Dataverse. Like Access, Dataverse also has referential integrity, such as cascading updates of related fields and cascading deletes of related records, but you can’t create a relationship between fields that are not primary keys.

REVIEWERS: Are cascading relationships automatically converted or must they be manually re-created?

Finally, both Access and Dataverse support a lookup between two tables in a one-to-many relationship. During the migration process, Access lookups are recreated in Dataverse as the following example shows:

  • Two Access tables, Customers and Orders, have primary keys CustomerID and OrderID based on an autonumber data type.

  • During the export operation, both Access primary keys are converted from an autonumber data type to a Dataverse primary key (GUID).

  • A lookup is created for the CustomerID foreign key in the Orders table to find the matching CustomerID primary key in the Customers table.

  • In this one-to-many relationship example, the Orders primary key is not used, but of course can be used for other relationships.

For more information, see Video: Create relationships with the Lookup Wizard (office.com) and Create a relationship between tables by using a lookup column (docs.com).

Note      Both Access and Dataverse support many-to-many relationships. Dataverse even uses a built-in junction table. However, the ability to migrate many-to-many relationships is not supported in this current Beta. Microsoft is examining this as a future enhancement. For more information, see Video: Create many-to-many relationships (office.com) and Create many-to-many table relationships overview (docs.com).

Guidance for decisions about keys and relationship

To help you decide which is the best choice for you, here’s a summary of common options when you migrate:

Migrate data but ignore relationships      You decide to remove the Access primary key (AutoNumber) column because there are no other Access tables with references or relationships that depend on it, and it would be confusing to have it in the Dataverse table alongside the Dataverse primary key (GUID).

Migrate data and relationships with linked tables      You decide to keep the Access primary key (AutoNumber) and use it as the primary key in the Dataverse entity as a unique identifier. You set the Access primary key (AutoNumber) column as the Primary Name and ignore using the Dataverse primary key (GUID) because it’s long and confusing. Note that you can’t delete the Dataverse primary key (GUID). Finally, you create linked tables, to preserve the Access relationships.

Migrate data without linked tables and recreate relationships in Dataverse      Because tables are no longer used in Access, you can re-create relationships in Dataverse by using the Dataverse Primary Key (GUID), adding Dataverse lookups, and choosing a text field in each table as a Primary Name.

REVIEWERS: Are self-joins supported, such as an Employees table where each employee has a ManagerID, joined to EmployeeID?

Understand the differences in storage capacity as each database has different maximum size limits:

An important part of the migration process is to validate the data in several ways:

  • To prevent data loss, Access ensures the Access table doesn’t exceed Dataverse size limits and that the number of columns in the Access table doesn’t exceed the Dataverse maximum number of fields for a table.

  • If the Access table contains unsupported Dataverse data types or the column has values that exceed Dataverse data type ranges, Access provides additional information to help you fix the errors.

Only supported data types are exported. The original Access table, including unsupported data types, remains in Access. Once you are satisfied that all the data has migrated completely and correctly, you can either keep the original Access table, or delete it and copy it to a backup database.

For more information, see Migrate: comparing Access and Dataverse Data types.

Migrate Access data to Dataverse

The process of migrating Access tables and columns to Dataverse includes: creating and specifying a Dataverse environment, exporting data from Access to Dataverse, selecting tables and related tables, creating linked tables in Access to the migrated tables in Dataverse, managing primary keys, names, and relationships, validating the data export, and previewing the results in Access and Dataverse.

  1. Start Access, select Account, select Switch Account. The Account dialog box opens.

  2. Select Sign in with a different account, and then enter your Power Apps credentials. Depending on your environment, you may need to enter additional security information, such as a secondary authentication or a pin.

    Note    Always double-check you are logged in to the correct account. It’s easy to forget, especially when you switch back and forth between different accounts.

  3. Open the Access database you want to migrate.

    Tip      Make sure all open Access objects are closed, because a lock can prevent table migration.

  4. Right-click a table in the navigation pane and select Export > Dataverse

  5. On the Export Objects dialog box, select the tables you want to export and unselect the tables you don’t want to export.

  6. Select OK.

    Selecting tables to migrate

  7. In the Export Data to Dataverse Environment dialog box, select an instance URL from the list provided by the Global Discovery Service.

    Note      In Power Apps, an Instance URL represents a specific session of a Power Apps environment. It shouldn’t be confused with a Web page URL. A component of the string, “crm.dynamics” is a carryover from previous versions of Power Apps.

    Entering the Dataverse URL

    For more information, see Begin: set up a Dataverse environment.

The next step is to make important choices about how you want to migrate. Notice that the default values are the most common ones.

Check boxes to select about linking tables and auto-selecting related tables

  1. To export related tables of the selected tables, select Export all related tables.

  2. To view these tables, select See Related Tables at the bottom. The Related Tables dialog box appears.

    Table relationships are displayed in a hierarchical view in two columns: the left column displays the selected tables, and the right column displays the related tables.

    A list of related tables

  3. To create table links in Access for each Dataverse table created by the export operation, select Link to Dataverse table after exporting.

    If your Access tables are referenced in queries, forms, reports, and macros, then you probably want to link to the exported tables that are now Dataverse tables.

  4. To preview the results, select Open the Dataverse table URL when finished.

  5. Select Next.

The validation process automatically begins when you see a dialog box that displays "Running Validator…" at the top. If the validation process is successful, another message displays "Validation complete", and then you can select OK.

Watch for these messages:

Message

Explanation

"Retrieving list of tables from OData source"

The migration process has begun.

"Exporting select objects"

The tables you selected are being migrated to Dataverse.

"All tables that didn't encounter an error have been successfully exported."

If a single error occurs in a table, the table is not exported, otherwise the table  export operation is complete.

To continue, select Close.

"Importing selected tables and any related tables"

Depending on the way you exported your tables, linked tables are created, original tables are renamed, and several utility tables in Dataverse are created and linked in Access.

BETA TESTERS: The following information doesn't apply to the this Beta, but consider it as planning for future Betas.

If the validation process is unsuccessful, there are three types of errors you may encounter:

Data type in table is not supported  

Access displays a message the data type is not supported. The data remains in Access, is migrated to Dataverse, but the unsupported column not exported. For example:

Validation Example: Data type not supported

Data is out of range

Access displays a message that the data type is out of range. For example:

Validation Example: Limit exceeded

Required field/row is not supported or out of range

Access displays a message that the data is out of range and is also also required in a row or field. For example:

Required Field/Row Not Supported or Out of Range

In this case, you can do one of the following:

  • Do not export the table at all. The entire Access table remains in the Access database and the export operation to Dataverse is cancelled.

  • Export the table except the row that contains the required value. The export operation continues, and the row is added to both the errors query and the errors table.

If you chose to link tables, confirm that the results are what you expected. Watch for this message "". The original tables remain but their names are changed. The linked tables should now have the original table names so that all front-end objects continue to work as before. The Access primary key (AutoNumber) is preserved. A new column is added that corresponds to the Dataverse Primary Key (GUID) and the column name is the table name.

Access also adds additional linked tables from Dataverse called Teams, Users, and Business Units. These utility tables contain the following useful information:

Users      Email address, full name, phone, license type, business unit, and so on.

Teams      Team, Team name,  membership, and so on.

Business Unit   Cost center, Web site, Credit Limit, and so on.

You may want to use this additional information to enhance your Access solution. You could maintain these tables in a form or add them to a report.

It’s a good idea to review and confirm the migration process by examining each table and field in the Dataverse environment. Dataverse table and field names should match the Access table and column names. Access automatically opens the Dataverse environment to the first migrated table.

Previewing the data migration in Dataverse

Access tables appear in Dataverse as tables with a corresponding display name and data type:

  • The "Display Name" column should list each Access field name within a table.

  • The format for a "Name" field has a prefix, an underscore, and the table name, for example, cr444_<table name>

  • All migrated Access fields appear as "Custom" under the Type column, next to the auto generated GUID and existing Dataverse field data types.

Note   If there's already a Dataverse table with the same name as the exported Access table name, a number is appended to the new Dataverse table.

By default, Access sets the first text column (from left to right) as the Dataverse primary name. If a table has no text fields, Access adds an empty text column as the last field in the table and sets that field as the Dataverse primary name. This column is visible in Access and Dataverse.

For more information, see Work with any data in Dataverse.

When migrating Access data types to Dataverse, it’s vitally important to understand what is not supported, what is partially supported, and what is fully supported for each matching data type.

Of the many data types that are supported, there are some different limits and variations between Access and Dataverse. During migration, the validation process alerts you to data conversion errors. In most cases, there are practical alternatives or workarounds. For more information, see Introduction to data types and field properties in Access and Column data types in Dataverse.

The following table provides more details about supported data types, limits, and variations.

Summary of data types

Access

Dataverse

Limits

Variations

Short Text

Text

Dataverse: 4000
Access: 255

In Dataverse, if the string is updated beyond 255 characters, Access casts the value to Long Text for display.

Long Text

Multiline Text

Dataverse: 1,048,576
Access: 1 GB with a display limit of 64,000

Access prevents export if the Dataverse limit is exceeded.

Hyperlink

URL

Dataverse: 4000
Access: 8,192 characters (including the URL, display name, and tool tip parts)

A plain URL is converted to one column.

Dataverse can’t display the Access parts in one field, so Access exports a column for each part to Dataverse.

To export all Access parts, split them into separate string columns, and then recombine them in Dataverse.

To re-display the Dataverse columns in Access, recombine them into one column.

DateTime

DateTime

Both Dataverse and Access use the standard date and time format

Full support

Date/Time Extended

No equivalent

Access: For more information, see Date/Time Extended.

Converts to a Dataverse DateTime datatype, but fails with a date or precision that exceeds the DateTime limit.

AutoNumber

Unique Identifier

Dataverse: GUID
Access: integer, +2,147,483,647

Dataverse converts the Access value to an integer.

Currency

Currency

Dataverse: +/- 922,337,203,685,477 with two to ten decimal places
ccess: +/- 922,337,203,685,477 with four decimal places

Dataverse has built-in exchange rate conversion but only supports one currency format in a table.
Access supports more than one currency format in a table. Consider converting the Access Currency data type to Decimal.

Number: Decimal

Decimal Number

Dataverse: +/-100,000,000,000 and up to 10 decimal places.
Access: +/- 10^28-1 and up to 28 decimals places

Access doesn’t export data that exceed Dataverse limits.

Number: Integer

Whole Number

Dataverse: +/- 2,147,483,647
Access: +/- 2,147,483,647 (1, 2, or 4 bytes)

Full support

Number: Single (4 bytes)
Number: Double (8 bytes)

Floating Point Number

Dataverse: Fractional values up to 17 digits, but display is limited to +/- 100 billion with five decimal places, and arithmetic is approximate

Access: Four bytes
Negative values: 3.402823E38 to -1.401298E-45
Positive values: 1.401298E-45 to 3.402823E38

Access Eight bytes:
Negative values:  1.79769313486231E308 to -4.94065645841247E-324 Positive values:  4.94065645841247E-324 to 1.79769313486231E308

In comparison to the Access floating point data type, the Dataverse floating point data type can create tiny differences that might be rounded for display. This normally isn’t a problem, but during repeated calculations, you can get slightly incorrect results.

If absolute precision is required, consider using the Dataverse Decimal data type because it holds a larger range of fractional values and it preserves accuracy.

Large Number

Big Integer

Dataverse: 8 bytes, -2^63 to 2^63-1
Access: 8 bytes, -2^63 to 2^63-1

Not supported

Reviewers: Why is this not supported when it appears to be the same underlying storage?

Attachment

File

Dataverse: 128 MB, one file per column, and stored in the cloud
Access: 2 GB, multiple files per column, and stored in the database

Access doesn’t export data that exceed Dataverse limits

OLE Object

Image (or File)

Dataverse: 32 MB, supports GIF, PNG, JPG, and BMP, one image per column, and stored in the cloud
Access: 2 GB, supports images, charts, and Active X controls, and stored in the database

Unsupported

Lookup Wizard

Lookup

Dataverse: one column lookup
Access: one or more columns lookup

Access exports only a one column lookup

Yes/No

Yes/No (or Two Options)

Both Dataverse and Access use a boolean value

Full support for two options. 

No support  for multi select option set.

Calculated Columns

No equivalent

Dataverse: You can create calculations based on whole number, decimal, or text data types. For more information, see Define calculated columns in Power Apps and Create and define calculation or rollup fields in Microsoft Dataverse

Access: For more information, see Calculated Columns.

Only the results of the Access calculated columns are exported.

Multivalued fields

MultiSelect Field or Choices

Dataverse: Stores values as a delimited string in one column.

Access: Stores multivalued field stores values across multiple tables. For more information, see multivalued fields.

Unsupported

Next steps 

After a successful migration, you are ready to create a Power app of one kind or another. Browse the links provided to get a solid overview of the ever-expanding Power platform. You can even import or link to data in a pre-built Power app.

Once you finish the migration of your Access data to Dataverse, you can create an app on the Power Platform that runs on the web, a tablet, a phone, or even the desktop. The Power platform is quite extensive. The following summary of content can help you get an overview of your basic choices and target which type of app works best for you.

REVIEWERS: NAH - awaiting final set of links from your content creators which I will add when all are ready.

Component

More information

Dataverse

Why choose Microsoft Dataverse?

Get started using Dataverse

Manage permissions and administration for Dataverse

Tables in Dataverse

Manage tables in Dataverse

Table relationships overview

Columns Overview

Create and manage columns within a table in Dataverse

Working with choices in Dataverse

Teams

Get started with Microsoft Dataverse for Teams

Build your first app with Power Apps and Dataverse for Teams

Create apps, chatbots, flows, and more with Microsoft Dataverse and Teams

Model-driven apps

How to build a model-driven app

Create a model-driven application in Power Apps

Get started with model-driven apps in Power Apps

Canvas apps

How to build a canvas app

Customize a canvas app in Power Apps

Create a canvas app in Power Apps

Portals

Discover Power Apps portals features

Introduction to Power Apps portals

Get Started with Power Apps portals

Mobile

Run canvas app and model-driven apps on Power Apps mobile

Set up push notification for the Power Apps mobile app

Automation (workflows)

Get started with Power Automate

Business process automation that helps teams do more

Create a business process flow in Power Automate

Chatbots

Introduction to Power Virtual Agents

Create a business process flow in Power Automate

Create chatbots from Power Apps

Analysis (Power BI)

Get started using Power BI

Quickstart: learn about the Power BI capabilities for business users

Sample Applications

Hospital Emergency Response

Regional Government Emergency Response and Monitoring

Higher Education Crisis Financial Impact Tracker

REVIEWERS: This section will eventually become a separate but linked topic upon GA. It will resemble Link to or import data from Dynamics 365. The following is a brief version for the MVP Technical Beta.

You can link to any Dataverse table from Access, not just the ones that you migrate. Perhaps the table is already used in a pre-built Power app. Or the app is used with other products such as Excel, SharePoint, or SQL Server. You can enhance the app with Access by linking to those tables and creating forms, reports, queries, and other objects.

  1. Select External Data > New Data Source > From Online Services > From Dataverse. The Get External Data – Dataverse dialog box opens.

  2. Enter the URL of the Dataverse site.

  3. Do one of the following: 

    Import      Select Import the source data into a new table in the current database.
    Link      Select Link the data source by creating a linked table.

  4. Select OK.

  5. 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.

See Also

Introduction to importing, linking, and exporting data in Access

Manage linked tables

Power Apps Blog

Power Apps Customer Stories

Power Apps Community

Power Platform Fundamentals

Power Platform documentation

Power Platform Learning Path

Need more help?

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×