UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Get Filenames In An Attachment, Access 2016    
 
   
Ron38
post Mar 29 2020, 08:50 AM
Post#1



Posts: 260
Joined: 19-August 10
From: New Hampshire


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
Go to the top of the page
 
cheekybuddha
post Mar 29 2020, 09:22 AM
Post#2


UtterAccess Moderator
Posts: 12,815
Joined: 6-December 03
From: Telegraph Hill


Hi Ron,

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

Have a look at this site

Shout back if you have any issues.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
DanielPineault
post Mar 29 2020, 09:37 AM
Post#3


UtterAccess VIP
Posts: 7,344
Joined: 30-June 11



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...ent-data-types/
https://www.devhut.net/2018/05/07/ms-access...-using-a-query/
https://www.devhut.net/2014/02/11/ms-access...tachment-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-at...ccess-database/

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
Ron38
post Mar 30 2020, 08:25 AM
Post#4



Posts: 260
Joined: 19-August 10
From: New Hampshire


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
Go to the top of the page
 
Ron38
post Mar 30 2020, 02:22 PM
Post#5



Posts: 260
Joined: 19-August 10
From: New Hampshire


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.
Go to the top of the page
 
tina t
post Mar 30 2020, 02:33 PM
Post#6



Posts: 6,604
Joined: 11-November 10
From: SoCal, USA


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

--------------------
"the wheel never stops turning"
Go to the top of the page
 
DanielPineault
post Mar 30 2020, 04:21 PM
Post#7


UtterAccess VIP
Posts: 7,344
Joined: 30-June 11



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

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
ADezii
post Mar 30 2020, 05:35 PM
Post#8



Posts: 2,995
Joined: 4-February 07
From: USA, Florida, Delray Beach


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.
This post has been edited by ADezii: Mar 30 2020, 06:29 PM
Go to the top of the page
 
orange999
post Mar 30 2020, 08:27 PM
Post#9



Posts: 2,099
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Ron,

There is a M$oft example function to ListFileNames and filetypes in Attachment field here. See the sample Sub ListAttachments()
Similar to Daniel's code.
This post has been edited by orange999: Mar 30 2020, 08:28 PM

--------------------
Good luck with your project!
Go to the top of the page
 
Ron38
post Apr 8 2020, 01:31 PM
Post#10



Posts: 260
Joined: 19-August 10
From: New Hampshire


Thanks to all who responded. Certainly enough info there to get my work accomplished!!!
Go to the top of the page
 
Ron38
post Apr 9 2020, 11:18 AM
Post#11



Posts: 260
Joined: 19-August 10
From: New Hampshire


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.
Go to the top of the page
 
ADezii
post Apr 9 2020, 11:58 AM
Post#12



Posts: 2,995
Joined: 4-February 07
From: USA, Florida, Delray Beach


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.
This post has been edited by ADezii: Apr 9 2020, 11:59 AM
Go to the top of the page
 
tina t
post Apr 9 2020, 01:56 PM
Post#13



Posts: 6,604
Joined: 11-November 10
From: SoCal, USA


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?

--------------------
"the wheel never stops turning"
Go to the top of the page
 
Ron38
post Apr 9 2020, 02:02 PM
Post#14



Posts: 260
Joined: 19-August 10
From: New Hampshire


Yes, would like to see it!!!
Ron
Go to the top of the page
 
ADezii
post Apr 9 2020, 03:33 PM
Post#15



Posts: 2,995
Joined: 4-February 07
From: USA, Florida, Delray Beach


For what it's worth:
Attached File(s)
Attached File  The_Complete_BLOB.zip ( 1.49MB )Number of downloads: 3
 
Go to the top of the page
 
Ron38
post Apr 9 2020, 03:42 PM
Post#16



Posts: 260
Joined: 19-August 10
From: New Hampshire


Thx!
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    31st May 2020 - 10:59 AM