Results 1 to 2 of 2
  1. #1
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197

    Arrow SQL Server varbinary(32) in Access


    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

  2. #2
    hapm is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2014
    Posts
    197
    It works as intended. There was an insert trigger that prevented the value from being inserted in the the record. Blame me...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-26-2013, 11:31 AM
  2. Replies: 2
    Last Post: 11-21-2012, 09:57 AM
  3. Replies: 1
    Last Post: 04-23-2012, 03:41 PM
  4. Access and SQL Server?
    By evander in forum Database Design
    Replies: 2
    Last Post: 07-31-2010, 07:47 PM
  5. Display varbinary in Access form
    By aspfun in forum Access
    Replies: 0
    Last Post: 06-29-2010, 02:06 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums