• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Add an attachment to db table vba based on ID

Status
Not open for further replies.

Sanar1567

New Member
Hello,
I have a db linked with share point, now i want to attach pdf’s to specific fields based on ID. The title of the pdf saved on the path based on “ID”. Could you please help me how to write the code for it. Becuase i am having 500 records and attaching 500 pdfs manually would be cumbersome
 

Chihiro

Excel Ninja
You need to detail more info.
What sort of db? Is that on Excel, Access or some other software?
Is SharePoint online? Or local deployment?

Assuming SharePoint Online. You'd need following type of set up.
1. pdf name must contain some identifier to associate it with specific record. Then dump all pdf into SharePoint folder or OneDrive. Use Power Automate to attach pdf to record/item and remove from folder.

2. Enable SharePoint API access in Azure AD, and leverage VBA to upload. This will require your MS365 tenant admin to enable API and register.
 

Sanar1567

New Member
Sorry ! . I am having a sharepoint online. A share point list was created over there. I exported the share point list to ms access. Now iam able to change the data in sharepoint through this access table. In the table there are two columns . One is “ID” and the other is attachment field. Now i am manually attaching a pdf document to access table. With that i can see the pdf in sharepoint online. Like wise i need to repeat the same task 500 times. Because there are 500 records in the table. Could you please suggest some macro or vba to automate it.
 

Sanar1567

New Member
Thanks! The id is being created from sharepont itself. My db access table is as below
74543
The attachments on the drive like
74544
Now each pdf should be attached to the respective row item.
Based on your inputs, i have written a VBA code, but it seems to be not working

Code:
Sub Attachments()

Dim rsfile As DAO.Recordset
Dim rsreport As DAO.Recordset
Dim db As DAO.Database
Dim filepath As String
Set db = CurrentDb
Set rsfile = db.OpenRecordset("table1")
Do While Not rsfile.EOF
If rsfile.Fields("ID").Value = 1 Then
rsfile.Edit
Set rsreport = rsfile.Fields("Field1").Value
filepath = "C:\Users\india\Desktop\New folder (2)\New folder\1.pdf"
rsreport.AddNew
rsreport.Fields("Filedata").LoadFromFile (filepath)
rsreport.Update
End If
rsfile.MoveNext
Loop

End Sub
 

Chihiro

Excel Ninja
Something like below would do it.
Code:
Option Compare Database

Sub Demo()
Dim db As DAO.Database, rs As DAO.Recordset, rsAtt As DAO.Recordset2
Dim fPath As String: fPath = "C:\Users\india\Desktop\New folder (2)\New folder\"
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1")

Do While Not rs.EOF
    If Dir(fPath & rs.Fields("ID").Value & ".pdf") <> "" Then
        rs.Edit
        Set rsAtt = rs.Fields("Field1").Value
        With rsAtt
            .AddNew
            .Fields("FileData").LoadFromFile (fPath & rs.Fields("ID").Value & ".pdf")
            .Update
        End With
        rs.Update
    End If
    rs.MoveNext
Loop
End Sub
74591
 

A380b747

New Member
Hi @Chihiro @Sanar1567;

Hope you are doing well. I´m reaching out to you because I read the answer in which you helped another sanar1567 to attach an external file to a database (field) on Access. And I´m now struggling trying to do the same or something similar to the original issue the sanar had at that time. Since i´m a beginner with Access, I don´t know how to proceed with this.

So basically, I have a form and there is a button inside that form. So everytime, the user clicks on it, it should be able to upload an external file and store it on a database. The form is opened with some unique data of each register on a table. The value of that unique register is shown in a textbox called "Evento_ID" in the form

The table name is "GC_Eventos"
Table field is "Contrato"

So I was trying to use this code but I don´t even know how to get the path of a file from another person. Since the users will be using the program while working on their computers, they can stored the documents anywhere ("C:\Users\...)

>>> use code - tags <<<
Code:
Private Sub Command870_Click()

Option Compare Database

Sub Demo()
Dim db As DAO.Database, rs As DAO.Recordset, rsAtt As DAO.Recordset2
Dim fPath As String: fPath = "C:\Users
Set db = CurrentDb
Set rs = db.OpenRecordset("GC_Eventos")

Do While Not rs.EOF
If Dir(fPath & rs.Fields("Me.Evento_ID").Value & ".pdf") <> "" Then
rs.Edit
Set rsAtt = rs.Fields("Contrato").Value
With rsAtt
.AddNew
.Fields("FileData").LoadFromFile (fPath & rs.Fields(Me.Evento_ID).Value & ".pdf")
.Update
End With
rs.Update
End If
rs.MoveNext
Loop
End Sub

End Sub
Do you know how I can get this code?? Hope you can help me (I don't know why the indentation is not shown)

Regards

Maybe an image works

79599
 
Last edited by a moderator:

vletm

Excel Ninja
A380b747
Moderator note:
You should open a new thread as written in Forum Rules.
  • Start a new post every time you ask a question, even if the theme is similar. The original author may continue asking questions whilst the post is in progress and of course if you are answering questions you may need to ask questions of the initial poster.
This thread is closed now.
 
Status
Not open for further replies.
Top