Storing Images in Access Databases

Although the Data Control and ADODC are best avoided, this is one place where their use is very helpful. If you bind a picture box to them, then images stored in the database will be displyed automatically as the control moves through the recordset.

Additionally, in the case of the DAO Data Control, images can be added / edited directly to the database via the Data Control, in the same way as text in a bound textbox. This is NOT possible with the ADODC, so the .AppendChunk method has to be used. According to this article, even displaying is not possible, but in practice it does work.

Download Here

When starting the project the following form appears, and allows you to choose to use either DAO or ADO:

The code behind this is:

Option Explicit

Private Sub cmdADO_Click()
    frmImages.fUseDAO = False
    frmImages.Show
    Unload Me
End Sub
Private Sub cmdDAO_Click()
    frmImages.fUseDAO = True
    frmImages.Show
    Unload Me
End Sub
Private Sub Form_Load()
    'Move to centre screen
    Me.Move (Screen.Width - Me.Width) / 2, (Screen.Height - Me.Height) / 2
End Sub

This form simply sets the Public variable fUseDAO on the main form to True if the "Use DAO" button is clicked. The main form is then loaded. If the database already contains images, then an image will be displayed, and the Listbox will have at least one entry:

The code on Form_Load() is:

Private Sub Form_Load()
    Dim strPath As String
    Dim strFile As String
    Dim iCount As Integer
    
    'centre form on screen
    Me.Move (Screen.Width - Me.Width) \ 2, (Screen.Height - Me.Height) \ 2
    Me.Show
    
    'disable both controls
    Data1.Enabled = False
    Adodc1.Enabled = False
    'align controls in control arrays, which were drawn at design
    'time in offset positions to make the project layout clearer
    With picDisplay(0)
        .Visible = False
        .AutoRedraw = True
        .AutoSize = True
        picDisplay(1).Visible = .Visible
        picDisplay(1).Top = .Top
        picDisplay(1).Left = .Left
        picDisplay(1).Width = .Width
        picDisplay(1).Height = .Height
        picDisplay(1).AutoRedraw = .AutoRedraw
        picDisplay(1).AutoSize = .AutoSize
    End With
    With txtName(0)
        .Visible = False
        txtName(1).Visible = .Visible
        txtName(1).Top = .Top
        txtName(1).Left = .Left
        txtName(1).Width = .Width
        txtName(1).Height = .Height
    End With
    With txtPath(0)
        .Visible = False
        txtPath(1).Visible = .Visible
        txtPath(1).Top = .Top
        txtPath(1).Left = .Left
        txtPath(1).Width = .Width
        txtPath(1).Height = .Height
    End With
    
    If fUseDAO Then
        'set up data control
        With Data1
            .Enabled = True
            .DatabaseName = App.Path & "\Images.mdb"
            .RecordsetType = vbRSTypeTable
            .RecordSource = "Images"
            .Refresh
        End With
        
        'set up code database connection
        Set db = OpenDatabase(Data1.DatabaseName)
        Set rs = db.OpenRecordset("Images", dbOpenTable)
        
        'set indexes
        rs.Index = "Name"
        Data1.Recordset.Index = "Name"
        
        'make bound controls visible
        picDisplay(0).Visible = True
        txtName(0).Visible = True
        txtPath(0).Visible = True
        Me.Caption = "Saving Images in a Database - DAO code/Data Control"
    Else
        'set up ADODC
        With Adodc1
            .Enabled = True
            .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Images.mdb;Persist Security Info=False"
            .CommandType = adCmdTableDirect
            .CursorLocation = adUseServer
            .LockType = adLockOptimistic
            .Mode = adModeShareDenyNone
            .RecordSource = "Images"
            .Refresh
        End With
        
        'set up code database connection
        Set cnn = New ADODB.Connection
        cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Images.mdb;Persist Security Info=False"
        cnn.Open
        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer
        rst.Open "Images", cnn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
        
        
        'set indexes
        rst.Index = "Name"
        Adodc1.Recordset.Index = "Name"
    
        'make bound controls visible
        picDisplay(1).Visible = True
        txtName(1).Visible = True
        txtPath(1).Visible = True
        Me.Caption = "Saving Images in a Database - ADO code and ADODC"
    End If
    
    'fill list with image names
    FillList
End Sub