Brian Thor
asked on
How can I store a JPG file in an SQL Server database using an Access front end to store and display the image?
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(mso FileDialog FilePicker )
With fDialog
.AllowMultiSelect = False
.Title = "Please select the file you wish to attach."
If .Show = True Then
For Each varFile In .SelectedItems
Me.imgToolImage.PictureDat a = varFile
Next
Else
Exit Sub
End If
End With
Set fDialog = Nothing
End Sub
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(mso
With fDialog
.AllowMultiSelect = False
.Title = "Please select the file you wish to attach."
If .Show = True Then
For Each varFile In .SelectedItems
Me.imgToolImage.PictureDat
Next
Else
Exit Sub
End If
End With
Set fDialog = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
https://docs.microsoft.com/en-us/sql/relational-databases/blob/binary-large-object-blob-data-sql-server provides a good overview of BLOB management.
ASKER
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
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
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
Besides that you can use the ADODB Stream which is a bit more complex but in the end result is exactly the same