File search Dir() problem with large directories

[kerrid]

Please somebody help me with my code. I am read almost every post regarding
the changes to filesearch in 2007 and transfered my program over the to
utilise the Dir() function . However when search large directories (files
over around 100,000) the code falls over - losing network connection and
stating invalid function. Could somebody advise if my code is at fault?

Function findfilespath(pathname As String, tmpfilenametype As String,
tmpdelete As String, tmpsource As String)

Dim dbsanother As Object, rstfiles As Recordset
Set dbsanother = CurrentDb
Set rstfiles = dbsanother.OpenRecordset("foundfiles", dbOpenDynaset)

DoCmd.SetWarnings False

If tmpdelete = "Y" Then
DoCmd.RunSQL "delete [foundfiles].* from [foundfiles] where [foundfiles].
[tablename] = '" & tmpsource & "';"
End If
strfile = Dir(pathname & "\*." & tmpfilenametype)
Do While Len(strfile) > 0
rstfiles.AddNew
rstfiles.Fields("filename") = strfile
rstfiles.Fields("tablename") = tmpsource
rstfiles.Update

strfile = Dir()
Loop

Set dbsanother = Nothing
End Function

Thanks in advance, Kerri

 

[Stefan Hoffmann]

hi Kerri,

Please somebody help me with my code. I am read almost every post regarding
the changes to filesearch in 2007 and transfered my program over the to
utilise the Dir() function . However when search large directories (files
over around 100,000) the code falls over - losing network connection and
stating invalid function. Could somebody advise if my code is at fault?

I would use the FileSystemObject instead of Dir():

http://msdn.microsoft.com/en-us/library/6kxy1a51(VS.85).aspx

When using early binding you need to set a reference to the Microsoft
Scripting Runtime:

Dim fso As Scripting.FileSystemObject ' Object
Dim fld As Scripting.Folder ' Object
Dim fl As Scripting.File ' Object

Dim count As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("c:\temp")

For Each fl In fld.Files
Debug.Print fl.Name
Next fl

Set fl = Nothing
Set fld = Nothing
Set fso = Nothing


mfG
--> stefan <--

 

[kerrid via AccessMonster.com]

Many thanks! - after some tweaking fitted perfectly into my code.
thanks again
Kerri