Inserting images from VBA

Inserting images into a SQL Compact from VB(A) does not appear to be possible using vanilla VBA code (please correct me if I am wrong). In order to work around this limitation, it is possible to create a function in C#, and call this function from VBA. This excellent post got me going. This is the VBA Code:

Sub InsertBlob()
    
    Dim IMG_FILE As String
    Dim intFile As Integer
    Dim ImgBuff() As Byte
    Dim ImgLen As Long
    Dim ret As String
    
    IMG_FILE = "C:\ErikEJ.jpg"
    'Read/Store GIF file in ByteArray
    intFile = FreeFile
    Open IMG_FILE For Binary As #intFile
    ImgLen = LOF(intFile)
    ReDim ImgBuff(ImgLen) As Byte
    Get #intFile, , ImgBuff()
    Close #intFile
       
    Dim sqlCeUtil As New SqlCompactUtility
    ret = sqlCeUtil.SaveBlob(ImgBuff(), "Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture) VALUES (@Blob)", "@Blob")
 
End Sub

First a Byte array with the image contents is created. Then the .NET based method (SaveBlob) is invoked.

The SaveBlob method takes 4 parameters:

1. The byte array

2. The SQL Compact connection string

3. The Insert statement

4. The parameter name

In order for this to work, add a reference to the .NET dll library from the VB(A) project, the code of which is shown below (created per the instructions in the "A Basic Walk Through" part of the post mentioned above). If you have followed the instruction in the blog, the DLL should appear in the Add Reference dialog once built.

using System;
using System.Data.SqlServerCe;
using System.Runtime.InteropServices;

namespace SqlCompactInterop
{
    public class SqlCompactUtility
    {
        //SaveBlob(data, @"Data Source=C:\Data\SQLCE\NorthWind2.sdf", "INSERT INTO Images (Picture, Id) VALUES (@Blob, 2)", "@Blob");
        public string SaveBlob(byte[] data, string connectionString, string insertStatement, string parameterName)
        {
            try
            {
                using (SqlCeConnection conn = new SqlCeConnection(connectionString))
                {
                    SqlCeCommand cmd = new SqlCeCommand(insertStatement);
                    cmd.Parameters.Add(new SqlCeParameter(parameterName, System.Data.SqlDbType.Image, data.Length));
                    cmd.Parameters[parameterName].Value = data;
                    cmd.Connection = conn;
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();
                    return string.Empty;
                }
            }
            catch (SqlCeException e)
            {
                return ShowErrors(e);
            }
        }

        // Error handling routine that generates an error message
        private string ShowErrors(SqlCeException e)
        {
            SqlCeErrorCollection errorCollection = e.Errors;

            System.Text.StringBuilder bld = new System.Text.StringBuilder();
            Exception inner = e.InnerException;

            if (null != inner)
            {
                bld.Append(inner.ToString());
            }
            // Enumerate the errors to a message box.
            foreach (SqlCeError err in errorCollection)
            {
                bld.Append("\n Error Code: " + err.HResult.ToString("X"));
                bld.Append("\n Message   : " + err.Message);
                bld.Append("\n Minor Err.: " + err.NativeError);
                bld.Append("\n Source    : " + err.Source);

                // Enumerate each numeric parameter for the error.
                foreach (int numPar in err.NumericErrorParameters)
                {
                    if (0 != numPar) bld.Append("\n Num. Par. : " + numPar);
                }

                // Enumerate each string parameter for the error.
                foreach (string errPar in err.ErrorParameters)
                {
                    if (String.Empty != errPar) bld.Append("\n Err. Par. : " + errPar);
                }

             
            }
            return bld.ToString();
        }
    
    }

}
 

"Hope this helps"