Get a list of files on network drive

[RayV]

I have a slight problem getting a list of files that exist on a
network drive. I have two ways of doing it that are both slow (~10
minutes). I am only interested in one folder and not subfolders and
there are only about 5,000 files in this folder.

At a command prompt with a dir command the list of flies is displayed
in less than 10 seconds. when I add the switch to write to a text
file it takes about 10 minutes using.
dir /b >filelisting.txt
I also tried putting this in a bat file on the network drive but it
still runs locally using cmd.exe

So I tried in VB using this code which takes a little longer than 10
minutes but then the files are already in the table I need.
Function FindPDFs()
Dim strPath As String
Dim fs, f, s, respone
strPath = "N:\Contracts"
setting = Application.GetOption("Confirm Action Queries")
Application.SetOption "confirm action queries", False
With Application.FileSearch
.LookIn = strPath
.SearchSubFolders = False
.FileName = "*.pdf"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
sqlStr = "INSERT INTO folder ( [fname] ) SELECT '" &
Mid(.FoundFiles(i), 44, 11) & "' AS Expr1;"
DoCmd.RunSQL sqlStr
Next i
End If ' databases found
End With ' filesearch
Application.SetOption "confirm action queries", setting
End Function

Any ideas on how to make this quicker?

As a side note I do a similar import from an FTP site which all runs
in less than a minute.

 

[Pat Hartman]

For one thing, you are forcing Access to re-evaluate that query each time
you run it. Switch to using DAO or ADO. Open a recordset outside the loop
and then inside the loop do an .AddNew/.Update for each found file.