Get started: Migrate Access data to Dataverse

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

2.  Make sure you are signed into Access with the same credentials you use in Power Apps. Depending on your environment, you may need to enter additional security information, such as a secondary authentication or a pin.
   
   Note    If you don’t use the same account for Access and Power Apps, migration fails due to permissions issues.

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 all the tables you want to export and clear all the tables you don’t want to export.

6. Select OK.
   
   

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

1. To export related tables of the selected tables, select Export all related tables.
   
   Note    At this time, only direct child tables are included when you choose to export related tables. If you would like all relationships to be included, use the Export Objects dialog box to select all the tables you want to migrate.

2. To view these tables, select See Related Tables. 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.
   
   

   Note: In Access you can create a one-to-many relationship and the foreign key field in the referenced table can be any field. But in Dataverse, the foreign key field in the referenced table must be the primary key of the referenced table. If the Access foreign key is not a primary key in the referenced table, the export operation to Dataverse fails. To successfully export the data, make sure that the Access foreign key is the primary key in the referenced table.

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

4. To preview the results, select Open the Dataverse table URL when finished.
   
   This option is not available if you are exporting to Dataverse in Teams.

5. To explicitly set a field as the primary name column, select Select primary name fields, and then choose a field from the drop-down list. 
   
   If you don't specify a column to use, the first text column from the left is used as the Primary Name column. If there is no text column, a primary name placeholder column is created with null values.
   
   

6. 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 Dataverse system tables are created and linked in Access.

During the validation process, if there are unsupported fields, Access identifies unsupported data types or rows that exceed limits and creates a Dataverse column validation errors table which stores in each row the exported table name and the fields that were unsupported. You can choose to migrate all supported data types and choose not to migrate non-supported data types. If you choose not to migrate, the data remains in Access.

What happens to unsupported data types and exceeded limits

If you chose to link tables, confirm that the results are what you expected. Watch for this message "Retrieving list of tables from the Dataverse environment". 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 system 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.

• Currency   Currency details for all transaction currencies including values like exchange rate, name, symbol, code, and so on. This table only appears if Access has a currency data type that you have migrated to Dataverse.

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. For more information, see Dataverse system tables and columns. 

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.

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>. This is called the logical name in Dataverse.

• 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. you can also set a specific field as a primary name. This column is visible in Access and Dataverse.

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

Once you have migrated Access tables to Dataverse, you can view the tables and columns in Dataverse and Access at any time. Note that in Dataverse, fields are displayed in alphabetical order, not the original order in Access.

Dataverse/Power Apps       For information on viewing and working with tables in Dataverse see Edit a table in Power Apps.

Note     Right clicking  a migrated, linked table in Access opens the table in the Power Apps Maker portal in your browser.  In the default view of the Power Apps portal, systems fields are not displayed, providing a simpler view that shows the Access migrated columns.

Dataverse for Teams       There are several ways to view the tables and columns:

• Create a quick Power App to view or update the data.

• In the build menu, Select See All in the “Items created for...” panel and then click on the table you wish to view.

• Use the Table Designer in Teams. For more information, see Create a table in Teams.

Tip      You might find it useful to bookmark the URL address of the table view you prefer in the browser.

Tip      You can also view the data in Excel by selecting the Data drop-down command, and then selecting Edit in Excel. For more information, see Edit table data in Excel. 

For more information, see Dataverse system tables and columns. 

The following sections provide additional overview and conceptual information.

Understanding primary keys and primary names

Primary Keys are used to identify the rows of data in a database. Access and Dataverse have different key structures. During migration, Dataverse and Access automatically manage coordinating between the two products with their own unique keys and with no user interaction.

During the planning and the process of migration, there are two primary keys and one additional Primary field to be aware of:

Access AutoNumber      Access often uses the AutoNumber data type, which auto-increments a unique number, as a primary key for table. This field is created in Dataverse as a whole number field with the autonumber format applied. During migration, the starting number in Dataverse is set to be the next sequential number based on the Access rows. Access continues to use the AutoNumber column to identify the rows of data, even though Dataverse uses a different key.

Important      Dataverse creates the Autonumber field as optional, but this is by design. Dataverse generates its sequential number when the record is saved. If this value is set to required then Dataverse clients will require a value to be entered prior to saving, which prevents autonumber generation. If the field is left blank, an autonumber is always generated.

