Solved Export attachments to Folder - Not putting attachments in Folders (1 Viewer)

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
This is in reference to code posted in this post https://www.access-programmers.co.uk/forums/threads/export-attachments-to-folders.299345/
I didn't want to hijack that thread so I started a new one - please let me know if I need to move my questions over to that thread.

So, the following code is working fine as far as pulling the pictures (attachments) and saving them (saving them with the AIS value + file name, and it's doing fine creating the folders titled with the AIS field appropriately; However, it isn't placing the pictures (attachments) inside the appropriate folder. So the output I'm getting now is empty folders (titled properly), and then all the pictures in the same directory as all the empty folders (titled properly). I guess I'm getting my onClick Call wrong. Any advice of the correct way so I can get the pictures put into their appropriate folders? Thanks!

Code:
Public Function SaveAttachmentsTest(strPath As String, Optional strPattern As String = "*.*") As Long
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rsA As DAO.Recordset2
Dim rsB As String
Dim fld As DAO.Field2
Dim OrdID As DAO.Field2
Dim strFullPath As String
Dim thisPath As String
'Get the database, recordset, and attachment field
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Persons")
Set fld = rst("Attachments")
Set OrdID = rst("AIS")

'Navigate through the table
Do While Not rst.EOF

'Get the recordset for the Attachments field
Set rsA = fld.Value
rsB = OrdID.Value

'Save all attachments in the field
Do While Not rsA.EOF
Set OrdID = rst("AIS")
If rsA("FileName") Like strPattern Then
'To Export the data, use the line below
thisPath = Replace(strPath & "" & OrdID & "", "\", "")
subMakePath thisPath
strFullPath = thisPath & rsA("FileName")

'Make sure the file does not exist and save
If Dir(strFullPath) = "" Then
rsA("FileData").SaveToFile strFullPath
End If

'Increment the number of files saved
SaveAttachmentsTest = SaveAttachmentsTest + 1
End If

'Next attachment
rsA.MoveNext
Loop
rsA.Close

'Next record
rst.MoveNext
Loop

rst.Close
dbs.Close

Set fld = Nothing
Set rsA = Nothing
Set rst = Nothing
Set dbs = Nothing


End Function

Private Sub subMakePath(NewPath As String)
Dim var, direc
Dim sPath As String
var = Split(NewPath, "")
For Each direc In var
sPath = sPath & direc & ""
If Dir(sPath, vbDirectory) = "" Then
MkDir sPath
End If
Next
End Sub

Private Sub Command0_Click()
'Call SaveAttachmentsTest - argument not optional
'Call SaveAttachmentsTest("") - saves ais folder and adds ais to photo name but not putting photos in folders
End Sub
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
Can you show us some of the names of your files?
Some examples of what the attachment is and what you want as the folder and file names to be saved.
 

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
Sure! Thank you for your response.
So the files/attachments are all pictures (jpgs). The AIS field is the primary key field for the Persons table. I had an attachment field in the persons table that stored pictures for that record (associated with that AIS). I ultimately learned of the 2GB limit so now I'm having to export all of the pictures from the table and I would like to be able to have the function create a folder named the AIS and place all of the attachments for that record (pictures) inside that folder. As far as examples of what the attachment is and what I want as the folder and file names, see the image below. I hope that helps. The function that I got from the aforementioned thread supposedly worked for him and as I said, I think the problem might lie with my Call. Thank you again for the response.

Example of how folders should be generated:
1581644467903.png


And then inside each folder would contain all of the attachments (pictures) for that record (AIS field) - For example inside folder 100123 would look like this:
1581644544496.png
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:59
Joined
Oct 29, 2018
Messages
21,358
Hi. If you insert a Debug.Print strFullPath right after you assign a value to it or right before you try to save the attachment, what do you get in the Immediate Window? I was just curious if you're missing a backslash in the file's path.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
I have the same concern with the backslash as theDBguy. When I set up a test to run the code (for the MkDir routine ) I noticed it required a backslash. Also, not specific to your issue, I could not add a directory/folder below C:\Users\ ---I believe this is a system created folder and I received error 75. I could certainly add other folders eg C:\Jack\TestJ\.
The function you are using seems to be the same as this M$oft article regarding attachments.

I'm just interested in your file naming and your table design (to see what AIS really is) in order to mock up a more focused test.
 

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
@theDBguy See the images below for the output to the immediate window. It was the same result in front and after.
@jdraw AIS = Think of it as just an employee ID number. And the rest of the database is comparable to an employees database with info on each employee as well as photos.

1581696449379.png

Output:
1581696465606.png
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
I just made a mockup and with a few adjustments to the code:
-made new folder and subfolder
-saved jpg type attachments to subfolder.

My table with attachments in MyPic field
id myPic
2 1 <-----this contains 1 png
3 3 <-----this contains 3 jpgs

I ran this test with result

?SaveAttachmentsTest("C:\TestJ\","*.jpg")
Made folder: C:\TestJ\
Made folder: C:\TestJ\SomeJpgs\
Saved : lynx2.jpg
Saved : racoon.jpg
Saved : tigerRiver.jpg
 

Attachments

  • attachResults..PNG
    attachResults..PNG
    92.5 KB · Views: 541

theDBguy

I’m here to help
Staff member
Local time
Today, 04:59
Joined
Oct 29, 2018
Messages
21,358
@theDBguy See the images below for the output to the immediate window. It was the same result in front and after.
Hi. I could be wrong, but if you are not getting a "Path" with strFullPath, then wouldn't the SaveToFile method simply put the attachment to the project's root folder?
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
I also ran this test
Code:
Sub testMakePath()
    Dim testpath As String, i As Integer, a(3) As String
    a(0) = "abcd"
    a(1) = "qzaxcd"
    a(2) = "a23yyd"
    a(3) = "abxuur"
    testpath = "C:\Jack\TestJ\FromAttachments"
    For i = 0 To 3
        submakepath testpath & "\" & a(i)
        Next i
    End Sub
Which gave individual subfolders.

Made folder: C:\Jack\
Made folder: C:\Jack\TestJ\
Made folder: C:\Jack\TestJ\FromAttachments\
Made folder: C:\Jack\TestJ\FromAttachments\abcd\
Made folder: C:\Jack\TestJ\FromAttachments\qzaxcd\
Made folder: C:\Jack\TestJ\FromAttachments\a23yyd\
Made folder: C:\Jack\TestJ\FromAttachments\abxuur\
 

Cronk

Registered User.
Local time
Today, 22:59
Joined
Jul 4, 2013
Messages
2,770
First problem is the following line (taken from code in #1)
Code:
thisPath = Replace(strPath & "" & OrdID & "", "\", "")

This strips out all back slashes. So if the passed folder (strPath) were
C:\Jack\TestJ\FromAttachments\

the file would be saved as
C:JackTestJFromAttachments230408IMG_4150.jpg

Second problem is not having a backslash before the file name. So if the Replace line is removed, the file would be saved as
C:\Jack\TestJ\FromAttachments\230408IMG_4150.jpg

So my suggestion is these code lines
thisPath = Replace(strPath & "" & OrdID & "", "\", "")
subMakePath thisPath
strFullPath = thisPath & rsA("FileName")


be replaced by

thisPath =strPath & "\" & OrdID
subMakePath thisPath
strFullPath = thisPath & "\" & rsA("FileName")
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
Cronk,
That is correct. I changed that line in my tests.
 

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
Thank you guy so much for the help! I'm swamped as usual, but I should be able to test your suggestions out tonight. Again, really REALLY appreciate it and looking forward to trying it out! @jdraw @theDBguy @Cronk
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
Good luck with your project.
 

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
Just to clarify.. am I calling the function correctly?
Code:
Private Sub Command0_Click()
Call SaveAttachmentsTest("")
- saves ais folder and adds ais to photo name but not putting photos in folders
End Sub

Because when I put anything in between the quotes like Call SaveAttachmentsTest("Ventress") it labels all the folders AND the images like Ventress12346 (folder) VentressDCIM-1345.jpg (picture)

When I try to just use Call SaveAttachmentsTest - I get an error
When I try to use Call SaveAttachmentsTest() - I get an error
But Call SaveAttachmentsTest("") executes
 

Cronk

Registered User.
Local time
Today, 22:59
Joined
Jul 4, 2013
Messages
2,770
You are not adding the back slashes.
If the parent folder is to be C:\Ventress, then the code should be called as
Call Call SaveAttachmentsTest("C:\Ventress")

In the sub routine, the line
thisPath =strPath [COLOR=rgb(184, 49, 47)][B]& "\" &[/B][/COLOR] OrdID
would have the value of thisPath containing "C:\Ventress\12346" provided the variable OrdID was "12346".

Similarly,
strFullPath = thisPath [COLOR=rgb(184, 49, 47)][B]& "\" &[/B][/COLOR] rsA("FileName")
would give "C:Ventress\12346\DCIM-1345.jpg" for the file being saved.
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
Audrey,
Here is a demo of saving attachments (png/jpg files) to a folder. It parallels your requirement but deals with a table and attachments that I have in a test database. The folder(s) is/are created as part of the SaveAttachmentsTest function. The frmDemo, which opens with the database, describes the processes and conditions. If you place a breakpoint on the Do While statement of SaveAttachmentsTest function and step through the code (F8) you will see how the folder names and path are built. There are Debug.Print statement in the code to present results in the Immediate Window.

To remove the folders and files created, open the file browser, highlight the "Jack" folder, and click Delete. This should remove the files and folders/subfolders. If not, go to the file level and delete from file, then subfolder, then folder.

Note: You can run the demo from the button on FrmDemo.
Good luck.


UPDATE: 5 SEP 2022
Today, I found that the demo database was failing with error 13 (type mismatch) when Dimming variables as DAO.Field2. I changed and used
Code:
Dim fld As Object ' DAO.Field2     'the name of the attachment field
Dim OrdID As Object 'DAO.Field2   'the field from the table to use a last folder/node name
this works. You will have to change these 2 lines as shown.

Note: There have been issues and subsequent fix with Attachment fields. There seems to be some change to the Field2 based on my findings.
 

Attachments

  • AttachDemo.zip
    333.6 KB · Views: 491
Last edited:

audrey

Registered User.
Local time
Today, 06:59
Joined
Sep 9, 2008
Messages
15
@Cronk
You are not adding the back slashes.
If the parent folder is to be C:\Ventress, then the code should be called as
Call Call SaveAttachmentsTest("C:\Ventress")

In the sub routine, the line
thisPath =strPath & "\" & OrdID
would have the value of thisPath containing "C:\Ventress\12346" provided the variable OrdID was "12346".

Similarly,
strFullPath = thisPath & "\" & rsA("FileName")
would give "C:Ventress\12346\DCIM-1345.jpg" for the file being saved.

This fixed it! Working perfectly now! I am so utterly grateful for your help!

@jdraw and thank you so much for the demo!
For anyone in the same situation: The explanations for each section helped tremendously and I would recommend anyone that has run into this issue (of not knowing about the 2GB limit on attachments and in a crunch to export all of them out) this is definitely where to start! A lot of effort put in detailing each line.

Thank you both very very much!
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
You are very welcome. Good luck with your project(s).
 

gstylianou

Registered User.
Local time
Today, 13:59
Joined
Dec 16, 2013
Messages
357
Audrey,
Here is a demo of saving attachments (png/jpg files) to a folder. It parallels your requirement but deals with a table and attachments that I have in a test database. The folder(s) is/are created as part of the SaveAttachmentsTest function. The frmDemo, which opens with the database, describes the processes and conditions. If you place a breakpoint on the Do While statement of SaveAttachmentsTest function and step through the code (F8) you will see how the folder names and path are built. There are Debug.Print statement in the code to present results in the Immediate Window.

To remove the folders and files created, open the file browser, highlight the "Jack" folder, and click Delete. This should remove the files and folders/subfolders. If not, go to the file level and delete from file, then subfolder, then folder.

Note: You can run the demo from the button on FrmDemo.
Good luck.

Morning jdraw,

I test your example database and is working fine for me, but, how can we modify the code in order to store word, excel, PowerPoint and pdf attachments? Is that possible?

Thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Jan 23, 2006
Messages
15,364
It should save whatever files are in your "attachment field". The default file type selection is "*.*".
The demo constrained the file types to be moved to "*.*g" ---which in the demo is for *.jpg and *.png.
If you have a test database with an attachment field, assign some pdf or ppt or other file types to that field and test before doing any changes in your production/operational database. Let us your progress.

If your requirement is for a different directory/folder structure pattern, then that logic will have to be tested and adjusted separately.
Good luck with your project.
 

Users who are viewing this thread

Top Bottom