locked
storing attachments in sql server RRS feed

  • Question

  • I am in the midst of trying to migrate an access database to sql server.  Have successfully gotten all data migrated except 'attachment' datatypes.  We would like to store attachments within a table in the database and moving forward in the future, continue to use the access front end to attach files to store in the database.  

    From sql server, tried the varbinary(max) and also filestream.  Does anyone have any suggestions on how to get this data into sql server?  I've been spinning my wheels for weeks.  

    Tuesday, December 3, 2013 4:03 PM

Answers

  • you could use an ADO command object and run T-sql code like the following (T-sql is for Microsoft Sql Server -- it is not exactly the same as Jet Sql for an Access DB) -- the only catch is that the attachment data needs to be a file on the disk.  I don't think you can directly transfer binary data from an Ole Object field in Access to a varbinary(MAX) field/column in a sql server table (varbinary(MAX) is the sql server equivalent of an OLE Object field in Access).

    -- make a reference to "Microsoft ActiveX Data Object 2.5 library" (2.5 or higher libraries)
    Dim cmd As New ADODB.Command
    
    cmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=wks308\sqlexpress;Database=MasterDetail;Trusted_Connection=Yes;"
    cmd.ActiveConnection.CursorLocation = adUseClient
    cmd.CommandType = adCmdText
    
    cmd.CommandText = "insert into tblBlobStuff(blobThing) select BulkColumn from openrowset(BULK N'C:\1A\myTest.pdf', Single_Blob) doc

    Note:  BulkColumn is a key word / specific command name -- it is not a random field name

    blobThing is a varbinary(MAX) data type column in the sql server table

    I use a pdf file in my sample, but this will work with image files, doc files, etc.



    Rich P



    • Edited by Rich P123 Tuesday, December 3, 2013 5:38 PM ......
    • Marked as answer by Luna Zhang - MSFT Friday, December 13, 2013 2:23 AM
    Tuesday, December 3, 2013 5:00 PM