Data types and sizes for Access data migration to Dataverse

When you migrate from Microsoft Access to Microsoft Dataverse or Microsoft Dataverse for Teams, you should be aware of a few differences in the data types. These differences include supported types, data type names, and column capacity.

When you migrate, a validation will be executed to ensure:

  • The table only includes supported data types.
  • The column values in the rows being migrated don't exceed the size limits of Dataverse.

This validation is done to prevent data loss. If a table has columns that exceed the maximum column value in Dataverse, or the table contains data types not supported by Dataverse, the user will be alerted by the Access migration tool validator and will be provided additional information.

Users can choose to either cancel the migration completely, or to continue to migrate all supported content and keep the unsupported content in an Access table.

Access data types supported by Dataverse

In the following table, the data type mappings supported can assist you in planning your data migration.

Access data type Dataverse data type Can migrate?
Short Text Text Yes
Long Text Multiline text Yes
Autonumber Autonumber Yes
Date/Time Date and Time Yes
Currency Currency Yes
Number: Decimal Decimal Number Yes
Float Number:Single, Number:Double Yes1
Yes/No Yes/No Yes
Int Whole Number Yes
Lookup Wizard Lookup Yes
Multi-Value Lookups Choice Yes2
Hyperlink URL Yes
Attachment File Yes3

1Dataverse includes a float data type. However, it has lower limits than Access a min/max of +/- 100,000,000,000 or +/- 99,999,999,999.99999. A maximum of five decimal places is supported. Users will be informed of this limitation during migration and can opt out if they require greater than five decimal places.

2One column multi-value lookups only. Because of the difference in how Dataverse and Access identify these lookups, a manual process is needed in Access before migration. More information: Export multi-value lookup Access Fields to Dataverse choice columns

3 Attachments can be migrated for single attachments per column at this time.

Access data types not supported for migration to Dataverse

  • OLE Object
  • Calculated4
  • Rich Text

4When you migrate, the calculated field will migrate the last calculated value into a column. Users will need to configure new calculation columns in Dataverse. More information: Define calculated columns to automate calculations

Access and Dataverse data size comparison

You'll notice some Dataverse columns don't have the same size capacity as Access. As noted above, if a column contains data too large to be migrated, the migration tool alerts the user that the contents can't be migrated. This is to prevent data loss. This decision isn't based on the maximum possible size for the column, but rather the size of the actual data in each row.

Access/Dataverse data type Access limit Dataverse limit
Short Text/Text 255 characters 4,000 characters
Long Text/Multiline Text 1 GB 1,048,576 characters
Autonumber 2,147,483,647 4,000 characters
Date and Time Standard date and time Standard data and time
Currency5 Min/max -922,337,203,685,477/+922,337,203,685,477 Min/max -922,337,203,685,477/+922,337,203,685,477
Decimal Number Min/max -10^28-1/+10^28-1 up to 28 decimals Min/max -100,000,000,000/+100,000,000,000 up to 10 decimal places
Float (Number:Single) -3.402823E38 to -1.401298E-45 for negative values and 1.401298E-45 to 3.402823E38 for positive values. Min/max -100,000,000,000/+100,000,000,000 up to five decimal places
Float (Number:Double) -1.79769313486231E308 to -4.94065645841247E-324 for negative values and 4.94065645841247E-324 to 1.79769313486231E308 for positive values. Min/max -100,000,000,000/+100,000,000,000 up to five decimal places
Yes/No Boolean Boolean
Int/Whole Number Min/max -2^31/+2^31 Min/max -2,147,483,647/+2,147,483,647
Lookup Wizard/ Lookup Multiple column return Single column return

5The migration tool assumes the currency coming from Access is the Dataverse base currency.

Calculated fields in Access will currently create a column for the content type in Dataverse that stores the calculated value. This could be text, whole number, decimal, and so on. Dataverse can be used to create calculated fields to enable calculations.

Export multi-value lookup Access Fields to Dataverse choice columns

Both Access and Dataverse offer users the ability to provide a list of multiple values a user can select from in a row. The ways these are implemented are different.

Access uses a multi-value lookup, which is a lookup that allows the user to enter any number of values, which will then be presented as a dropdown list for selection. Access has the ability to have more than one column of values for this function.

Dataverse uses choice columns, which are an enumerated list of values that each have a string label associated to them. Users locate and choose the values in a dropdown list using the label values that are stored in the background as an enum selection with a relationship to the table where the labels are stored.

Because of these differences, migrating multi-value lookup columns from Access into Dataverse presents some challenges. The following process must be followed to migrate:

  1. Access can only start with a single column multi-value lookup.
  2. Access users must add a new column to the multi-value lookup to act as the enum value expected by Dataverse.
  3. At export, Dataverse will store this as two columns, which allows both Dataverse and Access to use the lookup appropriately in forms.

Creating a valid choice field for export to Dataverse

To successfully migrate a choice field from Access, the field must be created in a manner that is similar to the steps described here.

  1. Create a new table in Access.

  2. Add a Number field to the table. It must be a Number field to support export to Dataverse.

  3. Go to Design View in Access. Select the new field, and then select Lookup Wizard.

    Select Lookup Wizard in Access

  4. In the Lookup Wizard, select the I will type in the values that I want option, and then select Next.

  5. In the next dialog, enter 2 for the number of columns and then select the field below Col1.

  6. In Col1 enter values for three rows by entering 1, 2, and 3. In Col2 enter values for three rows by entering red, green, and blue.

    Create two columns with three rows of data each

  7. Select the separator between Col1 and Col2 and slide it to the left so that only Col2 appears.

    Move the slider between the columns to the left

  8. Select Next > Next > Finish to complete the Lookup Wizard.

  9. Save your changes and return to the Datasheet View.

  10. Create a new record to validate the choice field works as expected. It should appear like this if it was configured properly.

    How the choice field should appear in Access

  11. Close the table and migrate it to Dataverse.

  12. If everything worked properly, the choice column in Dataverse will appear like this when adding a new record.

    How the choice column should appear in Dataverse after migration from Access

Migrate Number:Single and Number:Double columns to Dataverse

Both Access and Dataverse include the ability to store floating point numbers. Access uses Number:Single and Number:Double for this. These data types are often used for any number column. Dataverse has a Floating Point Number data type, but it has some limitations with how it's implemented. Dataverse only allows a maximum of five decimal places with a minimum/maximum value of -/+ 100,000,000,000. When migrating, the validator informs you that by migrating this data as a float you'll lose some decimal precision. If that's acceptable, you can migrate the tables automatically. During the migration process any rows that exceed the min/max size limits won't be migrated and will be stored locally in a file in Access just as with any other out of range data type.

However, if you need to migrate but don't want to lose decimal precision, you can migrate Access Number:Single and Number:Double by changing the data type in Access. You can use the Access table designer and change the type of Number:Single and Number:Double columns to Decimal, which support up to 10 decimal places in Dataverse. Then any row that doesn't exceed the minimum/maximum decimal limits can be migrated.

See also

Migrate Microsoft Access data to Microsoft Dataverse