Storing an image into an Attachment field in an Access database

Question

I'm writing a VB application where I need to store an image in the database. The user selects the image on their computer, which gives me the path as a string. Here's my attempt at it, however I'm getting the error "An INSERT INTO query cannot contain a multi-valued field."

Here is my code:

Dim buff As Byte() = Nothing
Public Function ReadByteArrayFromFile(ByVal fileName As String) As Byte()
    Dim fs As New FileStream(fileName, FileMode.Open, FileAccess.Read)
    Dim br As New BinaryReader(fs)
    Dim numBytes As Long = New FileInfo(fileName).Length
    buff = br.ReadBytes(CInt(numBytes))
    Return buff
End Function

Sub ....
    Dim connImg As New OleDbConnection
    Dim sConnString As String
    Dim cmdImg As New OleDbCommand

    sConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Settings.DB & ";Persist Security Info=False;"
    connImg = New OleDbConnection(sConnString)
    connImg.Open()
    cmdImg.Connection = connImg
    cmdImg.CommandType = CommandType.Text

    If d.slogo <> "" Then
        cmdImg.CommandText = "INSERT INTO Logo ( refId, [type], [img] ) VALUES(@refId, @type, @imgBinary)"
        cmdImg.Parameters.Add("@refId", OleDbType.Double).Value = refId
        cmdImg.Parameters.Add("@type", OleDbType.Double).Value = 0
        cmdImg.Parameters.Add("@imgBinary", OleDbType.VarBinary).Value = ReadByteArrayFromFile(PathToImage)
        cmdImg.ExecuteNonQuery()
    End If
    ....
End Sub

I've tried searching for other solutions online, but it seems everything I find is VB6 or VBA code. And I know people are going to argue that images should not be stored in the database, but in this case, it is my only option.

Thank-you for any help!

Solution 1

As you have discovered, you cannot use a SQL statement to insert files into an Attachment field in an Access database. You have to use the LoadFromFile() method of an ACE DAO Field2 object. The following C# code works for me. It is adapted from the Office Blog entry here.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Access.Dao;

namespace daoConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // This code requires the following COM reference in your project:
            //
            // Microsoft Office 14.0 Access Database Engine Object Library
            //
            var dbe = new DBEngine();
            Database db = dbe.OpenDatabase(@"C:\__tmp\testData.accdb");
            try
            {
                Recordset rstMain = db.OpenRecordset(
                        "SELECT refId, img FROM Logo WHERE refId = 1", 
                        RecordsetTypeEnum.dbOpenDynaset);
                if (rstMain.EOF)
                {
                    // record does not already exist in [Logo] table, so add it
                    rstMain.AddNew();
                    rstMain.Fields["refId"].Value = 1;
                }
                else
                {
                    rstMain.Edit();
                }
                // retrieve Recordset2 object for (potentially multi-valued) [img] field
                //     of the current record in rstMain
                Recordset2 rstAttach = rstMain.Fields["img"].Value;
                rstAttach.AddNew();
                Field2 fldAttach = 
                        (Field2)rstAttach.Fields["FileData"];
                fldAttach.LoadFromFile(@"C:\__tmp\testImage.jpg");
                rstAttach.Update();
                rstAttach.Close();
                rstMain.Update();
                rstMain.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
        }
    }
}

Solution 2

I did the same thing based off of the above code and the blog entry from here.

Here is my vb.net code which allows me to do an OpenFileDialog and multiple selections and the function will handle storing multiple files just fine. Though I did have to add a reference to my project for Microsoft Office 15.0 Access database engine Object Library to get it to work properly. I think you can go down to 12.0 or 14.0 as well.

Private Sub AddAttachment(ByVal Files() As String)
    Const Caller = "AddAttachment"
    Dim dbe = New Microsoft.Office.Interop.Access.Dao.DBEngine()
    Dim db As Microsoft.Office.Interop.Access.Dao.Database
    db = dbe.OpenDatabase(dbPath)
    Try
        Dim rstMain As Microsoft.Office.Interop.Access.Dao.Recordset
        rstMain = db.OpenRecordset("SELECT ID, fieldName FROM tableName WHERE ID = " + (dt.Rows(currentRow).Item("ID").ToString), Microsoft.Office.Interop.Access.Dao.RecordsetTypeEnum.dbOpenDynaset)
        If (rstMain.EOF) Then
            rstMain.AddNew()
            rstMain.Fields("ID").Value = 1
        Else
            For Each value As String In Files
                rstMain.Edit()
                Dim rstAttach As Microsoft.Office.Interop.Access.Dao.Recordset2
                rstAttach = rstMain.Fields("ATTACHMENTS").Value
                rstAttach.AddNew()
                Dim fldAttach As Microsoft.Office.Interop.Access.Dao.Field2
                fldAttach = rstAttach.Fields("FileData")
                fldAttach.LoadFromFile(value)
                rstAttach.Update()
                rstAttach.Close()
            Next
            rstMain.Update()
            rstMain.Close()
        End If

    Catch ex As Exception
        If Err.Number <> 3820 Then
            MsgBox(ex.Message)
        Else
            MsgBox("File of same name already attached", MsgBoxStyle.Critical, "Cannot attach file" & Caller)
            MessageBox.Show(ex.Message)
        End If

    End Try
End Sub

I'm now working on the functions to RemoveAttachments and save files from the attachements field. I'll post those here later.

Comments

[abarisone]

Could you please elaborate more your answer adding a little more description about the solution you provide?