MS Access Migration to Dataverse , recordset not updatable

[TheJaks]

Hi all, this morning I was able to migrate the backend of our MS Access application from Sharepoint Lists to Dataverse for teams.

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 are linked tables via query underlying the form, I get the error "this recordset is not updatable" (the data does appear fine).  This does not occur in my parralel DB using sharepoint lists and the same form, so it is definitely a dataverse export related issue.

I suspect there must be an error in exporting the relationships. However, from what I can see all the relationships were copied as expected but it's not so easy as the primary keys are different for dataverse. In MS Access relationship overview, each relationship in MS Access now has status "indeterminate" rather than pre export one-to-many . To test if resetting the relationship would fix the issue, I tried to delete and recreate one of the relationships in MS Access, but it turns out I can't create new one-to-many relationship (it shows up as an arrow, with status "indeterminate") so that didn't solve the problem.

PS unsure if relevant but I deleted all the _local tables post export, they didnt seem to add function anymore.

Any ideas what's going on & how to solve this?

[dpoggemann]

Hi @TheJaks ,

Sorry, your details are a bit foreign to me and I want to help you get a solution so I might have some stupid questions and I apologize in advance...

1. Are you saying your current MS Access Application's backend data structure is in SharePoint?
2. You are modifying from SharePoint backend to Dataverse or from Access tables to Dataverse?
3. If the tables are in Access, did you use the migration strategy https://learn.microsoft.com/en-us/power-apps/maker/data-platform/migrate-access-to-dataverse to move from the Access tables to Dataverse

Overall some more information would be helpful.   I have moved a number of customers from Access Databases to full Dataverse, although it has always been full Dataverse vs. Dataverse for Teams and I would like to help understand your situation more and hopefully I can assist.

[TheJaks]

thanks a lot for your interest and good to hear you have experience migrating some databases across.

My existing setup is MS access frontend, sharepoint backend. been working like this stable for some time.

To prepare for migration, I imported all the sharepointed lists to lmake them ocal tables. Then I deleted all the sharepoint related columns (that get added by sharepoint automatically). Last step was changing all the float fields to decimals. 

I tested that database, checked the relationships, all working fine.

Then I followed the export procedure for Dataverse for Teams (the one you shared), for all tables.

No error messages, all working fine. Except for when an edit form has a query underlying that sources data from more than 1 table. 

PS I also tested to export to normal Dataverse , and that had the exact same outcome.

thanks!

[dpoggemann · Accepted solution]

Hi @TheJaks ,

Not sure how your query when migrating to Dataverse.  The relationships in Access can be on many fields between two tables but in Dataverse it needs to be on the PrimaryName column.  You might want to review the following:  https://support.microsoft.com/en-us/office/get-started-migrate-access-data-to-dataverse-013c8bab-773... with the section on "Background Information on primary keys and relationships".

I personally don't expect the form to work trying to edit multiple data tables at one time based on how model apps and canvas apps work with Dataverse but I can't tell you if it should or not for sure.  Just my expectation is it would not since this is not supported in any way that I know of in Dataverse.

[TheJaks]

@dpoggemann 

thanks. You're right, it is the fact that the edit form has multiple tables in the underlying  recordsource query. Now, just to be correct I am only ever editing one table at a time, but I occasionally pull in supporting data from other tables (ie that information is displayed in non editable fields). This worked fine with access/sharepoint, but it seems dataverse blocks this, and I can see the reason why to protect data integrity.

I will change the edit data forms to a single table source, and pull in the supporting data via dlookup or form redesign. 

thanks!

[TheJaks]

@dpoggemann 

Hi Drew - One more question if I may please! I've nearly completed the migration, all forms are working now. However I am stuck on one Append / insert into query that is activated from a (read) form, but when run it gives the error "Failed to write into an internet handle". Strangly, after closing the form and reopening, it turns out it has added some data but not all, and a bit randomly (ie not skipping the same data consistently).

The query basically pulls 6 columns from 2 tables, does some calculations and adds them as a new record in a single other table. PS The reason caclulated data is saved in another table is because those values may be changed later on by the user , but I want to retain the original values too. We're talking up to say 100 entries per run so definitely don't want to do it manual.

Again, this worked fine either local tables or sharepoint but not working in dataverse.

Any idea what could cause the issue?