Comparing Access and Dataverse data types
Applies To
Access for Microsoft 365
| Note This feature is in General Availability (GA) in the Microsoft 365 Monthly Release Channel as of May 2022. If you are on the Microsoft Semi-annual Enterprise Channel, it is available in December 2022. For more information on how to participate, see aka.ms/AccessAndPowerPlatform. |
|---|
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 characters Access: 255 characters |
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 characters Access: 1 GB with a display limit of 64,000 |
Access prevents export if the Dataverse limit is exceeded. |
| Hyperlink | URL | Dataverse: 4000 characters Access: 8,192 characters (including the URL, display name, and tool tip parts) |
Whether the Access URL is just a simple URL or it has one or more URL parts, it is converted to one column in Dataverse. |
| 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. | Not supported |
| 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 Access: +/- 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. Alternatively, You can import the Currency system table (which has the exchange rate and base tables for every currency) from Dataverse to Access. For more information, see Dataverse system tables and columns. |
| 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 |
Not supported Tip To migrate an Access floating point data type, 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. 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 (BigInt) | Dataverse: 8 bytes, -2^63 to 2^63-1 Access: 8 bytes, -2^63 to 2^63-1 |
Full support |
| 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 |
Not supported yet, but coming soon. Watch for updates. |
| 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 |
Not supported |
| Lookup Wizard | Lookup | Dataverse: one column lookup Access: one or more columns lookup |
Supported Access exports only a one column lookup which requires preparation. For more information, see Create a choice field for migration to Dataverse. |
| 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. |
Not supported |
| Rich Text | Text or Multi-line text | Dataverse: Text supports up to 4000 characters and Multi-line text supports up to 1,048,576 characters with formatting. Access: A Long Text data type (also called Memo) field with the TextFormat property set to RichText. |
Not supported |
| Hyperlinks | URL | Dataverse: Only displays the URL text Access: Besides the URL text, Access has built-in components that include the friendly name and displayed name of the link. |
Supported Hyperlink components for the friendly name and display name are created as additional columns in Dataverse. These will continue to work as expected in Access forms and reports. |