Auto Bulk Attachment Problem

[joshirohany]

Dear Experts,
I would like to thank in Advance for your help. I am new to Access as we as VBA / Macro.

I have a DB, where if there are three fields "Student Code, PPLINK, Attachments.

i have a code which helps me to auto insert Images of Student in Attachments.
Where Student Code is unique and PPLINK is the Path of the folder where their photo's are saved. after running the code, it insert attachments based on the PPLINK's path.

Code i have shared below.

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 StudentDetails", dbOpenDynaset)
Do Until rstMain.EOF
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
rstMain.MoveNext
Loop
rstMain.Close
Set rstMain = Nothing
Set cdb = Nothing
End Sub



My area of pain are mentioned below.

a. For Example :-
Student Code PPLINK Attachments
1001 D:\Photos\1001.jpg
1002 D:\Photos\1002.jpg
1003 D:\Photos\1003.jpg

Lets say if 1002's photo is not present in the folder then i am getting error in Yellow "rstAttach.Update". as well as its not going to next code to attaching the attachments.

b. Also i cant give a command button since its say's its not public.

Requesting Experts to help me on the same.

Thanking once again

 

[theDBguy]

Hi. Welcome to the forum. You can use the Dir() function to check if the file exists before attaching it. I’m not sure about your second question but if you want to create a public sub, try putting it in a standard module.

 

[joshirohany]

Hello,
thanks for the reply. but the problem is, lets say there is not attachment, then it should pass on next line for lookup and not stop.

 

[June7]

Please post lengthy code between CODE tags to retain indentation and readability.

Check that the file exists in folder before starting recordset edits.

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

Should certainly be able to put code in button Click event.

 

[joshirohany]

Hello June7,
Yes i want that only. if the Image attachment is not present in the folder then it should go to next line for checking if present then attach or move next.



please help

 

[June7]

Maybe you read my previous post before I edited it. I provided code. Have you tried it?

Have you considered that embedding files in db can quickly use up Access 2GB size limit?

 

[joshirohany]

i used the below code as per you

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


then its showing me error "Compile Error : End If without Block if".

please guide

Yes 2gb limit i have considered.

 

[June7]

Don't have the If Dir() line.

Look at my example again.

 

[joshirohany]

Sorry i did its showing error no 3820. with option of End and Debug. if Debug is selected then its highlighting "rstAttach.Update"

 

[June7]

Here is example procedures.
https://sourcedaddy.com/ms-access/working-with-attachment-fields.html

Your code appears to be correct (assuming you added the Dir() line).

 

[joshirohany]

Hello June7,
Your inputs worked. I thank you personally. I need your help in the same area.


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

 

[moke123]

Are you using an attachment datatype? Note that using an attachment field will bloat the size of your database. Most developers will only store a path to a file as text.

 

[joshirohany]

Yes, attachment are stored in Database. I have already considered the 2gb Limit. i am fine with the same


requesting you to help on the code.

 

[moke123]

Yes, attachment are stored in Database. I have already considered the 2gb Limit. i am fine with the same

Suit yourself but I think you'll find that the size will grow rapidly.

Presumably this is an effort to show the employees photo on a form. There are several much simpler methods.

One method is to store all the photos in one folder and name them with a unique name, employeeID for instance (ie. 1002.jpg) You need only to store the photo name in your table. You can also have a dummy photo in the folder named NoPhoto.jpg. You can then just set the picture property of an image control to the Path to the folder and concatenate the photo name to the end. If there is no photo then the NZ() will insert the NoPhoto.jpg.

something along the lines of ...

Me.YourImageControlName.Picture =  "D:\Photos\" & nz(Me.photoName,'NoPhoto.jpg')

 

[June7]

Please use CODE tags in post so code retains indentation and readability.

I ran your code with no error and updated entire table. You need to step debug. What is the exact message for error 3820?

VBA setting Picture property on a form will only be practical for form in Single View.

I use ControlSource property with an expression and no VBA needed: = "D:\Photos\" & Nz([photoName], "NoPhoto.jpg")

 

[moke123]

error 3820 has to do with a duplicate value in an attachment field. Presumably you may need to test if the value is already in the table. That may be why it errors on subsequent runs.

 

[June7]

If that is the case, then maybe limit recordset query to only those records that don't already have attachment.

 

[joshirohany]

Dear All,
I have attached the db as well as photo. requesting you to download and try the same.



a. Scenario ;What if someone joins new and i add his/her photo in Photo Folder and run the code again ...it still not adding. this happens when i run the code 2nd time. for first time all Photo's from the Folder and added successfully but while running the code second time or third there are no updates.

for instant run the code after dowloading the file. then change 200.jpg to 2002.jpg it wont add


please guide.

 

[June7]

Change the recordset SQL to only pull records that don't have attachment:

Set rstMain = cdb.OpenRecordset("SELECT PPLink, Attachment FROM EmployeeDetails WHERE Attachment.FileName Is Null", dbOpenDynaset)

The new record must be committed before running the code. Cannot still be in edit mode on table or form.

 

[moke123]

Your getting an error because the file is already in the attachment field and you cannot have duplicates in this type of field.

You need to either test whether the file exists in the attachment or handle the error in your error handler.

I added a simple On Error resume next and it appears to work.

Since your already storing the Path to the photo it seems duplicative to also attach it.

....
    On Error Resume Next

    Set rstMain = cdb.OpenRecordset("SELECT PPLink, Attachment FROM EmployeeDetails ", dbOpenDynaset)

    Do Until rstMain.EOF
.....