Code to save a file as binary into SQL database. Can be used in Excel and Access.

Save the file to the SQL database

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strObj As ADODB.Stream

Set conn = New ADODB.Connection
connString = "Provider=SQLOLEDB;Server=SERVER;Database=DATABASE; Integrated Security=SSPI;"
conn.Open connString
        
Set rs = New ADODB.Recordset
sqlGetRecord = "SELECT * FROM TABLE where ID = " & Me.ID
        
rs.Open sqlGetRecord, conn, adOpenDynamic, adLockOptimistic
        
If Not (rs.BOF And rs.EOF) Then
        
    Set strObj = New ADODB.Stream
    strObj.Type = adTypeBinary
    strObj.Open
            
    strObj.LoadFromFile (fileLocation)
    'get just file name
    fileName = Right(fileLocation, Len(fileLocation) - InStrRev(fileLocation, "\"))
            
    rs!BinaryFile = strObj.Read
    rs!FileAttachment = fileName
            
    rs.Update
    strObj.Close
    Set strObj = Nothing
      
End If


Open the file from the SQL database:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim strObj As ADODB.Stream
Dim sqlGetFile, fileName As String

Set conn = New ADODB.Connection
connString = "Provider=SQLOLEDB;Server=SERVER;Database=DATABASE; Integrated Security=SSPI;"
conn.Open connString
    
sqlGetFile = "SELECT FileAttachment, BinaryFile FROM TABLE where ID = " & noteID
savePath = "\\SAVE\PATH"
Set rs = conn.Execute(sqlGetFile)
    
If rs.EOF Then
    MsgBox "Nothing found"
    End
End If
    
Set strObj = New ADODB.Stream
strObj.Type = adTypeBinary
strObj.Open
strObj.Write rs!BinaryFile
fileName = rs!FileAttachment
fileName = Trim(fileName)
strObj.SaveToFile savePath & fileName, adSaveCreateOverWrite
   
strObj.Close
Set strObj = Nothing
Application.FollowHyperlink savePath & fileName

Categorized in:

VBA Access, VBA Excel,

Last Update: May 18, 2024