How to Extract Files from an Access Attachment with VBA

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.

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.

[codekabinettcomen]

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.

[FDAbc]

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 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!

[garycurtis]

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.

[johnclark]

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??

[Chrislee-yp]

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.

[BertHarmsma]

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?

[davegoodo]

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.

[roberthendrickson]

'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 = * from AttachmentDemotb") 'my table heisst 'AttachmentDemotb'

rst.AddNew
rst.title = MailID
Set rsAttachments = rst.Fields("Attchmnt").Value
rsAttachments.AddNew
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 :)

[riaankock]

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 = & AttachmentColumnName & _ "FROM" & TableName & _ "Where" & AttachmentColumnName & ".FileName IS NOT NULL")

Do Until rsMainRecords.EOF

Set rsAttachments =

Do Until rsAttachments.EOF
Dim outputFileName As String

outputFileName =
outputFileName = ToDirectory & "\" & outputFileName

outputFileName

rsAttachments.MoveNext

Loop
rsAttachments.Close
rsMainRecords.Close

Loop

Set rsAttachments = Nothing
Set rsMainRecords = Nothing

End Sub

[michellebigham]

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!!!

[prasannaa]

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.

[amberbeach]

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!

[Dr_Mel]

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.