Results 1 to 7 of 7

Using DAO to save attachments from SharePoint List

  1. #1
    Wendie1970 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Location
    Fort Worth, TX
    Posts
    3

    Using DAO to save attachments from SharePoint List

    Hi all!



    I wanted to share this DAO code which works great for saving attachments that are stored within a SharePoint list, which is linked to the Access db. When I was hunting for something like this, everything I came acrosss was complicated, and usually did not work. So, after some head scratching & banging, I put this together, and IT WORKED a charm. So I thought I would share, and save someone else the frustration of finding a nice simple piece of code to do the same.

    BTW: If any of you geeks can suggest ways to clean it up or make it faster I am all ears. Love learning this stuff.

    Code:
    Sub SaveAttachments()
    
    ' Saves attachments from linked SHAREPOINT list
    ' Using a simple SELECT query
        Dim db As DAO.Database
        Dim rsParent As DAO.Recordset2
        Dim rsChild As DAO.Recordset2
        Dim qdfSp As DAO.QueryDef
        Dim rsSpList As DAO.Recordset2
        Dim rsSpListChild As DAO.Recordset2
        Dim rsSpListURL As String
        
        Set db = CurrentDb
            Set rsParent = db.OpenRecordset("tbl-dyn_Attachments") ' <<< This is the dynamic table that attachments will be saved too
                Set qdfSp = db.QueryDefs("qry_Attachments") ' <<< Simple SELECT query that pulls in the data from the linked table
                    Set rsSpList = qdfSp.OpenRecordset()
                        Set rsSpListChild = rsSpList.Fields("Attachments").Value
                                rsSpListURL = rsSpListChild.Fields("FileURL")
    
    On Error GoTo errSaveNew
    '   Move to first record in the SharePoint List Recordset
        rsSpList.MoveFirst
            
            '   Loop through the list until "End of File" is reached
            
            Do Until rsSpList.EOF
                
                rsParent.AddNew
                        
                rsParent!AttachmentID = rsSpList!ID
                        
                '   Save the attachment to the storage table
                    '   Within SharePoint, Attachments are stored on the SQL server disk, usually in the same root
                    ' as the website.  So to get the file, you have to follow the URL to the file location.
            
                            Set rsChild = rsParent.Fields("AttachFile").Value
                                rsChild.AddNew
                                    rsChild.Fields("FileData").LoadFromFile (rsSpListURL)
                            rsChild.Update
                    rsParent.Update
    SaveNew:
                
              rsSpList.MoveNext
              Loop
    
    Housekeeping:
                    
            Set rsChild = Nothing
            Set rsParent = Nothing
            Set rsSpList = Nothing
            Set qdfSp = Nothing
            Set db = Nothing
        Exit Sub
    
    errSaveNew:
        
    '   if the error number = 3022 (record is already in the index)
    '   then GOTO next record
        If Err.Number = 3022 Then
                Resume SaveNew
        
        '   for any other error type, throw a error message box
        
            Else
                MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
                    Resume Housekeeping
        End If
                
    End Sub

  2. #2
    Si-Bong is offline Novice
    Windows 7 64bit Access 2013
    Join Date
    Jan 2016
    Posts
    1
    Hi Wendie. I am having problems extracting the file attachments from Sharepoint lists linked to my database and needed something like the above. I am a novice at this but tried the copying the above code and it fails at the 'rsSpListURL = rsSpListChild.Fields("FileURL")' point. I'm not sure what to put into this line. I recive a rum-time error of Run Time Error 3265. Any ideas what I'm suppossed to put in between the brackets? Thank you.

  3. #3
    Wendie1970 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Location
    Fort Worth, TX
    Posts
    3
    Hi Si-Bong,

    Code:
    rsSpListURL = rsSpListChild.Fields("FileURL")   ' <<< =This is built into all SharePoint Attachment fields.
    There is nothing in the brackets that you need to add. "FileURL" is actually the URL that is assigned to the Attachment in SharePoint.
    Try commenting out the:
    Code:
    On Error GoTo errSaveNew
    then step through the code using F8. I suspect that the script is actually failing on the table that you are trying to save the attachment to. Make sure that the table contains the following fields:
    "AttachFile" Attachment data type
    "AttachmentID" Long Integer - this is the SharePoint List ID number

    Then try the code again. Also make sure that the query looks something like the following ("SharePointList" is a placeholder)

    Code:
    SELECT [SharePointList].ID, [SharePointList].Attachments
    FROM [SharePointList]
    WHERE ((([SharePointList].ID)=[ToSaveID]) AND (([ToSaveID]) Is Not Null));
    I hope that helps,
    Wendie

  4. #4
    Wendie1970 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2014
    Location
    Fort Worth, TX
    Posts
    3
    Si-Bong,

    Here is an example if that will help too.

    SaveSharePointAttachments.zip

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    41,906
    Si-Bong, you should have started your own thread with a reference to this one. You're lucky Wendie came back and responded. Thread 'hijacking' violates forum etiquette as does resurrecting old threads. A question receives more reviews in new, independent thread.

    I could split these new posts to another thread but will leave here because of the example file Wendie provided to accompany the originally posted code.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  6. #6
    therealsuave is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    2
    Hi Wendie1970

    How can I loop and get multiple files per ID

    Thanks!

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    41,906
    Did you not read post 5?
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 01-28-2014, 11:20 AM
  2. Refresh SharePoint List - SharePoint 2010
    By jgelpi16 in forum Programming
    Replies: 0
    Last Post: 08-22-2013, 05:21 AM
  3. VBA to save attachments to shared drive via Forms
    By shannonsims in forum Programming
    Replies: 6
    Last Post: 04-04-2013, 02:00 PM
  4. Save Attachments.
    By drunkinmunki in forum Programming
    Replies: 6
    Last Post: 11-20-2012, 09:11 AM
  5. import/export attachments to SharePoint list
    By Cojack in forum Import/Export Data
    Replies: 0
    Last Post: 07-15-2011, 12:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums