Trick of the Week :: Store (and then fetch) files from SQL Server tables

Posted: September 30, 2014 by Transformer in SQL, VBA
Tags: , , , ,

Problem Statement: I have a database of employees where I store information about each person. I wish to have a picture of the employee in my database that I want to retrieve when I select an employee ID. Solution: I am sharing a VBA code that saves the file in SQL server table in binary format. When the file is retrieved, it can be done in original format. Let’s say I’ve a table Employee, it has two columns ID (Datatype: int) and Image (Datatype: varBinary(Max)). Code to store an image as binary type:

'To save a file in a table as binary
Sub SaveAsBinary()

    Dim adoStream               As Object
    Dim adoCmd                  As Object
    Dim strFilePath             As String
    Dim adoCon                  As Object
    Const strDB                 As String = ""  'Database name
    Const strServerName         As String = ""  'Server Name
    
    Set adoCon = CreateObject("ADODB.Connection")
    Set adoStream = CreateObject("ADODB.Stream")
    Set adoCmd = CreateObject("ADODB.Command")
    
    '--Open Connection to SQL server
    adoCon.CursorLocation = adUseClient
    adoCon.Open "Provider=SQLOLEDB;Data Source=" & strServerName & ";Initial Catalog = " & strDB & ";Integrated Security=SSPI;"
    '----
    
    strFilePath = "C:\1.JPG" ' File to upload
    
    adoStream.Type = adTypeBinary
    adoStream.Open
    adoStream.LoadFromFile strFilePath 'It fails if file is open
        
    With adoCmd
        .CommandText = "INSERT INTO Employee VALUES (?,?)" ' Query
        .CommandType = adCmdText
        
        '---adding parameters
        .Parameters.Append .CreateParameter("@Id", adInteger, adParamInput, 0, 1)
        .Parameters.Append .CreateParameter("@Image", adVarBinary, adParamInput, adoStream.Size, adoStream.Read)
        '---
    End With
    
    adoCmd.ActiveConnection = adoCon
    adoCmd.Execute
        
    adoCon.Close
    
End Sub

Code to Read an image – Saved in Binary format:

'To read binary stream from DB and save the same on system drive
Sub ReadBinary()
     
     Dim adoRs                  As Object
     Dim adoStream              As Object
     Dim adoCon                 As Object
     Const strDB                As String = ""  'Database name
     Const strServerName        As String = ""  'Server Name
      
     
     Set adoCon = CreateObject("ADODB.Connection")
     Set adoRs = CreateObject("ADODB.Recordset")
     Set adoStream = CreateObject("ADODB.Stream")
         
     '--Open Connection to SQL server
     adoCon.CursorLocation = adUseClient
     adoCon.Open "Provider=SQLOLEDB;Data Source=" & strServerName & ";Initial Catalog = " & strDB & ";Integrated Security=SSPI;"
     '--
     
     adoRs.Open "SELECT ID,Image FROM Employee WHERE ID = 1", adoCon, adOpenStatic, adLockOptimistic
         
     adoStream.Type = adTypeBinary
     adoStream.Open
     adoStream.Write adoRs.Fields("Image").Value ' FieldName that contains binary data
     
     adoStream.SaveToFile "C:\" & adoRs.Fields("ID").Value & ".jpg", adSaveCreateOverWrite
     
     adoRs.Close
     adoCon.Close

End Sub

Above code has been written to save and read an image in SQL Server.In similar fashion you can store an read other format files(e.g. pdf,word and txt etc.) also.

Advertisements
Comments
  1. Nice one and same we can do in access as well with BLOBs

  2. Nice functional demonstration. Would be even better if routines/functions take parameters so they are directly reusable.

  3. Sujeet Singh says:

    How to store excel file in sql server table using vba.Please share me..

  4. Navnneet Bihani says:

    I am getting the following error while uploading the excel file:
    [Microsoft][ODBC SQL SERVER DRIVER] String Data, right Truncation

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s