I need to insert an image into a MS Access form (not link it, as it is a licence, and it needs to be encrypted and protected). I have a MS Access front end and a SQL Server backend. This is the code for the insert
Private Sub AddLicence1Picture_Click()
Dim f As Object
Set f = Application.FileDialog(1)
f.allowmultiselect = False
If (f.Show = True) Then
Me![LicencePicture1].Picture = f.selecteditems(1)
End If
End Sub
I have a table that holds all the other data, and a column called LicencePicture1
, and the data type is set to IMAGE
and I have also tried setting it to VARBINARY(MAX)
. Can anyone please point me in the right direction as to what in doing wrong?
CodePudding user response:
Well, one step at a time (encryption can be part 2).
So, for some reason a varbinary(max) column does not work with the standard odbc driver. However, if you create sql server image column, then it can/will work.
The first step? Use the NEW image control - NOT the oleDB one from the ribbon.
So, choose this one:
Next up, you find that if you BIND that image control to the database column (linked table to sql server), it will not work.
However, you can still shove the image file (as raw binary) into that column, and save the current record.
And you can also display.
So, you can navigate to the given record, then say have a button to browse to the given file - similar to what you have.
So, we have this:
Private Sub cmdFile_Click()
Dim f As FileDialog
Set f = Application.FileDialog(msoFileDialogFilePicker)
f.Show
If f.SelectedItems.Count > 0 Then
Me.txtFile = f.SelectedItems(1)
End If
End Sub
Ok, so that puts the picture path into a un-bound text box.
We have this so far:
Now, note the save to db button. The code for that button looks like this:
Private Sub cmdSaveToDB_Click()
' save current record
If Me.Dirty Then Me.Dirty = False
Dim MyImage() As Byte
MyImage = GetFileBytes(Me.txtFile)
Me!ImageB = MyImage
Me.Dirty = False
End Sub
And we also need this binary file read routine - it uses the path name in the text box
Public Function GetFileBytes(ByVal path As String) As Byte()
Dim lngFileNum As Long
Dim bytRtnVal() As Byte
lngFileNum = FreeFile
If LenB(Dir(path)) Then ''// Does file exist?
Open path For Binary Access Read As lngFileNum
ReDim bytRtnVal(LOF(lngFileNum) - 1&) As Byte
Get lngFileNum, , bytRtnVal
Close lngFileNum
Else
Err.Raise 53
End If
GetFileBytes = bytRtnVal
Erase bytRtnVal
End Function
That's it.
As noted, the only issue is that we can't bind the picture box directly to the forms data source.
but, you can do this:
So, in above, dropped in a button to display the picture.
It looks like this:
Private Sub cmdShowFromDB_Click()
Me.Image1.PictureData = Me.Recordset!ImageB
End Sub
So, the results now look like this:
If you only load the form to one reocrd then put the code to "set" the image in the forms on-load event.
However, if you allow navagation, then you have to use the on-current to automatic display the image.
But, at least now with the new image control (2010 I think??), then you don't need a lot of special code.
so above saves the binary picture (raw) to sql server: