How to Extract Files from an Access Attachment with VBA
HTML-код
- Опубликовано: 31 июл 2024
- The #Attachment data type in Microsoft #Access tables is a useful and convenient way to store files together with associated records in your #MsAccess database.
In this video I show how you can extract all the files from any Attachment column in any table in your database back to a folder in the file system with VBA.
A sample file containing the procedures shown in this video can be downloaded here: codekabinett.com/download/Extr...
00:00 Intro
00:55 Scenarios when to extract files from an Attachment field
04:50 How Attachments work in tables and queries
11:15 How to Extract Attachments with VBA
23:03 How to Extract Attachments from linked SharePoint lists
----------------------------------------------------
I created a complete online course "Learning VBA Programming". A course for beginners and intermediates in VBA to learn from the ground up.
If you want to know more about the course: codekabinett.com/courses/inde... 
Наука
I'm in the mood for a little experiment. I offer a 40% discount on my online VBA Programming Course codekabinett.teachable.com/p/learning-vba-programming-in-microsoft-access
Use the coupon code YTEXP202404 to get the discount. - This is only valid until 2024-06-30 and only for the first 5 people using this coupon code.
Thanks! That was just what I needed. Clearly explaining that the attachment field is itself a rowset clarifies a lot. Caveat: I tried creating a routine that passed the field itself as a parameter (iFld as Field) and it failed - for some reason when passing as a param it loses that it is an attachment. When I pass the rowset (record) and the name, then extract it (iRs.Fields(iStrFieldName).Value) it can be assigned to a Rowset.
I write a LOT of functions that I assemble like Legos when I build my app so I try to make them atomic and reusable.
You rock, dude!
Thank you!
I like your Lego style approach.
With the introduction of the attachment fields there was a new object type introduced to support the new capabilities: DAO.Field2
Declare your procedure argument as (iFld As Field2) and it should work as you intended.
Excellent as always! Thank you!
wow ! interesting. Thank you uncle
Uncle? 😳🤣
Thanks
Good morning Philipp. I almost skipped this video because I have worked with Attachment and Multi-Value fields and was familiar with the process. I am so glad I didn't because, as someone who uses SharePoint as a BE, I am sure you will agree that there is not much "out there" that address how to make Access and SharePoint play nice with each other. That little "Oh, By The Way" at the end was priceless, thanks and please include more SP nuggets in the future.
Does your course cover SharePoint issues? Also, what is the deal with you broken clock - does 0644/1844 have a significance??
John, thank you for the feedback! :-)
In my Learning VBA Programming course I focus on VBA an try to teach it "in a vacuum", with as little dependencies on any external libraries as possible. So, I don't mention SharePoint in there at all.
The clock...
I read your comment immediately before going shopping and finally remembered to buy a new battery for it. - Thank you! :-) Still, it has issues...
Hi, great explanation. Sample download link doesnt seem to work though?
Would like to use this in conjunction with a Contacts Database containing photos as attachments, to export images to a .vdf contact card.
Thanks so much for the video! I think I copied all of your code correctly but I get a compile error that says User-defined type not defined and points to the line rsMainRecords As DAO.Recordset2> What have I done wrong? Is this code available for copy/paste? Thank you!!!
Hi Philipp I have copied the code but get a syntax error highlighting
Public Sub .... and Set rsMainrecord
My table name containing the attcahments is Mediposdata and my attachment field column name is VeriAttach.
I have included ExtractAllAttachments "Mediposdata","VeriAttach","C:\tmp\AttachmentExport" in the immediate section.
What could be the problem?? Your assistance apprecaited!
Option Compare Database
Option Explicit
Public Sub ExtractAllAttachments(ByVal TableName As String, ByVal AttachmentColumnName As String, ByVal ToDirectory As String)
Dim rsMainRecords As DAO.Recordset2
Dim rsAttachments As DAO.Recordset2
Set rsMainRecords = CurrentDb.OpenRecordset("SELECT" & AttachmentColumnName & _ "FROM" & TableName & _ "Where" & AttachmentColumnName & ".FileName IS NOT NULL")
Do Until rsMainRecords.EOF
Set rsAttachments = rsMainRecords.Fields(AttachmentColumnName).Value
Do Until rsAttachments.EOF
Dim outputFileName As String
outputFileName = rsAttachments.Fields("FileName").Value
outputFileName = ToDirectory & "\" & outputFileName
rsAttachments.Fields("FileData").SaveToFile outputFileName
rsAttachments.MoveNext
Loop
rsAttachments.Close
rsMainRecords.Close
Loop
Set rsAttachments = Nothing
Set rsMainRecords = Nothing
End Sub
Excellent video, this is exactly what I need, thank you so much. I do have a question, I need to know how to set the "TableName, AttachmentColumnName, ToDirectory" and call the public sub.
Thank you very much for your feedback!
You would need to pass in the values for the procedure arguments in your code.
Here is an example with hardcoded values:
Private Sub YourButton_Click()
Dim TableName as String
Dim AttachmentColumnName As String
Dim ToDirectory As String
TableName = "YourTable"
AttachmentColumnName = "ColumnWithTheAttachments"
ToDirectory = "C:\Temp\OutputDirectory"
ExtractAllAttachments TableName, AttachmentColumnName, ToDirectory
End Sub
Of course, you can also get the values from controls in your form:
Private Sub YourButton_Click()
Dim TableName as String
Dim AttachmentColumnName As String
Dim ToDirectory As String
TableName = Me.TxtTableName.Value
AttachmentColumnName = Me.TxtColumnName.Value
ToDirectory = Me.TxtDirectory.Value
ExtractAllAttachments TableName, AttachmentColumnName, ToDirectory
End Sub
I used the names TableName, AttachmentColumnName, ToDirectory and for my variables, but these are names are not relevant beyond being recognizable and meaningful. They are passed to the ExtractAllAttachments procedure just by their position in the procedure call.
Passing parameter values to procedures is an essential programming concept. If you want to learn more about it and other important concepts, I recommend my Online VBA Programming Course. -> codekabinett.teachable.com/p/learning-vba-programming-in-microsoft-access
Thanks for the video. Its very helpful. One quick question, can you tell me what code I could add to skip duplicate files? Currently, I get a run time error 3839: The specified file already exists. Thanks!
Delete the file first, or use the Dir function to see if it exists and skip the save if it does.
Hello, I have started learning MS Access through your videos and it's extremely helpful to me. First of all thank you very much for these videos. I need you help to understand on a topic related to attachments. I have created a table with some content as well as attachments as I want to reduce the usage of excel. when I generate the report, it is showing the attachment name like abc.msg as I have attached an email. but what I am rooting for is the entire attachment to be available in the report so that it will be very easy to maintain and share it with concerned people. Is it possible? if yes, please guide me on how to do it.
Unfortunately, the .msg file format is a proprietary Microsoft Outlook format and you can't process .msg files without involving Outlook. I'm not aware of any control that can display .msg files in an Access form or report.
Hi Phillip, will this video be continued with, for example, how to free up space in the database by removing the attachments and adding a field with a reference where the file can now be found?
Thank you for the suggestion. I hadn't planned for continuing the topic, but showing how to work with linked file system documents would be a good topic for a future video. I put it on my list (no guarantees!).
Freeing up the space is simple however. Just delete the column (or whole table) containing the Attachment column and then Compact&Repair your database.
@@codekabinettcomen I am pretty sure I wrote some code a while ago to automatically compact an Access DB.
I watched this almost by accident. I was looking for info on dealing with images in Access. I watched a video that recommended using the Attachment field for these. But that now sounds like a bad idea. I'm wanting to put pictures/images into some field. But the last video I watched suggested putting a text field with a Windows file path to the file system which then runs the On Current event to call the file in, thus avoiding the need to store images in Access. Is that possible? I'm now a bit confused about finding a way forward with dealing with images. Thanks for your video and I might be interested in your VBA course in the future.
Hi Dave! Databases in general are not the best location to store images. With an MS-Access backend file you application already needs mandatory access to the file system. So, there is one reason less to put images into the database.
As a rule of thumb, if you've got a few images that are part of core application functionality, such as icons, logos, an so on, storing those in the database in Attachment fields is fine.
However, if you expect a potentially large and growing number of image files, I'd rather store them in the file system and only store the file name/path in the database. The root path of your image storage location in the file system should be configurable to allow you to move the image files to different location without needing to update all the records in the database.
And yes, you can use the store file path to assign the actual image file to an image control in the OnCurrent event.
Watch out. Remember his use case that said when you hit the 2GB limit on your database your favorite pet dies. Oh and the DB becomes unusable.
'Vielen lieben dank dafür! ich wollte eigentlich andersrum, die janzen Dateien von einem Ordner einlesen. Und / aber dies hat mir sehr dabei geholfen diese rst in einer rst zu verstehen...
'nun geht's leicht mit:
Sub upload_file_to_access_db(MailID, Path, FileName) 'MailID ist bloss Eine eindeutige combo von Emfangszeit, Betreff und sender
Dim rst As DAO.Recordset2
Dim rsAttachments As DAO.Recordset2
Set rst = CurrentDb.OpenRecordset("select * from AttachmentDemotb") 'my table heisst 'AttachmentDemotb'
rst.AddNew
rst.title = MailID
Set rsAttachments = rst.Fields("Attchmnt").Value
rsAttachments.AddNew
rsAttachments.Fields("FileData").LoadFromFile Path & FileName
rsAttachments.Update
rst.Update
rst.Close
Set rst = Nothing
Set rsAttachments = Nothing
End Sub 'Geile sache! danke und Gruße aus N. Carolina :)
Thank you very much for your feedback and the sample code!
Nice work. But now I have an issue, I need to relate all of these files to their original records so my users know where the file was originally in the database. They didn't always use very good names for the files. Is there an easy way to concatenate the ID field to the output name? I tried doing so myself and seemed to be messing it up somehow.
Thanks.
At the same time when you read the Attachment, you also must read the ID of the main record. The store the ID and the link to the extracted Attachment together in a new table.
Hi @Dr_Mel, did you ever have any luck with this? I need to do a similar thing- pull another field from the table for use in the filename. But that variable assignment is perplexing. I don't know VBA at all, and this is a one-off thing for me as we migrate to a SaaS system...thanks!
@@buckyswider1 Wish I remembered, it's been about 2 years. I think I ended up batch renaming the files as a work around once they got extracted. I sorted the attachments to be in the same order as the table with all attachments shown as a separate record, then created a batch file to rename all of the files with the ID appended to the front. Philip indicated there's a way to do this during the initial extraction procedure. I've since moved on from working very much with VBA so I'm not much of a resource. If Philip responds he could probably explain what to do.
@@Dr_Mel thanks for the reply!
Ok, I figured it out! Added a variable for the field name (I used the ProID for this example; there's implications if a column has a null value which I don't have to worry about it my use case) in both the calling routine and the subroutine. Defined a variable as a string for my output, added the field to the select statement, added a "let" statement under the "Set": "Let rsProID = rsMainRecords.Fields(ProID).Value", and used the variable in my outfile name. My error earlier was using "Set" instead of "Let" (or just leaving that out as it's implied)- "Set" is for objects only, and since my variable is a string, VBA didn't like that. Woo Hoo!
😌 ｐｒｏｍｏｓｍ