Anything can make this code run faster?

[Isaac]

I wrote some code to download all attachments from a sharepoint list. The database where this is running is on my local. The destination of the files is a folder on my desktop.

It takes about an hour on 1400 records each with one attachment. Can you spot anything I could do to make it run faster?

Private Sub lblFiles_Click()
Dim fd As FileDialog, strFolderPath As String, lngTotal As Long, rs As DAO.Recordset, rsMini As DAO.Recordset, strFilePath As String, db As DAO.Database, lngIncrement As Long
MsgBox "On the next screen, you will be presented with a BROWSE window.  Please browse to, and select, the FOLDER where you want the attachment files downloaded to.  " _
        & "Please ensure that the folder is somewhere on your computer (not the network).  Example, your desktop or somewhere on your C drive", vbInformation, "  "
Set fd = FileDialog(msoFileDialogFolderPicker)
With fd
    .Show
    strFolderPath = .SelectedItems(1)
End With

If InStr(1, LCase(strFolderPath), "c") = 0 Then
    MsgBox "Please try again.  Select a folder that is on your computer (such as your Desktop or somewhere local - not a network/shared folder)", vbCritical, "  "
End If

Dim lngCurrent As Long
Set rs = db.OpenRecordset("select id, attachments from 20210112")
Do Until rs.EOF = True
    lngIncrement = 0
    lngCurrent = lngCurrent + 1
    
    Set rsMini = rs.Fields("Attachments").Value
    Do Until rsMini.EOF = True
        lngIncrement = lngIncrement + 1
        rsMini.Fields("filedata").SaveToFile strFolderPath & "\" & rs.Fields("id").Value & "_" & lngIncrement & Right(rsMini.Fields("filename"), Len(rsMini.Fields("filename")) - InStrRev(rsMini.Fields("filename"), "/"))
        rsMini.MoveNext
    Loop
    rsMini.Close
    Set rsMini = Nothing
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub

 

[Minty]

it's taking about 2 seconds per file, I doubt you can make that much more efficient, allowing for the filesystem to move the file around.

How long does it take if you manually copied and pasted them?

 

[Isaac]

I would be able to manually copy & paste if they were in a doc library, but alas, these are attachments on a Custom List

 

[The_Doc_Man]

Given that you are doing network ops (because it is a Sharepoint list), you are doomed to a lot of overhead because of remote file access arbitration and, because you are creating files, a lot of directory manipulation. Neither of those is a fast action.

 

[Isaac]

Thanks gentlemen. Looking at it so much right now I am thinking this:

rsMini.Fields("filedata").SaveToFile strFolderPath & "\" & rs.Fields("id").Value & "_" & lngIncrement & Right(rsMini.Fields("filename"), Len(rsMini.Fields("filename")) - InStrRev(rsMini.Fields("filename"), "/"))

could be changed to where i only call rsMini.fields() once. maybe a tiny improvement. :|