Share via

Dataverse solution for Access

Anonymous
2023-08-31T17:26:55+00:00

I am attempting to use Access to develop cloud and mobile apps. I exported my Access tables to Dataverse to use DV as the data source for my front end database (via .accde file). There is some latency but the app works ok. Unfortunately, this is where the process ends as DV does not support forms for relational databases. The same goes for Power Apps. Is there a tool out there that can transform Dataverse tables imported from Access into cloud apps or should I abandon this idea and build on an SQL server?

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. George Hepworth 22,765 Reputation points Volunteer Moderator
    2023-08-31T20:37:38+00:00

    I am not sure what you mean here. Once you link Access to Dataverse tables, they operate very much like native Access tables would, or SQL Azure tables, etc. I've created multiple forms based on Dataverse tables which work every bit as they would if they were relational Access tables. You can find a video of a presentation on themhere.

    It is true that Dataverse considers one of the other fields in the table "the primary Key" not an autonumber PK previously designated in Access. It is not possible, I'm afraid, to provide a full tutorial in this context, so I'm doing to suggest you look at a couple of YouTube videos, starting with this one

    and this one on PowerApps with SQL Server, from an Access developer.

    "To do so would require creating a query from the tables and then a form from the query."

    That's true for Access local tables, as well, is it not?

    "Dataverse shows the Primary and Foreign Keys but not the names of the customers or the names of the orders."

    Again, that is how a properly designed set of relational tables in Access works, so the problem here is unclear.

    At the moment, I'm creating a full featured library catalog application for a small local foundation. It is linked to SQL Server tables (fully relational), but it could have been Dataverse tables, just as easily. I'll produce a series of videos documenting it when I get through the data entry in a couple of months. It's all being done remotely, by the way, which is one huge advantage of this technology.

    In short, I think the problem may have more to do with less information being available about how Dataverse works, than with any shortcomings in the architecture. I agree fully that DV tables are more challenging conceptually. So, I urge you to dig deeper into the way Dataverse tables implement things and how you can exploit them.

    All of that said, if I had to choose between SQL Azure and Dataverse, I'd come down on the side of SQL almost every time. But Dataverse tables do work properly in a relational design, and moreover, they offer the advantage of off-line synching. If that's a key requirement for an application, you can't beat Dataverse.

    3 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. George Hepworth 22,765 Reputation points Volunteer Moderator
    2023-09-07T16:19:29+00:00

    I think we're in agreement on almost everything. The app I'm creating for this library would be unusable on a phone. My first testers refused to use it on a phone, as a matter of fact. On a tablet, it's quite comfortable, comparable to a laptop for the most part.

    I envision single use apps, with very limited features, as the target for phone sized apps.

    I have to admit that I'm at a bit of a loss about the non-customizability issue. I see it this way: If you create a model-driven app, you are going for dead-simple, cookie-cutter apps anyway. If you need and want customization, canvas apps are the path. So, while it may be true, it's not a deal-breaker.

    2 people found this answer helpful.
    0 comments

12 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. George Hepworth 22,765 Reputation points Volunteer Moderator
    2023-09-07T03:31:39+00:00

    There is one HUGE advantage to having any application which runs on a smart device, i.e. a tablet or phone. That advantage is freedom from the need to have a wired or wireless network connection to work.

    However, that advantage only comes to the fore if you actually have a function which requires data entry from remote locations, like a job site, a warehouse, or even a client's office. If your users are stationed on-remises, or have DSP connectivity to their office, Access is the premier option.

    For that reason, the real strength of PowerApps is in supporting those functions which require the ability to work while walking around in the open.

    Observations on some of the other points.

    I think it's a mistake to get bogged down worrying about a table GUID. I can't think of a time when I needed to know it or use it directly. I've only worked with tables in the same way I would other tables. But if that's a stumbling block, then there's no point pursuing Dataverse.

    "There is also zero customization for a Model app forms." I don't think that is a supportable claim, but most of my development in PowerApps has been with canvas apps. And I guess it depends, in part, what one would consider "customization". Model Apps are indeed intended to be quick, simple, low-code apps for the simplest of applications. There are numerous templates, which reflect that fact. On the other hand, if you do advance to Canvas apps, a lot of creativity is open.

    I had never heard of Kintone, which isn't surprising. There are a multitude of smaller, more agile alternatives like them. (They also come and they go.) Kintone claims to have over 30,000 customers. With multiple users at each, I imagine, so they could have more than 150,000 users based on their minimum 5 user licensing. Compare that to over 7 million active Power Platform users. The report I found offered no break-out by app, so that would include Power BI as well as other related apps. Still, even 10% of that 7 million would be substantially more than 150,000. FWIW, that comparison suggests you're placing a bet on an uncertain future.

    Kintone pricing is actually a bit sobering: $24 user/month with a minimum of 5 users, or $120/month even if you are a single developer starting out.

    PowerApps pricing seems very competitive with that, although I've never thought of it as an economical solution for smaller organizations. A single user, like myself, pays $25/month plus tax, for example, with no minimums. And there are other options that can be as low as $6/user/month for a single app. I have always considered that a bit of challenge for many smaller organizations. That's why a $120/month minimum is an eye-opener.

    In the end, though, one goes with whatever technology fits best with their organization's needs and goals. If Kintone does that for your organization, it's a good choice.

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2023-08-31T18:10:42+00:00

    Technically, you are correct. The DV related tables does work, but it is not possible to create a form from the related tables. To do so would require creating a query from the tables and then a form from the query. For example. Let's say I have a Customer table relating to an Order table using a One -to-many relationship. Dataverse shows the Primary and Foreign Keys but not the names of the customers or the names of the orders. Dataverse accurately shows the mapped keys but cannot display the names. Good for machines but not good for humans to read. And forget about using junction tables to create Many-to-many relationships. Dataverse, which is really a data source solution and not a application builder, is simply not equipped to handle this. Perhaps I am missing the proper code to create usable forms that can be read by humans but I cannot find where to insert code and what kind of code to use. Javascript? Power Fx? VBA?. Sure, Dataverse and Power Apps are good for storing contacts and cookie recipes but, from my experience, it is an unusable solution to create relational database apps.

    0 comments
  3. Tom van Stiphout 40,201 Reputation points MVP Volunteer Moderator
    2023-08-31T17:41:20+00:00

    > DV does not support forms for relational databases

    That is not its job. Same thing for ACE databases (the technology underlying ACCDB databases), or SQL Server databases: they provide storage and a way to retrieve data, but are not used to create forms.

    PowerApps and many other tools can connect to such data sources, and provide a way to create forms to work with the data. Your notion "The same goes for Power Apps" is incorrect.

    0 comments