Search code examples
sql-serverobjectms-accessvbaole

Saving OLE Objects in Access field with VBA

I know that this subject has been covered to large extend, but I have not been able to find a solution to my particular problem.

I have a table with a column Attachments of OLE Object data type. The back end of the table is SQL Server table with VARBINARY(MAX) data type for Attachments column.

If I right-click on the Attachments field in Access, a menu pops-up with an option to Insert Object... Following this path I could insert a file in the field.

enter image description here

enter image description here

The file inserted this way could be opened for viewing and editing just by double-clicking the field.

enter image description here

Now. I need to do the same using VBA. I need to take the list of files and insert them in the Attachments field in the appropriate rows. This should not be a difficult task as it is widely known how to insert a file in a field using ADODB.Stream. The following is a simple code to try the concept:

Private Sub POC()

    Dim db As DAO.Database
    Dim rsa As DAO.Recordset
    Dim stream As ADODB.stream

    Set db = CurrentDb()
    Set rsa = db.OpenRecordset("ZipCodeAttachments", dbOpenDynaset, dbSeeChanges)

    rsa.MoveFirst
    rsa.MoveNext

    rsa.Edit

    Set stream = New ADODB.stream
    stream.Type = adTypeBinary
    stream.Open
    stream.LoadFromFile Application.CurrentProject.Path & "\Attachments\537.zip"

    rsa.Fields("Attachments").value = stream.Read

    rsa.Update
    rsa.Close

    Set rsa = Nothing
    Set dba = Nothing
End Sub

The code inserts a file in the Attachments field of the second row. I could validated that value has been added via SSMS. However, when I try to open the field for viewing and editing as I did earlier with the first row, this time I am getting an error:

enter image description here

Clearly, there is something wrong with the way the file is saved with VBA.

What am I doing wrong? How to achieve the same result with VBA as I get with Access user interface?

Solution

  • If you want to store a file as an OLE Package shell object, doing some GUI coding (opening a form with an OLE object, then using that to store the file) is the only way as far as I know.

    Create an unbound form called frmLoadOLEObj, with on it a bound OLE object called MyBoundOLEFrame.

    On the form, add the following code:

    Public Sub SaveOLEObj(rs As DAO.Recordset, fldName As String, FileName As Variant)
    'Save the position of the recordset
    Dim bkmrk As Variant
    bkmrk = rs.Bookmark
    'Bind the form to the recordset
    Set Me.Recordset = rs
    'Move the form to the saved position
    Me.Bookmark = bkmrk
    'Bind the OLE frame to the field
    MyBoundOLEFrame.ControlSource = fldName
    MyBoundOLEFrame.Class = "Package"
    'Load the attachment into the OLE frame
    MyBoundOLEFrame.SourceDoc = FileName
    MyBoundOLEFrame.Action = acOLECreateEmbed
End Sub

    Then, to add a file to a record:

    Dim rsa As DAO.Recordset
Set rsa = CurrentDb.OpenRecordset("ZipCodeAttachments", dbOpenDynaset, dbSeeChanges)
Dim frmOLE As New Form_frmLoadOLEObj
frmOLE.SaveOLEObj rs, "Attachments", Application.CurrentProject.Path & "\Attachments\537.zip"

    As you can see, this is very "hacky" code, because it runs GUI operations, and you have code on a form that is not a form, but really a module, but you need a form to put the control on because you can't have the control without the form. I'd rather have a BLOB any day.