locked
Having a logo (.jpg) in an Access table shown in a report . How do you do the same with SQL Server as BE RRS feed

  • Question

  • Hi

    In an Access database I use Reports where the logo on the reports came from a field in a table. Data type is Attached File. Very useful when the logo is changed or updated.

    Now I am migrating to SQL server.
    I have activated Filestream in SQL Server 2014 developers edition.

    I havde linked the BE SQL tables via ODBC to a MS Access FE.

    If I run a query (view) of the table and Column I can see "OLE Object" as text. If i double click that "OLE Object" the file/logo opens and show on screen. Data type in SQL Server is "varbinary(MAX)"

    But when I try to "view" the OLE-object as the logo in an access report, nothing is shown.
    What must I do?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Thursday, June 2, 2016 4:54 PM

Answers

  • >>>But when I try to "view" the OLE-object as the logo in an access report, nothing is shown.
    What must I do?

    According to your description, I agree with DB guy's suggestion, you could refer to below code:
    Private Sub Command1_Click()
        Dim r As DAO.Recordset, sSQL As String, sTempPicture As String
        sSQL = "SELECT ID, LogoImage FROM dbo_TB_Logo"
        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not (r.EOF And r.BOF) Then
            sTempPicture = "D:\MyTempPicture.jpg"
            Call BlobToFile(sTempPicture, r("LogoImage"))
            If Dir(sTempPicture) <> "" Then
                Me.Image0.Picture = sTempPicture
            End If
        End If
        r.Close
        Set r = Nothing
    End Sub
    
    Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
        On Error GoTo BlobToFileError
    
        Dim nFileNum As Integer
        Dim abytData() As Byte
        BlobToFile = 0
        nFileNum = FreeFile
        Open strFile For Binary Access Write As nFileNum
        abytData = Field
        Put #nFileNum, , abytData
        BlobToFile = LOF(nFileNum)
    
    BlobToFileExit:
        If nFileNum > 0 Then Close nFileNum
        Exit Function
    
    BlobToFileError:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
               "Error writing file in BlobToFile"
        BlobToFile = 0
        Resume BlobToFileExit
    
    End Function

    Friday, June 3, 2016 2:08 AM
  • Hi guys and thanks!

    I decided to go an easier way (my point of view)

    I keep the logo image in a Access table and distribute it in the FE Access database


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    Tuesday, June 7, 2016 1:55 PM

All replies

  • Hi

    In an Access database I use Reports where the logo on the reports came from a field in a table. Data type is Attached File. Very useful when the logo is changed or updated.

    Now I am migrating to SQL server.
    I have activated Filestream in SQL Server 2014 developers edition.

    I havde linked the SQL tables via ODBC to a MS Access FE.

    If I run a query (view) of the table and Column I can see "OLE Object" as text. If i double click that OLE Object the file/logo opens and show. Data type in SQL Server is "varbinary(MAX)"

    But when I try to "view" the OLE-object as the logo in an access report, nothing is shown


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00


    Thursday, June 2, 2016 6:16 AM
  • Hi Peter. Just a guess but you might have to "extract" the logo from the table and save it to disk first and then apply it to the report probably by using code. Just a thought...
    Thursday, June 2, 2016 5:25 PM
  • >>>But when I try to "view" the OLE-object as the logo in an access report, nothing is shown.
    What must I do?

    According to your description, I agree with DB guy's suggestion, you could refer to below code:
    Private Sub Command1_Click()
        Dim r As DAO.Recordset, sSQL As String, sTempPicture As String
        sSQL = "SELECT ID, LogoImage FROM dbo_TB_Logo"
        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not (r.EOF And r.BOF) Then
            sTempPicture = "D:\MyTempPicture.jpg"
            Call BlobToFile(sTempPicture, r("LogoImage"))
            If Dir(sTempPicture) <> "" Then
                Me.Image0.Picture = sTempPicture
            End If
        End If
        r.Close
        Set r = Nothing
    End Sub
    
    Public Function BlobToFile(strFile As String, ByRef Field As Object) As Long
        On Error GoTo BlobToFileError
    
        Dim nFileNum As Integer
        Dim abytData() As Byte
        BlobToFile = 0
        nFileNum = FreeFile
        Open strFile For Binary Access Write As nFileNum
        abytData = Field
        Put #nFileNum, , abytData
        BlobToFile = LOF(nFileNum)
    
    BlobToFileExit:
        If nFileNum > 0 Then Close nFileNum
        Exit Function
    
    BlobToFileError:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, _
               "Error writing file in BlobToFile"
        BlobToFile = 0
        Resume BlobToFileExit
    
    End Function

    Friday, June 3, 2016 2:08 AM
  • Hi guys and thanks!

    I decided to go an easier way (my point of view)

    I keep the logo image in a Access table and distribute it in the FE Access database


    Best // Peter Forss Stockholm and Sigtuna GMT &#43;1.00

    Tuesday, June 7, 2016 1:55 PM
  • Hi Peter. It's good enough for me. Cheers!
    Tuesday, June 7, 2016 2:56 PM