Auto Bulk Attachment Problem

[joshirohany]

Dear Friends,
Firstly i will thanks for your suggestion and help in advance.


I am New to Access and not aware about vba. I need your advice. I have mentioned below the code where in a table there are fields mentioned below.


a. PPLink ( Path of the folder and file name are mentioned)
b.Attachment (Where as attachments it gets inserted automatically when i run the code.


the problem is When i run the Code first time, it runs and attache's the attachment. When running it second time, it does not consider the new updates and doesn't attach the attachment and as well as shows Run time error "3820". below is the code used. at times it even skips the attachment which are present in the folder.

Public Sub test()
Dim cdb As DAO.Database, rstMain As DAO.Recordset, rstAttach As DAO.Recordset2, _
fldAttach As DAO.Field2
Set cdb = CurrentDb
Set rstMain = cdb.OpenRecordset("SELECT PPLink, Attachment FROM EmployeeDetails", dbOpenDynaset)
Do Until rstMain.EOF
If Dir(rstMain!PPLink) <> "" Then
rstMain.Edit
Set rstAttach = rstMain("Attachment").Value
rstAttach.AddNew
Set fldAttach = rstAttach.Fields("FileData")
fldAttach.LoadFromFile rstMain("PPLink").Value
rstAttach.Update
rstAttach.Close
Set rstAttach = Nothing
rstMain.Update
End If
rstMain.MoveNext
Loop
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing
End Sub



please help.

 

[theDBguy]

Hi. What does the error message for error 3820 say? What do you mean by "does not consider the new updates?" What is updated? The file? Is it a Word doc or Excel sheet? Attachment fields can only store one copy of a file at a time. So, if you're trying to overwrite an existing attachment, you might have to delete it first before attaching the new one.

 

[theDBguy]

Looks like this may be a continuation of this previous discussion.