Forum Discussion
Migration to Dataverse
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.
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.
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?