add files to attachment field from a folder without dubulcates

[aspen]

Hi every one

I have table with an attachment field and on my form i have put a code on the click event of the id field. here is the code

Const strRootFolder As String = "C:\Users\eve\Desktop\New\"
AddAttachmentsFromFolder strRootFolder, "Table1", "Files", _
"*.*", True
MsgBox "Done adding files from: " & vbCrLf & strRootFolder, _
vbInformation, "File Import Process Completed"

This code ads all the files in the foldder. what i want acomplish is to change the code so that it would not add a file if it already exists in the the table. I am very at vba so can some one please help me.

Thank you

[JeroenMioch]

Something along these lines :

if FileExists("C:\Users\eve\Desktop\New\" & me.AttachmentField & ") = true then
AddAttachmentsFromFolder strRootFolder, "Table1", "Files", _
" & me.AttachmentField & ", False
else
'nothing else
end if
exit sub

Its aircode but ill try to test it for you

[aspen]

I am using this module along with that code . please try

' -------------------------------------------------------------------------
' Procedure : AddAttachmentsFromFolder
' Purpose : Adds one record to an attachment field for each file in the
' : specified folder.
' Arguments : strFolder - Name of the folder
' : strTable - Name of the table containing the attachment field
' : strField - Name of the attachment field
' : strPattern - Search pattern for the directory. Defaults to
' : all files (*.*)
' : fIncludeSubfolders - Include subfolders in the specified folder
' : Defaults to False
' Comments : This routine adds a new record to the table, then one attachment.
' : It commits the changes and handles all cleanup
' -------------------------------------------------------------------------
Sub AddAttachmentsFromFolder(ByVal strFolder As String, _
ByVal strTable As String, _
ByVal strField As String, _
Optional ByVal strPattern As String = "*.*", _
Optional ByVal fIncludeSubfolders As Boolean = False)
Dim strFile As String
Dim lngCount As Long
Dim rstParent As DAO.Recordset2
Dim rstChild As DAO.Recordset
Dim fldAttach As DAO.Field2
Dim objFso As Object ' Scripting.FileSystemObject
Dim objFolder As Object ' Scripting.Folder
Dim objFile As Object ' Scripting.File
Dim objSubFolder As Object ' Scripting.Folder

On Error GoTo ErrorHandler

' Instantiate the FileSystemObject
Set objFso = CreateObject("Scripting.FileSystemObject")
' fix up the folder

If (Right(strFolder, 1) <> "\") Then strFolder = strFolder & "\"
' make sure the folder exists
If (Dir(strFolder, vbDirectory) = "") Then
   MsgBox "The specified folder does not exist: " & strFolder, vbExclamation
   Exit Sub
End If
' get the folder object
Set objFolder = objFso.GetFolder(strFolder)
' open the table containing the attachment field
Set rstParent = CurrentDb().OpenRecordset(strTable)
' get the first file
strFile = Dir(strFolder & strPattern)
' get each file that meets the pattern
While (Len(strFile) > 0)
   ' add a record to the parent table
   Debug.Print strFolder & strFile
   rstParent.AddNew
   ' get the attachment recordset and FileData field to contain the file
   Set rstChild = rstParent.Fields(strField).Value
   Set fldAttach = rstChild.Fields("FileData")
   ' add the attachment to the attachment field
   rstChild.AddNew
   fldAttach.LoadFromFile strFolder & strFile
   rstChild.Update
   rstParent.Update
   ' get the next file
   strFile = Dir
Wend
' recurse subfolders?
If (fIncludeSubfolders) Then
   For Each objSubFolder In objFolder.SubFolders
      AddAttachmentsFromFolder objSubFolder.Path, strTable, strField, _
      strPattern, fIncludeSubfolders
   Next
End If
Cleanup:
   rstParent.close
   Set rstParent = Nothing
Exit Sub
ErrorHandler:
   Debug.Print "Error " & Err.Number & " - " & Err.Description
   MsgBox Err.Description & vbCrLf & _
   Err.Number & vbCrLf & _
   Err.Source, VbMsgBoxStyle.vbCritical, "AddAttachmentsFromFolder Failed"
   GoTo Cleanup

Thank you

[June7]

Use code tags as shown in my edit of your post. Use indentation in your code and it will be preserved when you paste between code tags.

Checking to see if file already in the attachment field I think will involve opening a recordset of the attachment subtable. This http://msdn.microsoft.com/en-us/library/ff835669.aspx shows relevent code. Look at the procedure to save attachment to disc. Instead of doing the save within the loop, do a comparison of file names. If the file name is found, don't attach file, if not found then attach.

[JeroenMioch]

Right, ive done some testing and ive got it partially figured out.

I added a .jpg file to a directory and let access check if it existed. When i checked it, it DID exist.
When i removed the file and checked the existence again, it did NOT exist.
This makes me believe that ive allmost got it figured out.

Why allmost ?
Because i didnt add files with your code. I looked for a specific file and i knew it was there and what its name was.
Now, ill have to code it so it does that automaticly, so bare with me.

To give you a heads up, here is my source (who else then Allen Browne, the Genius )
http://allenbrowne.com/func-11.html

I implemented his fileExist in the following code :

Private Sub knpAddFiles_Click()
If FileExists("I:\Jeroen\MS-access\Afbeeldingen\Facillity.jpg") Then
MsgBox "it exists"
Else
MsgBox "it does NOT exist"
End If
End Sub

[June7]

JeroenMioch,

aspen's requirement is to check if the file is already in the attachment field, not if it is in the directory.

[JeroenMioch]

Thats exactly what it does June7.

It checks the attachement field, then looks in the directory and "tells" me that the file allready is there
The only problem with further testing is that Aspens module to attache files doesnt work for me. So ill have to figure that out first.

[June7]

"6 of one, half a dozen of the other"

How will you plan to dynamically pull the file names from the attachment field to check against files in directory? Still think need recordset of the attachment subtable.

[JeroenMioch]

That is indeed the tricky part.
But the first step has been achieved and that is to see if there are conflicting file names.

My shift here at work is allmost at an end so ill try to think that over tomorrow.
In the mean time if you have better suggestions then by all means post it. the purpose here is to help fellow programmers. So if its you or me finding the solution is irrelevant.

[June7]

I am actually hoping aspen () will use the example in the link I provided along with my instructions and attempt some code for us to analyse.

[JeroenMioch]

Right, somehow i missed your post haha.
I hope it works for him, else we will look further

[aspen]

Hi guys
Thanks for your concern. it shows the forum is wild alive. i'll check the link first. i thought it would take some time for a response.

Thank you

[aspen]

Hi every body
I tried the code and the link. but my vba is enough. I know that code exists. But i cant pay the prize now. I got to stop some losses. This is for my kids. But i have to come to this latter.
I am sory friend. I will come back another time. to this thread or a new thread . but i promise the same topic. I must fly now

Bye

[June7]

aspen, I looked again at the tutorial blog referenced in your other threads http://blogs.office.com/b/microsoft-...cess-2007.aspx

The first example procedure uses error handler to deal with the possibility of duplicate attachment. However, this is only for a single document. In your case of attaching multiple documents from a folder, would not want to exit the sub but instead continue with the next document in folder. Most error handlers are designed to offer a graceful exit from a procedure when error encountered. Review for error handler example http://allenbrowne.com/ser-23a.html

However, code execution can be returned to the primary code segment from the error. Review
http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
http://www.ozgrid.com/forum/showthread.php?t=114989