update dataverse table with dataverse primary key results in datatype error

[TheJaks]

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?

[#2]

@TheJaks 

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.

[#3]

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?

[#4]

> 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.

[#5]

@TheJaks 

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.

[#6]

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.

[#7]

I agree that Dataverse is a black box with its own internal workings and rules. I guess I am lazy, but I'd rather avoid the whole issue and rely on the known, tried and true SQL Server/SQL Azure. If someone else wants to adopt Dataverse, great, but the learning curve is mostly up and not very direct, IMO.