How do I fix or work around foreign key columns being read-only after exporting my Access database to Dataverse?
For context, I'm trying to migrate one of our business apps from MS Access to Dataverse using the export wizard in Access. The database has ~20 tables, each of which has an AutoNumber primary key and usually multiple foreign key relationships that reference the related tables' primary keys. The schema itself is normalized with help from a DBA. All that to say, the Access database is relatively conventional and adheres to most (if not all) Access and general DB best practices (which, in the context of Access, might be unconventional, but I digress). All data manipulation happens via forms, often parent/sub-forms to manage relationships, and there's quite a bit of custom VBA code to handle validation and other user niceties.
We've already worked through some bugs and quirks with the export process (notably, we had to rename all our primary and foreign key columns from <TableName>Id to something else (we used Legacy<TableName>Id) to work around the wizard failing due to being unable to create the relationships, which we tracked down to naming collisions when creating the relationships and keys). These frustrations have led to creating a minimal sample with just 4 of the tables from a relatively isolated part of the schema and a single parent/subform pair to test data manipulation. Before exporting to Dataverse, all data manipulation, via the forms or the tables directly, works as expected.
However, after exporting the sample database to Dataverse, all the foreign key fields are now read-only, which renders it impossible to add or edit any rows that are related to other tables (which is basically all of them). Specifically, given a table BankAccount with a LegacyBankId column that references the Bank table's LegacyBankId column, the exported table in Dataverse ends up with those columns, which are marked as required, but then creates new Relationship columns, e.g. BankAccount.BankLegacyBankId. However, the Bank.LegacyBankId columns are read-only in both Dataverse and Access, which means all my forms that present a combobox, e.g. a Bank combobox on the BankAccount form are unable to create the record - the Required BankAccount's LegacyBankId column is required, but also read-only.
I've already attempted to work around by changing the forms' fields to the new Dataverse relationships columns, e.g. BankAccount.BankLegacyBankId, but this presents problems anywhere custom VBA code exists, because the GUIDs contained in that column is reported as a Byte() (rather than a GUID or other known type), presumably because GUIDs are not a valid Access data type. Besides the fact that this means basically rebuilding the app from scratch, as every query and form would need to change, it appears to be technically impossible?
It's possible that there's some other undocumented quirk with the export wizard that I'm running afoul of, so I'm open to any and all ideas for how to tweak my database or forms before export so that the wizard works as intended, or any suggestions for how to make the for-Access Legacy<TableName>Id foreign key columns writable to restore the app to functionality.
Please help!
