Data types and sizes for Access data migration to Dataverse

Article
01/26/2023

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.

Note

Learn how to prepare MS Access for Migration

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	Yes¹
Yes/No	Yes/No	Yes
Int	Whole Number	Yes
Lookup Wizard	Lookup	Yes
Multi-Value Lookups	Choice	Yes²
Hyperlink	URL	Yes
Attachment	File	Yes³

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

²One 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

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

Access data types not supported for migration to Dataverse

OLE Object
Calculated⁴
Rich Text

⁴When 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
Currency⁵	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

⁵The 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:

Access can only start with a single column multi-value lookup.
Access users must add a new column to the multi-value lookup to act as the enum value expected by Dataverse.
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.

Create a new table in Access.
Add a Number field to the table. It must be a Number field to support export to Dataverse.
Go to Design View in Access. Select the new field, and then select Lookup Wizard.
In the Lookup Wizard, select the I will type in the values that I want option, and then select Next.
In the next dialog, enter 2 for the number of columns and then select the field below Col1.
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.
Select the separator between Col1 and Col2 and slide it to the left so that only Col2 appears.
Select Next > Next > Finish to complete the Lookup Wizard.
Save your changes and return to the Datasheet View.
Create a new record to validate the choice field works as expected. It should appear like this if it was configured properly.
Close the table and migrate it to Dataverse.
If everything worked properly, the choice column in Dataverse will appear like this when adding a new record.

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.

Share via