Why the Dataverse connector?

Brass Contributor

Can anyone explain to me why Microsoft added a Dataverse connector to Access when you can't share an Access DB using Dataverse tables? I would love to know the logic on that piece. 

16 Replies
> can't share
You mean such apps are single-user?
Have you seen this: https://powerusers.microsoft.com/t5/Building-Power-Apps/How-to-control-app-and-Dataverse-access/td-p...

Hi,

 

I'm not sure what you mean by "can't share an Access DB using Dataverse tables".

 

You should be able to share it with other users. The users must be members of the tenant and environment where the Dataverse tables live and have the right permissions set. I see no difference in these requirements for Access compared to other frontends and users for Dataverse like Power Apps etc.

 

Servus
Karl
****************
Access Bug Trackers
Access News
Access DevCon

Access-Entwickler-Konferenz AEK

@Karl_Donaubauer 

 

I think I found the error.. Users cannot use an encrypted Access front .accde file which renders the dataverse like a read only file. I am testing a new solution. It looks like users need the full .accdb file instead. The downside is that I have install the full file with all the local tables and dataverse tables, so dataverse clearly isn't a viable datasouce for a networked Access environment for me. I was hoping DV would actually replace the need for local SQL server machine, but doesn't seem to be the case. 

Hi Tom,, I am trying to share an Access front .accde file (app) using Dataverse tables as the backend. Not a Power App. Unfortunately, users cannot access the DV tables because the .accde file renders them as read only. The huge error with the Access DV connector is that it won't map to a DV solution, which is probably why I can't share the DV tables as read/write tables. In any case, as I just stated here, it looks like I will have to go old school and build a local SQl server machine. Dataverse is simple not a viable solution for Access, which is why I asked the question, "Why the Dataverse Connecter?" It's an incomplete solution apparently implemented by people who probably never used or developed an Access DB.

"The huge error with the Access DV connector is that it won't map to a DV solution" What does that mean in this context? "won't map to a Dataverse solution"? If you mean what I think you mean, it's not accurate. I actually have an Access accdb linked to Dataverse tables in a solution.

GeorgeHepworth_0-1694196431231.png

 

 

 

But perhaps you mean something entirely different?

@George_Hepworth 

 

I take that back. I actually have two accdbs linked to Dataverse tables in two different Solutions.

 

 

I have not found and way to export Access tables to a Solution. I tried several times and the tables only export to my environment. I am thinking you have a different subscription than I do. My organization has a 365 Business Premium sub, but apparently, we need an Enterprise sub for full SharePoint & Dataverse capabilities. No budget for that. I tried using Sharepoint lists as a temporary measure, but I am having the same issues. Appearently, Access Combo box won't write to a SP List of Davaverse. So, I ask again, what is both connectors? 

 

I was told that SP Lists and DV can't be used as data sources for Access. Now I clearly see that they were correct. Hoping I get budget approval for an SQL server soon. In any case, dataverse is a far too a complicated platform. It requires heavy IT support. I am an Access developer, not an IT engineer - not suitable for non-IT developers.

"I was told that SP Lists and DV can't be used as data sources for Access"

That is flat out inaccurate.

We've been using SharePoint lists with Access since at least 2003. Access Web Databases, released in Access 2007, used ONLY SharePoint lists. In other words, the exact opposite is true. SharePoint lists work just fine. And so do Dataverse tables.

It may well be that Dataverse is too demanding for your needs, though. That's different from "can't be used".

What VBA code are you using to get SP lists to work? I would show you a screen shot but I am on a windows 11 that doesn't support screen captures.
What do you mean by that question? Linked SharePoint lists are similar to local Access tables. One need not actually run any VBA code to use them. What kinds of things do not work in your environment? Please be specific.

I modeled a DB in access then exported the tables first to dataverse. I created a .accde file for the queries and forms and installed it on a user's desktop. When the user attempted to use the front-end form, Access declared that the tables were read only and could not be opened. The user has admin permissions to open the DV tables, but they still cannot open them. It is obviously an error on Microsoft's side, so I asked for my IT person to address the issue. I read this on other forums that dataverse is not a good data source for Access while others claim it is. Very confusing.

Next, I copied the original Access DB and exported the tables to Sharepoint. The forms directly linked to a single SP Lists work but the forms with Combo boxes are problematic. One combo box selects a name from Table 1 and another combo box selects different name from Table 2. Both names are written to a third Table 3. My original Access DB with native tables shows the names selected from the combo boxes in local Table 3. However, the exported Table 3 only shows the PK's in the designated columns. So, while the forms do show the names, the SP list don't. The problem with this is that I don't know how to create a mobile Power App that will display names and not PK's.

I could have already built a solid DB already if I had an SQL server, which I am hoping my organization will furnish.

Is it possible to write to SP lists via combo boxes in SP? I don't think exporting tables from Access works. I suspect I need to create SP lists and then connect them to Access to create forms and queries This all very confusing and counter intuitive to Access logic.

I will post screen shots Monday when I return to my Windows 10 machine.

Thanks for your help.

@Rexter 

"It is obviously an error on Microsoft's side, so I asked for my IT person to address the issue" I don't think that is obvious at all. This being the first report on such a problem in any of the forums I frequent, it bears validation before reaching a conclusion.

 

I suspect your problem with combo boxes might have more to do with the design of those combo boxes and the way the fields involved were created. We'd have to see the tables involved to figure out what is really going on. I would risk a small wager that your original Access tables used Lookup fields in those cases where you are confused by the situation you ended up with.

 

In the previous post, you commented about VBA, yet nothing in this latest response addresses my request for clarification on what that might involve.

 

With regard to claims made by various individuals about any subject, it's actually pretty common to run into differences of opinion. What really matters is the reasoning behind those opinions, not the mere expression of the opinion. Wisdom dictates that we probably shouldn't state our opinions or beliefs as facts too readily, one way or the other. 

 

 

I also found time today to verify the statement that converting an accdb linked to Dataverse tables to an accde rendered them read-only. That proved not to be the case in the accdb I just converted. No doubt there's another issue behind the problem, but I don't believe it is actually the fact that the linked tables are in Dataverse.

I am not sure why your .accdb link works when mine doesn't. Are you sharing these front-end Access files with other users? Or are you just using for your own private database? In any case, I am having an awful experience using SharePoint lists for Access. One of the SP lists does not sync up to Access while the others do. No rhyme or reason for this when I used the same process for the other tables. (I rebuilt all 5 tables in SharePoint and then connected them in Access). All the Sp lists were imported / connected to Access and they all sync up but one. My takeaway is that SP won't work for my purposes. And it appears that Dataverse won't either. Microsoft cobbled SP and DV together from 1980's technology, trying to patch it into today's modern SaaS & SaaP environments. It just does not work well.

What does it mean to say "one of the SP lists does not sync up to Access"? Please elaborate on what you do see. "Synch up" is not a commonly used term in this context, so before trying to guess what your problem is, we ought to know exactly what you are experiencing.

"Microsoft cobbled SP and DV together from 1980's technology"

Really? Do you honestly believe that SharePoint and Dataverse predate the internet and Windows? It's statements like that which make it increasingly difficult to lend credence to other claims.

What I mean by sync is that the sharepoint list is not updated from the respective Access form. As for the 1980's tech, I meant to say 1990's tech. I have coded websites from the ground up. From this perspective, SharePoint sites are lacking the basics of modern website features, especially when it comes to file and document management. The inability to customize where SPLists are uploaded is one example of lackluster design. Anyway, I'm moving on. I have the budget to build an SQL server. But I do appreciate your input.