Putting Pictures
in a
Database




by Rick Meyer

1. Start a new standard exe.
2. In Project->Components add the ADO Control
3. In Project->Components add Microsoft CommonDialog
4. In Project->References add Microsoft ADO Ext 2.0 for DLL & Security
5. In Project->References add Microsoft ActiveX Data Objects 2.1 Library
6. In Project->References add Microsoft ActiveX Data Objects Recordset 2.1 Library
7. On Form1 put an ADO Data Control.
8. On Form1 put a Commandbutton named Command1.
9. On Form1 put a Textbox named Text1.
        (Set the Text1 DataSource property to Data1)
10. On Form1 put an Imagebox named Image1.
        (Set the Image1 DataSource property to Data1)
11. On Form1 put a CommonDialog named CommonDialog1.

      Form1 should look like the picture above.

Now you are ready for the code. Select all of the following code (by clicking on the word Option three times) and copy it to the clipboard [Ctrl][Insert]. Then paste it into the code window of Form1 with [Shift][Insert].
When you run the program, click on the CommandButton to select the pictures you want to put in the database with the CommonDialog. Then to see all the pictures you have entered, click on the Data1 Control. 
Option Explicit

Const FLTR = "Pictures|*.jpg*;*.gif*;*.bmp;*.ico|All Files|*.*"
     
Private Sub Form_Load()
    Dim dbName$, tbName$
    
    dbName = "c:\Pic1DB.mdb"
    tbName = "Pictures"
    
    'Delete statement used for testing create
    'If Dir(dbName) <> "" Then Kill dbName
    
    If Dir(dbName) = "" Then
        Dim cn$
        Dim cat As New ADOX.Catalog
        Dim tPics As New ADOX.Table
        
        cn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source = " & dbName & ";"
        cat.Create cn
        
        With tPics
            .Name = tbName
            .Columns.Append "Name", adVarWChar
            .Columns!Name.Attributes = adColNullable
            .Columns.Append "Picture", adLongVarBinary
        End With
        
        cat.Tables.Append tPics
        Set tPics = Nothing
        Set cat = Nothing
    End If
    
    With ISGData1
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .DataSource = dbName & ";"
        .SQL = "SELECT * FROM " & tbName
        .Refresh
    End With
    
    Text1.DataField = "Name"
    Command1.Caption = "Add Picture"
    Image1.BorderStyle = 1
 End Sub


Private Sub Command1_Click()
    Dim f%, L&, picFile$
    
    On Error GoTo er1
    With CommonDialog1
        .CancelError = True
        .Filter = FLTR
        .ShowOpen
        picFile = .filename
    End With
    
    f = FreeFile
    Open picFile For Binary Access Read As f

    L = LOF(f)
    ReDim B(L) As Byte
    Get f, , B()
    Close f
    
    With ISGData1.Recordset
        .AddNew
        !Picture.AppendChunk B()
        !Name = picFile
        .UpdateRecord
    End With
    
    GoTo er2
    
er1: Resume er2
er2: On Error GoTo 0
End Sub


Private Sub ISGData1_Reposition()
    Dim L&, f%, t$
    
    L = ISGData1.Recordset!Picture.ActualSize
    ReDim B(L) As Byte
    B = ISGData1.Recordset!Picture.GetChunk(L)
    
    t = "tmp"
    f = FreeFile
    Open t For Binary Access Write As f
    Put f, , B()
    Close f
    
    Set Image1.Picture = LoadPicture(t)
End Sub
There are 3 references to ISGData above. Change these to the name of your data control.
Strangely, you can not bind a picture, image, or OLE control successively to an ADO control. That is the reason for the code in the Reposition event. If you examine the code, you will see what has to be done is to read the data from the LongBinary datafield into a byte array. The array is in turn saved to a temp file on your hard disk. Then finally the picture may be loaded with the LoadPicture() function.