Insert a picture into a Access table with a SQL Server Backend

Question

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?

Answer 1

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: