Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Modules _ Get Filenames In An Attachment

Posted by: Ron38 Mar 29 2020, 08:50 AM

I would like to get the names of any files in a given attachment. Any sample code on how that gets done?
Documentation seems sparse...
Thx!
Ron

Posted by: cheekybuddha Mar 29 2020, 09:22 AM

Hi Ron,

I'm assuming you are talking about Attachment fields in Access rather than, say, an email attachment.

Have a look https://sourcedaddy.com/ms-access/working-with-attachment-fields.html

Shout back if you have any issues.

hth,

d

Posted by: DanielPineault Mar 29 2020, 09:37 AM

Code or Query?

Query is really simple:

CODE
SELECT YourFieldName.FileName
FROM YourTableName;


That all said, you should avoid attachments as much as possible. You are much better served simply storing the path/filename to files on the network rather than embedding the files within the database itself. As you can see, everything becomes more complex (managing attchments, coding for anything relating to them, ...).

Some useful posts on attachments:
https://www.devhut.net/2017/07/11/ms-access-working-with-attachment-data-types/
https://www.devhut.net/2018/05/07/ms-access-delete-attachments-using-a-query/
https://www.devhut.net/2014/02/11/ms-access-number-of-attachments-in-an-attachment-field/
https://www.devhut.net/2018/02/02/access-attachment-gallery/

The recommended way to handle attachments:
https://www.devhut.net/2016/10/03/adding-attachments-to-an-access-database/

Posted by: Ron38 Mar 30 2020, 08:25 AM

I have a Veteran's database for clients who need financial assistance. we require certain documents to back up their claims. We simply need the documents, for viewing only. Attachments allow us to see the documents right there when we are looking at the client. Putting the docs in a separate folder that we have to look at separately just makes more work, and we are all volunteers. We are small and our 13 year old database has only 2400 records, so not really worried about bloat!
But I was very interested in your suggestion to store separately, under other circumstances. Doesn't it add to the workload to have them outside the database? Separate lookup, looking at docs outside the database?
Ron

Posted by: Ron38 Mar 30 2020, 02:22 PM

Me!attClientAttachment.FileName gets me a filename just entered, but not 2 or more if more than 1 is entered. Since I only want it for a comment, not a big deal.

Posted by: tina t Mar 30 2020, 02:33 PM

QUOTE
But I was very interested in your suggestion to store separately, under other circumstances. Doesn't it add to the workload to have them outside the database? Separate lookup, looking at docs outside the database?

i don't see how, really. if you store a path to a pdf file, for instance, and have a button on a form (or double click a record, or whatever suits) to open the pdf, as

Dim strMyPath

strMyPath = Me!MyFieldNameThatHasPath
Application.FollowHyperlink strMyPath

then the file opens in the default software, such as Adobe Reader, and you look at it. Alt+Tab should switch between the open database FE app and the file you opened.

hth
tina

Posted by: DanielPineault Mar 30 2020, 04:21 PM

No, using just the path can be just as transparent with no extra work for the user.

As for listing the attachment file names using standard attachments, you can do something along the lines of

CODE
    Dim rst As DAO.Recordset
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    
    Set rst = Me.RecordsetClone 'Grab the form's recordset to work with
    rst.FindFirst "[YourPKFieldName] = " & Me.[YourPKFieldName] 'Get the current record specifically
    Set fld = rst("attClientAttachment") 'Get the attachment field specifically
    Set rsA = fld.Value
    'Loop throught the attachments
    Do While Not rsA.EOF
        Debug.Print rsA("FileName")
        rsA.MoveNext
    Loop
    
    Set rsA = Nothing
    Set fld = Nothing
    Set rst = Nothing

Posted by: ADezii Mar 30 2020, 05:35 PM

Another option can be to store the Files as BLOBs (Binary Large Objects) in an OLE Object Field resulting in the Files being stored within the DB and no or very little bloat. This approach is more complex and involves loading the File's contents into an ADODB Binary Stream and reversing the process for viewing. No Bloating, No External File Paths, No Attachments, etc. For your specific needs, namely viewing only, I feel that this approach would be ideal for you.

Posted by: orange999 Mar 30 2020, 08:27 PM

Ron,

There is a M$oft example function to ListFileNames and filetypes in Attachment field https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/field2-object-dao#example. See the sample Sub ListAttachments()
Similar to Daniel's code.

Posted by: Ron38 Apr 8 2020, 01:31 PM

Thanks to all who responded. Certainly enough info there to get my work accomplished!!!

Posted by: Ron38 Apr 9 2020, 11:18 AM

For some reason, my database crashed when I tried to implement attachments: really wonky. My testing at home went fine, but Wow!
I think I will take the other approach of storing paths.

Posted by: ADezii Apr 9 2020, 11:58 AM

Before you make your decision, here is a little more information on the BLOB approach:

QUOTE
Here is an Article that I wrote some time ago based on Alan Warren’s Demonstration Database on his Web Site. It provides an insight into this concept and the attached Demo provides a good example on how to implement this functionality. It was originally written to resolve the age-old question of ‘How to Store Graphic Images in a Database’, but I also modified the Code to include *.doc, *.docx, and *.pdfs.
Storing Pictures as BLOBs in the Database is the most efficient way on containing Image data because the Picture data is stored byte for byte, exact size, with the Images reflecting their true sizes.
When BLOBs are stored in the Database with other data, BLOB and tabular data are backed up and recovered together and are synchronized, there are no File Paths to contend with, and no resultant Database bloating. Data consistency is ensured because INSERT, UPDATE, and DELETE operations occur in the same transaction. Separate security measures need not be created since BLOB and regular data coexist.
The minimal requirement to implement the BLOB technique is two Functions, one to put files into the Database, and the other to take them out. You can use either DAO or ADO to grab the Image File and read it into an OLE Field (1st Function). To display the Image, we have to extract it from the Database (2nd Function) into a Temporary File then use an Image Control to display it by setting the Control's Picture property to the Path of the Temp File. Non-Graphic Files are also extracted into a Temporary File then viewed using the FollowHyperlink Method.

P.S. - If you like, I can Upload a Demo that will illustrate this approach.

Posted by: tina t Apr 9 2020, 01:56 PM

hi ADezii, if Ron doesn't take up your offer, will you go ahead and upload your demo for the rest of us, pls? :) tia, tina

ps. and btw, sounds like maybe a good addition to UA's code archive?

Posted by: Ron38 Apr 9 2020, 02:02 PM

Yes, would like to see it!!!
Ron

Posted by: ADezii Apr 9 2020, 03:33 PM

For what it's worth:

 The_Complete_BLOB.zip ( 1.49MB ): 3
 

Posted by: Ron38 Apr 9 2020, 03:42 PM

Thx!