Subfolder Search Loop

[lcannon]

I am very new with VB and am using Access2000 to try to find a Table in ALL mdb's located under Z:\ and look in all mdbs in all folders and subfolders. I need not to see information but to have someone actually help me with this code. There has to be a simple way to do this and keep this code to use a combo box and input into table. This code works well but I have several hundred databases I need to look for and too much to type in for every search. Thanks. :rolleyes:

Can someone help please.

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

CurrentDb.Execute "DELETE * FROM tblTables"
strPath = "c:\"
strFile = Dir(Z:\ & "*.mdb")

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

strFile = Dir()
Wend
Me!Combo0.Requery

End Sub

[Comatose]

One solution that I'm fond of, in order to make VB search an entire drive is to shell a dos command of dir and redirect it into a file. Then open the file (which will contain a list of all paths to the files of a specific criteria), read in the paths line for line, and do something accordingly. For example:

dim wsh
dim PathList() as string
ReDim PathList(0)
set wsh = createobject("WScript.Shell")
wsh.run "dir /a/s/b z:\*.mdb >Z:\tmpfile.dat", 0, 1
open "z:\tmpfile.dat" for input as #1
     do until eof(1)
          line input #1, tmp
          PathList(ubound(PathList())) = tmp
          redim preserve PathList(UBound(PathList()) + 1)
     loop
close #1
kill "z:\tmpfile.dat"

That should work to load the string array PathList with a list of paths and filenames to MDB files.... if your system doesn't support Scripting (such as the createobject of wscript.shell) then you may need to use API calls (shellexecute) so that you can shell the dir command AND have it wait for the shell to complete before your VB code continues (what a mess it would be, trying to read from a file of paths that isn't done yet huh?). Let me know how it turns out, or if you need further assistance.