none
Access 2010 Attachment Control to Sql Server 2008R2 Varbinary(max) column

    Question

  • Hello all,

    How can the Attachment control be used in Access to store files to a Varbinary(max) column on a sql server 2008R2 database?

    When I add the control and set the control source in access, clicking the control in form view results in no events occurring. If I just have a regular access database not using sql server as the backend the control works as expected.

    I have found several online articles related to my issue but none of them use Access.

    Attached is an image of the property sheet. Please let me know your suggestions.


    Keith

    Tuesday, August 27, 2013 7:13 PM

Answers

  • Hi Keith.

    Attachment is a multiple-valued filed (you can store multiple files in one record) which is not supported by SQL Server.

    Multi-valued fields are for compatibility with SharePoint, and the Attachment data type lets you store binary files in an Access database. Instead of attachments, you might want to consider FILESTREAM storage. For more information, see FILESTREAM Storage in SQL Server 2008 http://msdn.microsoft.com/en-us/library/cc949109.aspx

    In addition, if you do not know how many files in one record. You can choose OLE Object in Access and create a child table to store.

    According to the below blog, OLE Object can be varbinary in SQL Server while using SQL Server Migration Assistant (SSMA).

    Access to SQL Server Migration: Understanding Data Type Conversions


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.



    Thursday, August 29, 2013 3:05 AM

All replies

  • Is there another option to use besides the Attachment control with SQL server?

    What are alternate options in this scenario where I have sql server as the backend and want to include relevant data to a record?

    Based on the research I've done this control will ONLY work where access is using its own database rather than a linked sql server.


    Keith

    Wednesday, August 28, 2013 6:29 PM
  • Hi Keith.

    Attachment is a multiple-valued filed (you can store multiple files in one record) which is not supported by SQL Server.

    Multi-valued fields are for compatibility with SharePoint, and the Attachment data type lets you store binary files in an Access database. Instead of attachments, you might want to consider FILESTREAM storage. For more information, see FILESTREAM Storage in SQL Server 2008 http://msdn.microsoft.com/en-us/library/cc949109.aspx

    In addition, if you do not know how many files in one record. You can choose OLE Object in Access and create a child table to store.

    According to the below blog, OLE Object can be varbinary in SQL Server while using SQL Server Migration Assistant (SSMA).

    Access to SQL Server Migration: Understanding Data Type Conversions


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.



    Thursday, August 29, 2013 3:05 AM