CODEWITHSUNDEEP
vbams-accessfilenamesattachmentms-access-2013
Evis03
Evis03

Reputation: 17

get filename from access attachment

I've got a bit of VBA running in access. Its purpose is to output a pipe delineated string in order to provide exports/back ups of existing tables. It's working well but there's another feature I'd like to add. One of the tables it may be run against contains attachments and I would like the attachment filename to appear under the 'attachment' field. Currently the code just sticks a warning in there but I'd like something more relevant.

The code currently reads each field name in the designated table, splitting them with a pipe (|). It then goes to a new line and repeats the process with the values in each field. Relevant bit of code below:

Set rs = CurrentDb.OpenRecordset(Table, dbOpenSnapshot)
rs.Filter = srchString
Set rsFilt = rs.OpenRecordset()
fieldCount = rsFilt.Fields.Count
recordTot = rsFilt.RecordCount

If recordTot <> 0 Then

    rsFilt.MoveFirst

    Dim o As Integer

    For o = 0 To fieldCount - 1
        fieldNames = fieldNames & rsFilt.Fields(o).Name & "|"
    Next o


    Do While rsFilt.EOF = False
        For o = 0 To fieldCount - 1
            If rsFilt.Fields(o).Type <> 101 Then
                oldDataSet = oldDataSet & Nz(rsFilt.Fields(o).Value, "") & "|"
            Else
                oldDataSet = oldDataSet & "attached files not saved in logs" & "|"
            End If
        Next o
        oldDataSet = oldDataSet & vbNewLine
        rsFilt.MoveNext

    Loop
Else

    fieldNames = "No " & Table & " for this member."

End If

createRecordSnapshot = fieldNames & vbNewLine & oldDataSet

End Function

Each record has only one attachment associated with it. I'd like some way to fetch that filename as a string.

Many thanks,

Upvotes: 1

Views: 1313

Answers (1)

Cindy Meister
Cindy Meister

Reputation: 25663

As best I can tell, the only way to access this information is through an Attachment control on a Form. You could make a simple form with just this control on it, load it as part of your code and access it in the loop.

Here's an example of looping through the records in a form and reading the filename property of the Attachment control:

Dim frm As Form
Dim ctl As Attachment
Dim i As Long, j As Long

Set frm = Application.Forms("Form1")
Set ctl = frm.Controls("test") 'An Attachment control
frm.RecordsetClone.MoveLast
i = frm.Recordset.RecordCount
For j = 0 To i - 1
    Debug.Print ctl.FileName
    DoCmd.GoToRecord acDataForm, frm.Name, acNext
Next

Upvotes: 2

Related Questions