Search Directory Structure in Access VBA

[joseph micheli]

I would like to search a directory structure for all .mdb files and write the
file path of each one to a table. I want to start at the highest level of
the drive and search all subdirectories without specifying the subdirectory
names. Does anyone have any suggestions?

Thanks.

 

[Albert D. Kallal]

This code will work...

Sub dirTest()

Dim dlist As New Collection
Dim startDir As String
Dim i As Integer
Dim strType As String


startDir = "C:\Documents and Settings\Albert\My Documents\"
strType = "*.mdb"

Call FillDir(startDir, dlist, strType)

MsgBox "there are " & dlist.Count & " in the dir"

' lets printout the stuff into debug window for a test

For i = 0 To dlist.Count
Debug.Print dlist(i)
Next i


End Sub


Sub FillDir(startDir As String, dlist As Collection, sType As String)

' build up a list of files, and then
' add add to this list, any additinal
' folders

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

strTemp = Dir(startDir & sType)

Do While strTemp <> ""
dlist.Add startDir & strTemp
strTemp = Dir
Loop

' now build a list of additional folders
strTemp = Dir(startDir, vbDirectory)

Do While strTemp <> ""
If GetAttr(startDir & strTemp) = vbDirectory Then
If (strTemp <> "..") And (strTemp <> ".") Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

' now process each folder (recursion)
For Each vFolderName In colFolders
Call FillDir(startDir & vFolderName & "\", dlist, sType)
Next vFolderName

End Sub


Of couse, you need to change:

For i = 1 To dlist.Count
Debug.Print dlist(i)
Next i
to;
dim rstData as dao.recordset
set rstData = currentdb.OpenRecordSet("tblFiles")
for i = 1 to dlist.count
rstData.Addnew
rstData!MyFile = dlist(i)
rstData.Update
next i
rstData.Close

The above example uses recursion...and will "walk" all directories below the
one you start.....