So this is more a question about the compatibility of sql servers varbinary type and ms access, not only a pure sql server question, so I hope I post to the correct forum. I'm working with Access 2010 and SQL Server 2008 R2 here.
What I try is to edit a varbinary(32) field in a table on the server, that is linked as a dbBinary field in a table in access, using DAO in vba. So the recordset is opened, .AddNew is called, and all fields are set as needed. Wenn I call .Update the record is saved, but the varbinary field doesn't get the value I assigned to it:
Code:
Dim someBinaryData(16) AS Byte
' Some code to fill in the binary data
With db.OpenRecordset("SomeTable", dbOpenDynaset, dbSeeChanges)
.AddNew
' Set some other fields
.Fields("MyBinaryField").Value = someBinaryData
.Update
End With
When investigating this with the vba debugger, after executing the line where the value is assigned .Fields("MyBinaryField").Value is still Null. So as far as I can tell, the assignment fails silently. I did some research and often found information about how to use DAO.Recordset.AppendChunk to fill up dbLongBinary fields with binary data, but the field is of type dbBinary and .AppendChunk doesn't work for this type of field. So my questions are: how to correctly assign a value to this type of field when using DAO? Or isn't it supported in DAO and I need to use ADO or something else to do it?
All help appreciated,
hapm