We help IT Professionals succeed at work.

How can I store a JPG file in an SQL Server database using an Access front end to store and display the image?

Brian Thor
Brian Thor used Ask the Experts™
on
I am trying to create an Access form which allows the user to select a JPG file which then stores the image in a SQL Server table. I have the SQL Server table storing the data (if I insert it using a query) and displaying the save image, but I cannot figure out how to allow the user to select a file and read the data into the field. Here is the code I have to get the file NAME, but how do I get the file CONTENTS into the imgToolImage field?

Private Sub imgToolImage_Click()

    Dim fDialog As Office.FileDialog
    Dim strCompany As String
    Dim strFormName As String
    Dim strInitialFolder As String
    Dim strPathName As String
    Dim varFile As Variant

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog
        .AllowMultiSelect = False
        .Title = "Please select the file you wish to attach."
        If .Show = True Then
            For Each varFile In .SelectedItems
                Me.imgToolImage.PictureData = varFile
            Next
        Else
            Exit Sub
        End If
    End With

    Set fDialog = Nothing

End Sub
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Probably you need to take a look at my article :https://www.experts-exchange.com/articles/33716/Defeating-the-device-independent-bitmap-dib-format.html
Just tweak it to use SQL as BE and you should be good to go
David FavorFractional CTO
Distinguished Expert 2018

Commented:
Brian ThorPresident

Author

Commented:
John,

I was able to download your database and modify it as needed to work with my SQL backend. I think your example proved that there isn't a way to just make the image control equal to a file. In other words, the file had to be imported to a byte array and then assigned to the control. This also required that I update the record field with the byte data. I was hopeful that these two steps wouldn't be necessary but they appear to be.

One observation, in all of my searching or a solution I came to believe that using the Open Binary method was "old-school" and that there were newer better ways to accomplish this goal. Any thoughts on that?

Thanks,
Brian
John TsioumprisSoftware & Systems Engineer

Commented:
Open Binary does the job in 2 lines.. :)
Besides that you can use the ADODB Stream which is a bit more complex but in the end result is exactly the same