Dataverse GUID   Dataverse uses a Globally Unique Identifier (GUID) data type as its primary key, which is automatically created as a unique identifier, and required in each table. Dataverse doesn’t require imported data to have a primary key, it automatically creates a GUID for you.

Dataverse Primary Name   Dataverse also includes another column called the Primary Name. This is a friendly name used to identify rows as an alternative to using a GUID. It is required that this field be present, and depending on the settings in Power Platform clients, may require this to column to be populated. You can manually change this requirement. Primary Name is not unique by default. 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.

Guidance for decisions about primary keys

The Access primary key, the Dataverse primary key, and the Primary Name column are created automatically by default. But you can make choices about how to handle the three primary keys. Here is some guidance to help you decide:

Primary key       If an Access primary key (AutoNumber) is also a business key, such as a part number, then you probably  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).

Notes

• Sometimes an Access table contains an AutoNumber column that is not used as a primary key. In this case, Dataverse converts it to a Whole Number field and the ability to auto-number is removed.

• Dataverse also has the equivalent of an Access AutoNumber field, called the AutoNumber field, which is used to generate sequential numbers and is stored as a string value. This field can also include a prefix (such as a string or a date) to the number allowing for flexibility in how the field can be used. This column is based on the SQL Server auto-sequence data type. For more information, see Autonumber columns in Microsoft Dataverse.

Primary name      Dataverse requires the existence of a Primary Name column. A Primary Name must contain string values. During migration, users can specify which text column in Access they want to use as the Primary Name. If a user does not specify a column to use, the first text column from the left is used as the Primary Name column. If no text column is present, a new column will be added named Primary Name Placeholder which will meet the Dataverse requirement. This column is set to Optional and can be left unpopulated.

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  (or any column that can be indexed)	GUID	String
Contents	A simple, sequential number	Randomized numbers and letters unique to the environment	Human-readable string values
Example	234	123e4567-e89b-12d3-a456-426655440000	A name, such as "Jones". A whole number, such as 234, or a combination such as SalesOrder9071
Requirement	Often used but not required	Every table must have one	Every table must have one
Modifiable	An Autonumber field can't be modified, other field types can be modified	No	Yes
Unique identifier	Always	Always	Only as an Alternate Key
Compound key	Supported and often combined with AutoNumber	Not supported	Not supported
Visibility	Usually, the first table column	Usually hidden but can be displayed.	Defaults to being visible
Indexing	Can be indexed. For more information, see Create and use an index to improve performance.	Automatically indexed	Automatically indexed
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.

Notes 

• Access indexes are not automatically converted to Dataverse indexes.

• Under the covers, Dataverse uses Azure SQL Server to automatically create indexes based on data usage patterns. Indexes for primary and alternate keys are always created and others will be added or removed based on how you use your data.

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. If the Access database has a relationship diagram, it is preserved. However, the original local tables are replaced with new linked tables.

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. The Orders table in Access has a foreign key that holds values from the Access primary key in the Customers table.

• During the export operation, both Access primary keys are preserved and maintained as integer AutoNumber columns but are these are not used as the primary key column in the Dataverse table. Each table has a new GUID column added which becomes the primary key in the Dataverse table.

• A lookup is created for the CustomerID foreign key in the Orders table to find the matching CustomerID primary key in the Customers table. Once the tables are migrated to Dataverse, if you modify the foreign key lookup column newly added to the Orders table, it will also update the original foreign key column. Your Access application can continue to use the Access foreign key, instead of the Dataverse foreign key. If the Access foreign key is modified in the linked table in Access, it will also update the Dataverse foreign key. However, the Access foreign key will be read-only in Dataverse.

• 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 and Create a relationship between tables by using a lookup column.

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      The default scenario is to keep the original Access primary key (AutoNumber), but it is not used as the primary key in Dataverse. You now rely on the Dataverse primary key (GUID) and use it in the Dataverse table as a unique identifier. Access creates linked tables to preserve the Access relationships. Note that you can’t delete the Dataverse primary key (GUID).

In a relationship, you can edit the foreign key values (either in Access or in the Dataverse environment). You can also edit foreign keys that refer to the Access primary key in an Access linked table, and it will automatically update the Dataverse foreign key that references the Dataverse primary key in the foreign table.

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 during the process of migration.