Forum Discussion
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 form. Once the new record is created, I then want to add the dataverse primary key of the form to that new record. This all works fine when using numeric access primary keys, but I am trying to start using the dataverse primary keys which I think are string and this is throwing up datamismatch errors.
Code:
Private Sub AddEndorsement()
Dim rs As DAO.Recordset
On Error GoTo Errorhandle
Set rs = CurrentDb.OpenRecordset("Endorsements")
With rs
.AddNew
!Policies = Me.Policies
.Update
.MoveLast
.Bookmark = .LastModified
End With
rs.Close
Set rs = Nothing
Exit Sub
Errorhandler:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub
the error it throws up when trying to add the record is 3421, data type conversion error so clearly a data mismatch on !policies and me.policies. However from all I can see they are the same data type.
I have a text box on the form that shows me.policies (the primary key ) correctly as {343very long code}. When I check the datatype it is 8. However, in VBA debug window the same value is shown as "?????" which is odd.
Endorsement.policies stores the foreign key successfully for other records . The data type in dataverse is "Lookup" but I assume underlying it still means string.
Any ideas?
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?- > 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.
