Migration to Dataverse

[TheJaks]

Hi all, I was able to migrate the backend of our MS Access application from Sharepoint Lists to Dataverse for teams, using the new connector tool.

After export, in MS Access the reading of the records & all read queries works fine, and I can edit records provided there is only 1 table underlying the form. However, as soon as I want to edit a recordset via a MS Access form where there is more than 1 linked table being edited, I get the error "this recordset is not updatable" (the data shows fine, it's just not editable).

I suspect an error in the relationships after export. However, from what I can see all the relationships were copied as expected and exactly as they were before export (but it's not so easy as the primary keys are different for dataverse, and the relationship is "indeterminate"). In Dataverse, the relationships also appear.

I have since tried an export to Dataverse (instead of dataverse for teams), same issue, and I've rebuild the database from a new file and then export that, with the same issue. In a new Access file, I've imported the dataverse tables, and it imports correctly all tables and all relationships correctly, but the issue persists.

I've ran out of ideas what to do to solve this...Suspect it's a bug. Any further suggestions?

[#2 · Accepted solution]

@TheJaks 

First, and most obvious, it's a Best Practice NOT to try to bind multi-table queries to forms where you intend to edit records. So that in itself is the wrong path for good interface design. 

Second, many queries are no longer editable in Access for a number of reasons. It could be, as you suspect, a problem of relationships, but there are several others. You may be running afoul of one those those, too.

I get it that Dataverse can be somewhat confusing because it's almost like standard relational databases, but not quite. That said, if you can see the primary and foreign keys in your linked tables in design view, and if you can see the relationships in the relationship window, you should be okay on that score. 

And that leads back to examining the query and making sure that it is not the problem. And rather than trying to go against standard design, you might want to adopt a more appropriate interface, which is usually a main form/subform design with the main form bound to the table (or a query based only on that table) on the one-side of a relationship, with a subform bound to the table (or a query based only on that table) on the many side of the relationship.

This design is the standard for a reason: it works.

[#3]

@George Hepworththanks a lot for your considered reply, appreciated.

I checked the other reasons, none of those apply (PS he same application works 100% fine either local or sharepoint as backend).

The multi-table query form discussion is perhaps a little bit off topic. It's fully embedded in access functionality (eg form builder very easily allows that setup) and the multi-table forms work fine with either local tables or sharepoint, so just a bit intrigued why I should now redesign most forms for dataverse to work . I'll take on board it may not be best practice, and in time I may change them, but for now just wanted a working dataverse.

Re the relationships: the relationship lines appear fine (1 at one side, infinity at the other), however none of the tables have a primary key symbol in them (nor a foreign key). I checked and they were there before export. I just tried to add them back, but because they're linked tables I can't edit/save them in Access (and dont see an option in Dataverse, as that has a different key column in any case). So it may be a case of primary keys gone missing in Access during export. Any idea how to get them back?

[#4]

@TheJaks 

Regarding updateable queries with multiple tables. It's not impossible to create them, but experience is the best teacher. Over time, I suspect, the simplicity of adopting a "standard" design wherever possible seems to be the path of least effort for most of us. Without diverging too far off topic, though, it's probably something you can best decide based on your own experience.

With Regard to Dataverse tables, Primary Keys are tricky, and I'm not all that confident I have it completely right. However, I do know that Dataverse, like SharePoint, assigns its own choices for PKs when migrating Access tables. Here's the database diagram from the only accdb relational database application I have linked to DV for testing. Like you indicate, the "Primary Key" field is not flagged with the Key Symbol we see in other relational database diagrams, but it is definitely the primary key. Dataverse created it during the migration. Note that it repeats the name of the table. Behind the scenes DV is managing the relationships, but I'm not yet sure I understand all of the nuances.

In order to keep things straight, you'll need to figure out which fields to join in your queries. And how to define joins properly on them. I know of a couple of YouTube videos on the topic of using Access with Dataverse. I linked one, but there should be others. Perhaps that's a source of details you can use.

[#5]

@George Hepworth 

thanks for sending that link. 

I couldn't find the exact answer with that, but found some other users with the same issue, folllowed by again the same general advice you mentioned which is to avoid multi-table queries on a data edit form.

On reflection, I looked at my multi-table queries form and realised the multi-tables are redundant often (ie just pulling in an info field to help editing, which I can with Dlookup or small forms re-design), so I am now following your advice to switch to single table forms when editing. 

It looks like dataverse has this much more strictly controlled than sharepoint. Probably a good thing, half the reason I'm switching from sharepoint as that has very weak relationship enforcing and that worried me for future data integrity.

Thanks!