Thursday, April 28, 2022

Migrate Access data to Dataverse [MS]

Note    This feature is in public beta and only available to Insiders. For more information on how to participate, see aka.ms/AccessAndPowerPlatform.

The combination of Microsoft Access and Microsoft Dataverse with Power Platform opens a world of opportunities for the citizen developer in the cloud and for mobile scenarios. This combination unlocks hybrid solution scenarios that keep the value and ease of use of Access together with cloud-based storage in the following ways.

  • Mobile and Microsoft Teams scenarios.

  • Real-time sharing and editing of Access data using cloud-based data stores and front-end apps created in Access, Power Apps mobile and Microsoft Teams.

  • New security and compliance capabilities through Dataverse storage in the cloud using AAD, and role-based security while managing it from Access.

A visual of what you can do between Access and Dataverse

You can share Access data with Dataverse, which is a cloud database upon which you can build Power Platform apps, Automate workflows, Virtual Agents, and more for the web, phone, or tablet in the following ways:    

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

  • Migrate Access data to 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 App, 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.

Dataverse is the backbone of 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? The following sections provide more information.

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

Here is a quick checklist to make sure you are ready:

  • Enroll in the public beta. For more information, see aka.ms/AccessAndPowerPlatform.

  • If you do not already have one, obtain a Power Apps license which includes Dataverse or use an existing environment. Go to the Power Apps home page, and select Start Free.

  • If your Access data has a Multivalued field prepare it for migration. For more information, see Create a choice field for migration to Dataverse.

  • Although Dataverse has a floating-point data type, it has lower range limits than the Access floating-point data type. If your Access data has a floating-point field, convert it first to a Number data type, set the Field Size property to Decimal, and then migrate the data to Dataverse, which will store it as a Decimal Number data type.

Make sure you obtain the necessary licenses:

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 and Updates coming to Power Apps.

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.

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

If you are using a new environment:

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

  2. Select Settings  > Admin Center.

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

    Creating a new Dataverse environment

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

  5. In the Type box, select Trial from the drop-down list. If you have your own production environment, you can select Production.

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

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

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

  9. 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 Start Now.

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

Result

Your environment is ready to use. 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  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.

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

    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.

    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.

    A list of 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.

    Selecting a specific primary name field

  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

The process of validating Access data being migrated to Dataverse

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.

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

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.

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.

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.

Category

More information

Dataverse Videos

Adding users to Dataverse

Administer Application Users, Security roles, Teams, and Users in the Power Platform admin center

Assigning security roles in the Power Platform admin center

Choosing the right number type in Dataverse and Dataverse for Teams

Create a Quick App in Dataverse for Teams using Dataverse Data

Create Columns Dataverse for Teams

Create Columns in Dataverse

Create Tables in Dataverse – Part 1

Create Tables in Dataverse – Part 2

Dataverse and Dataverse for Teams Text Types

Dataverse Data Types

Dataverse for Teams Data Types Overview

Dataverse for Teams quick table and column in Table designer

Dataverse Quick Create Column

Quickly Create an app in Power Apps using a Dataverse Table

Quickly Create Dataverse for Teams Tables in the Design Hub

Quickly Create Tables in Dataverse

Dataverse help topics

Why choose Microsoft Dataverse?

Get started using Dataverse

Overview of Dataverse for Teams

How are Dataverse and Dataverse for Teams Different?

Get started with Microsoft Dataverse for Teams

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

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

Build your first app with Power Apps and Dataverse for 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

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

No comments:

Post a Comment

Search This Blog

Remove the OneNote badge from the Android homescreen [MS]

Remove the OneNote badge from the Android homescreen OneNote for Android ...