Subfolder search

[lcannon]

Using this code - is there a way to not have to type the path everytime and be able to search all folders and sub folders for the mdb databases that have the "employees" table? I really need a good example - I am new with VBA and this works fine for me except having to type the path everytime.

Thanks.

Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim strPath As String

CurrentDb.Execute "DELETE * FROM tblTables"
strPath = "x:\dbases_AC\dbases\"
strFile = Dir(strPath & "*.mdb")

While strFile <> ""
CurrentDb.Execute "INSERT INTO tblTables (TableName, DatabaseName) SELECT Name,'" & strPath & strFile & "' FROM MSysObjects IN '" & strPath & strFile & "' WHERE Name = 'employees'"

strFile = Dir()
Wend
Me!Combo0.Requery

End Sub

[mikeyc1204]

This should do it for you:

Function getDirList(startDir As String)
 
    Dim foundDirs()
    Dim subDir As String
    Dim dirCount As Integer
 
    subDir = Dir(startDir, vbDirectory + vbHidden)
    Do While subDir <> ""
        If subDir <> "." And subDir <> ".." Then
            If (GetAttr(startDir & subDir) And vbDirectory) = vbDirectory Then
                dirCount = dirCount + 1
                ReDim Preserve foundDirs(dirCount)
                foundDirs(dirCount) = startDir & subDir & "\"
            End If
        End If
        subDir = Dir
    Loop
 
    If dirCount = 0 Then
        getDirList = False
    Else
        getDirList = foundDirs
    End If
        
End Function
 
 
Private Sub Form_Open(Cancel As Integer)
 
    Call Old_Form_Open("x:\dbases_AC\dbases\")
 
End Sub
 
Sub Old_Form_Open(strPath As String)
 
    Dim strFile As String
    Dim varDirs As Variant
    Dim i As Integer
 
    CurrentDb.Execute "DELETE * FROM tblTables"
    strFile = Dir(strPath & "*.mdb")
    varDirs = getDirList(strPath)
 
    While strFile <> ""
        CurrentDb.Execute "INSERT INTO tblTables (TableName, DatabaseName) SELECT Name,'" & strPath & strFile & "' FROM MSysObjects IN '" & strPath & strFile & "' WHERE Name = 'employees'"
 
        strFile = Dir()
    Wend
    If IsArray(varDirs) Then
        For i = 1 To UBound(varDirs)
            Call Old_Form_Open(varDirs(i))
        Next i
    End If
    Me!Combo0.Requery
    
End Sub

[mikeyc1204]

I just reread your post, and this will loop through all mdb files in all subdirectories. It makes no distinction whether or not there is an employees table.

[lcannon]

Soo Close. I have the code to look for the "Employees" table in the second half - but do I need it in your new code you added and if so can I add it????

[mikeyc1204]

Didn't see that in your code... I just copied yours. Let er rip, I think it will work.

[lcannon]

I am getting a "ByRef argument type mismatch" error on this line

Call Old_Form_Open(varDirs(i))

I am using Access2000 - if that helps.

Thanks for staying with me.

[mikeyc1204]

change to Call Old_Form_Open(CStr(varDirs(i)))