Auto Bulk Attachment Problem (1 Viewer)

joshirohany

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

I’m here to help
Staff member
Local time
Today, 04:10
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
Please post lengthy code between CODE tags to retain indentation and readability.

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

Code:
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.
 
Last edited:

joshirohany

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
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

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
Don't have the If Dir() line.

Look at my example again.
 

joshirohany

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
Sorry i did its showing error no 3820. with option of End and Debug. if Debug is selected then its highlighting "rstAttach.Update"
 

joshirohany

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
3,852
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

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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

AWF VIP
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
3,852
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 ...

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

June7

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
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

AWF VIP
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
3,852
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

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
If that is the case, then maybe limit recordset query to only those records that don't already have attachment.
 

joshirohany

Registered User.
Local time
Today, 04:10
Joined
Apr 3, 2019
Messages
33
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.
 

Attachments

  • Employee DB - Copy - Copy.accdb
    548 KB · Views: 140
  • photo.zip
    176.8 KB · Views: 132

June7

AWF VIP
Local time
Today, 03:10
Joined
Mar 9, 2014
Messages
5,425
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.
 
Last edited:

moke123

AWF VIP
Local time
Today, 07:10
Joined
Jan 11, 2013
Messages
3,852
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.

Code:
....
    On Error Resume Next

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

    Do Until rstMain.EOF
.....
 

Users who are viewing this thread

Top Bottom