In the past few days, I’ve posted a few articles:

about Microsoft’s latest new ‘feature’ for Access the Dataverse connector.

Yesterday, I was made aware of the following YouTube recording of a Denver Area Users Group (DAAUG) session with Michael Aldridge, the new Microsoft Access Program Manager, dedicated entirely to the new Dataverse connector.

I thought based on the above video and a few other sources of information I’d try to share what we specifically know about this latest update.

So What Is Dataverse Exactly

I’m no expert on this subject, but from my understanding/experience, it is Microsoft’s attempt at a low-code/no-code cloud based database where anyone can be a DBA. From what I’ve been told, it is itself running off of Azure.

From an Access developer’s perspective, it is a cloud database. By using such a migration you can then share out a database to remote users, build mobile tools (Power Apps, Power BI, Power Automate – Flows, …).
 

What Is The Dataverse Connector

This is a new connector that was developed to facilitate the migration of tables from Access to Dataverse. The connector fully automates the migration process, so that in a few clicks, the data is moved and you need not do anything! It (re)creates the tables, migrates the data, then creates the necessary linked tables within Access so you can continue to use Access, but connected to the cloud based Dataverse data.

Licensing

There are two different licenses required depending on your objective.

To Build Apps for Teams

Requires a Dataverse (Teams version) license which is included “free” with the E3 or E5 plans, as well as all MS365 plans that include Power Apps. The Dataverse for Teams has a limit of 2GB or 1 million records, whichever is attained first.

You can find more information regarding Dataverse for Teams by reviewing Power Platform Licensing Guide_November 2021_ FINAL PUB v2.pdf and specifically reviewing page 29.

To Build ‘Mobile Apps’

Require a Dataverse (Full version) which is a separate Power Apps or Dynamics 365 license and permits you to create mobile apps (Power Apps) and has a limit of 10GB.

So What’s The Difference Between The Two?!

If you want to dig into the differences between the two licenses/solutions then take a look at Dataverse for Teams vs. Dataverse – Power Apps | Microsoft Docs.
 

Data Migration

As mention above, the Access/Dataverse Teams have tried to make the migration process as painless as possible. It is done through a wizard, so in a few clicks of a mouse the data is completely transfered for you and the necessary linked tables are created in Access so you are able to continue to work. It is important to comprehend the Migration simply moves the back-end to the cloud, in this instance Microsoft’s Dataverse, and Access continues to be full functional. The migration’s objective is to open new doors, Teams Apps, Power Apps, Flows, …

Another critical aspect to understand is that once data is migrated to Dataverse, any schema modifications must be done via Dataverse (through the web consoles), the tables become linked tables in Access and are no longer managed from within Access itself. Furthermore, after any design changes are made on Dataverse, Access’ Linked Tables must be refreshed to reflect these changes.

Lastly, the migration process leaves the original tables as they were. So the original structure, data, is left intact and new linked tables are created.

Migration Limitations

It is important to understand that there are a few limitations, I suppose in compatibility, between Access and Dataverse which impact the migration capabilities.

There happen to be differences in maximum allowable values in data types like: Long Text, Decimals, Integers, Lookups and thus in most cases there will be no issue, but in fridge cases data may not be imported.

Attachments are also a headache presently as the connector can only import 1 attachment per record/field. So if a record has an attachment field with 5 attachment, only 1 get imported presently.

There is also major limitations with Calculate fields, there values are imported, but the formulas are not!

OLE data is not supported.

M to M relationships are not supported

…

Based on a couple demos I have seen the migration process is ‘relatively’ slow and can even appear to have become unresponsive, but this is normally a one off process and it is fully automated and still much faster than if you had to do so yourself manually.

Different Environments

Most developers commonly have 2 or 3 development environments: Development, Testing, Production. The Dataverse Migration process only creates a single environment. If you need different environments, you will need to create each one manually. Also, there doesn’t seem to be any way currently to script changes between Dataverse databases/environments so changes in schema will need to be manually performed in each environment.

Dataverse Driving Apps and Access

One important thing to grasp if you do create apps, use Power Automate (Flow) to insert/update/delete data, … is that these changes are reflected in Access when the tables are active opened in Access as the data is cached of the time the table was opened/accessed. So it becomes important to Refresh/Requery data sources/forms/… on a regular basis to see any changes in real-time.

The Dev Team has stated they are in fact looking into what might be possible to automate refreshing the data. That said, you can either make your users aware of this and provide a command button to refresh the data manually at their convenience or perhaps use a Timer event to automatically do so.
 

Dataverse vs. Azure SQL

So what’s the big difference between Dataverse and Azure SQL.

To tell you the truth, this is the one point that I find Microsoft has completely not clearly defined to me as a developer!

What has been said is that Dataverse is low-code/no-code, whereas Azure SQL has a higher learning curve. With DataVerse a few click and everything is done for you and you are operational.

Migrating to Azure SQL already allowed for the above, Teams Apps, Power Apps, Power BI, Power Automate – Flow, … and has tools like SSMS, Azure Data Studio to allow you to work with it, make design changes. You can script environment changes, manage security, …

Azure SQL offer far more control and I truly, to this day, have yet to see any real benefit to using DataVerse over Azure SQL. Actually, with DataVerse I have found things harder to manage.

I hope Microsoft provides more information about the differentiation factors between the two and also highlight where the two overlap as it is important to know what Dataverse can do just like Azure SQL and what it cannot.

That said, I have asked this question multiple times now and no one has offered any information on this front!
 

A Few Resources on the Subject

• Access Session At Microsoft Ignite, Yes It Is True!
• Meet Michael Aldridge And The New Dataverse Connector.
• The Future of Access: Our strategy and public roadmap updates for 2021/22 – YouTube DAAUG Session
• Migrate Microsoft Access data to Microsoft Dataverse – Power Apps | Microsoft Docs
• About the Microsoft Dataverse for Teams environment – Power Platform | Microsoft Docs
• Enhancing the Benefits of Microsoft Access in the Cloud with the Power Platform – announcement from the Access Dev Team
• Access Dataverse Connector – Access MVP Mike Wolfe’s take on the Dataverse Connector