Assign Attachment Field To Variable in Access 2010

Question

I'm trying to understand how to work with the new Attachment field that is available in Access 2010. I would like to assign the value from the table directly into a variable. I know that I can do this if I use an intermediary form, but this seems like sloppy coding to rely on a form in order to grab a value from a table. Is there some way to grab what is in an attachment field and assign it directly to a variable? I have multiple instances where this would be handy for me. The first instance is I want to grab a photo stored in an attachment field to assign to the ribbon. A second instance is to load a company logo from a table into a variable and keep it in memory to use throughout the program as needed.

The code I have so far is this, but it gives me a type mismatch error:

Dim ParentRS As Recordset, ChildRS As Recordset, Img As Attachment
Set ParentRS = CurrentDb.OpenRecordset("SELECT * FROM LtblImg;", dbOpenSnapshot)
If ParentRS.RecordCount > 0 Then
    Set ChildRS = ParentRS("Img").Value
    If ChildRS.RecordCount > 0 Then
        Set Img = ChildRS("FileData")
    End If
    ChildRS.Close
End If
ParentRS.Close

Answer 1

Yes, Dim Img As Attachment looks tempting, but Attachment (which is actually Access.Attachment) refers to an Attachment control that could be used on a form (just like Access.TextBox) and does not appear to be suitable for your intended purpose.

The only native VBA type for storing this sort of binary data is an array of Byte values, but often when dealing with byte arrays we wind up looping through and processing them byte-by-byte, which is tedious and inefficient.

You might consider using a binary ADODB.Stream object as your "variable". You could create a function to retrieve the attachment bytes and return them in a Stream like so

Option Compare Database
Option Explicit

Public Function GetLogoAsStream() As ADODB.Stream
    Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, fldAttach As DAO.Field2
    ' Project references required for early binding:
    '     Windows Script Host Object Model
    '     Microsoft ActiveX Data Objects 2.8 Library
    Dim fso As FileSystemObject, tempFileSpec As String
    Static strm As ADODB.Stream

    If strm Is Nothing Then
        Set fso = New FileSystemObject
        tempFileSpec = fso.GetSpecialFolder(TemporaryFolder) & "\" & fso.GetTempName
        Set fso = Nothing

        Set cdb = CurrentDb
        Set rstMain = cdb.OpenRecordset( _
                "SELECT [AttachmentFiles] " & _
                "FROM [AttachmentsTable] " & _
                "WHERE [Description]='SO logo'", _
                dbOpenSnapshot)
        Set rstAttach = rstMain("AttachmentFiles").Value
        ' make sure we use the correct file extension
        tempFileSpec = tempFileSpec & "." & rstAttach.Fields("FileType").Value
        Set fldAttach = rstAttach.Fields("FileData")
        fldAttach.SaveToFile tempFileSpec
        Set fldAttach = Nothing
        rstAttach.Close
        Set rstAttach = Nothing
        rstMain.Close
        Set rstMain = Nothing
        Set cdb = Nothing

        Set strm = New ADODB.Stream
        strm.Type = adTypeBinary
        strm.Open
        strm.LoadFromFile tempFileSpec
        Kill tempFileSpec
    End If
    strm.Position = 0
    Set GetLogoAsStream = strm
End Function

and then if you had, say, a Report like this with an empty Image control

and an On Load event procedure like this to load the Image control's .PictureData from your "variable" (actually a Function returning an ADODB.Stream)

Private Sub Report_Load()
    Me.LogoImage.PictureData = GetLogoAsStream.Read
End Sub

it could produce something like this