none
"Attachment" data type in SQL Server 2012 RRS feed

  • Question

  • 
    
    I have a database that is running an Access 2013 front end and a SQL SErver 2012 back end.  One of the things we keep track of is the hotels that we are using for a given show.  My housing director would like to attach a PDF of the contract to each hotel's record.  Access has a data type of "Attachment" that would work for this, but the table is in SQL Server.  Is there a similar data type that will allow me to store a PDF attachment to a table record?
    Thursday, May 19, 2016 2:22 PM

Answers

  • In MS Access you need to write vba code to read the file as a stream object and insert into sql server table column that is having datatype varbinary(max). 

    Set rs = New ADODB.Recordset
    rs.Open "select * from AttachmentTable", Connection, adOpenKeyset, adLockOptimistic

    Set streamobj= New ADODB.Stream
    streamobj.Type = adTypeBinary
    streamobj.Open
    streamobj.LoadFromFile "c:\myfile.pdf"
    rs.Fields("blobfield").Value = streamobj.Read
    rs.Update


    Regards, RSingh


    Sunday, May 22, 2016 10:41 AM

All replies

  •  no there is no such data type as "attachement".  since you are on SQL 2012, you can try File Tables

    refer: https://www.mssqltips.com/sqlservertip/2667/filetable-feature-in-sql-server-2012/

    of you can use varbinary(max) data type


    Hope it Helps!!

    Thursday, May 19, 2016 2:38 PM
  • If I use varbinary(max), the Access database views it as an OLE Object data type.  How do I then get the form to allow the user to put the PDF in there or view it if it's there?  Or should I put that question to my Access forum?

    Amy E. Baggott "I try to take one day at a time, but sometimes several days attack me at once." -- Ashleigh Brilliant

    Thursday, May 19, 2016 3:29 PM
  • How big are the files? You can store them into a database (VARBINARY(MAX)) or on the file system and then have a link from the table (store the link as varchar) to that file

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 22, 2016 6:20 AM
  • In MS Access you need to write vba code to read the file as a stream object and insert into sql server table column that is having datatype varbinary(max). 

    Set rs = New ADODB.Recordset
    rs.Open "select * from AttachmentTable", Connection, adOpenKeyset, adLockOptimistic

    Set streamobj= New ADODB.Stream
    streamobj.Type = adTypeBinary
    streamobj.Open
    streamobj.LoadFromFile "c:\myfile.pdf"
    rs.Fields("blobfield").Value = streamobj.Read
    rs.Update


    Regards, RSingh


    Sunday, May 22, 2016 10:41 AM
  • Hi Amy Baggott,

    In this case your question is actually related to Access, so we are moving your thread to a proper forum for better support.

    Regards,
    Lin

    Monday, May 23, 2016 8:28 AM
  • I would suggest if possible that you place the files in folders on the server.

    You then place  the path name in the database.

    Placing files in folders gives you the ability to search and index text from those documents (without the database being used). Or perhaps you use some PDF software to open and modify the PDF files (maybe convert first page to a thumbnail for display on a web site?).

    And these “utility” programs can thus work directly on standard windows files in a folder. Most windows utility cannot and do not pull or read or open files from a database.

    And down the road, you might prefer to dish out such pdf documents to a web browsers application that allows customers to self-serve such information. Again, having files in a folder structure in most cases is desirable for web based applications.

    There are certainly some possible scenarios where storing the files inside of the database can help, but not in general.

    Storing documents outside of the database allows one to use such files without first having to obtain the file from the database system. So all kinds of file utilities, file print utilities, and even just sorting the file names by name, or by date of creating etc. can be done without any code or even having to use the database.

    You most certainly can store the URL or UNC path name to documents in a standard text column in the database to such folders. However, the documents etc. should be placed in folders, and not in the actual database.

    If you don't have the above choice, then you have to adopt code to retrieve and save documents into that database, and that can add up to significant amount of work.

    Regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    • Proposed as answer by mbizup MVP Tuesday, May 24, 2016 9:58 AM
    Tuesday, May 24, 2016 3:22 AM
  • Hi Amy Baggott,

    please visit the link below it will help you to store the file in sql server.

    Storing and Retrieving doc/pdf/xls files in SQL Server

    Regards

    Deepak

    Wednesday, May 25, 2016 6:01 AM
    Moderator