Forum Discussion
Solved
update dataverse table with dataverse primary key results in datatype error
Hi all. I have an annoying issue. I use MS access front end, dataverse backend, I'm using a simple bit of VBA code, to add a new record in a table. This is triggered by clicking a button on a...
hi all - just a quick update if anyone stumbles across this issue:
Dataverse as a managed solution blocks overriding a foreign/ alternative key, you can only update the dataverse primary key. In an unmanaged solution it’s the reverse I think, you can only update the alternative key, not the dataverse primary key.
the dataverse primary key is a GUID which cannot be stored in a text box as per issues above (that will show up as ???? In VBA). Solution : it can be stored in a combo box as a workaround, which as it turns out is compatible with GUID.
I believe that Dataverse Primary Keys are GUIDs.
If your existing records have a foreign key defined as Long Integer (which is how it would work if the Primary Key is an Autonumber), you are not going to be able to merge the two as this comment implies you have done, "This all works fine when using numeric access primary keys, but I am trying to start using the dataverse primary keys".
I am not that up on Dataverse, though. Check out this video and search out similar materials.
- Hi George -thanks a lot for your reply.
Apologies if that wasn't clear, both primary keys here are Dataverse keys and in the same format . I just meant that I have used this code in other applications in the past where it worked fine when they were integers at that stage.
vartype(me.policies) comes back as either 8 or 8209 if I read its value from a text box, but in the debug window only appears as ??????
If it is GUID, would that be a problem for Access?No problem. I am just kind of compulsive about making sure I understand the questions, before offering (potentially wrong) advice.
Actually, I don't think it should be a problem as long as both fields, primary key and foreign key, are the same datatype.
I don't see 8209 listed under the types in this article. Maybe it's a summation of two of them, i.e., vbArray (8192) and vbByte (17) 8192+17 =8209. I'm not sure it would be fruitful to venture down that rabbit hole. To be honest, this is the kind of stuff that makes me wary of investing much time in Dataverse. I think it's either all-in or stay away.
I don't have in-depth understanding of the guts of Dataverse. I've only tinkered with using Access with Dataverse in the past; I'm committed to SQL Server for the most part for back end databases.
Check out Maria's presentation from a couple of years ago and see if she has anything relevant.
- I generally have limited issues with it and have been using for about a year. there are these occassional quirks , there is less experience so not always easy to find an answer.
I tried this instead (as the Policies table still has an old Access integer based key):
strTmp = "INSERT INTO Endorsements ( policies ) SELECT Policies FROM Policies WHERE policy_ID = " & Me.Policy_ID & ";"
DBEngine(0)(0).Execute strTmp, dbFailOnError
This throws up a 4030 error , "required field does not contain a value".
It most definitely does as I can see it on screen and in the dataverse backend.
Unless someone has an alternative suggestion I suspect Dataverse is blocking SQL overriding a foreign key field in its tables.
- > If it is GUID, would that be a problem for Access?
Access can have GUIDs for the field datatype: Number with subtype ReplicationID.
However, there is currently no support for exporting such values to DV.
