DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Subfolder Search Loop

•

Join Date: Jul 2005

Posts: 1

Reputation: lcannon is an unknown quantity at this point

Solved Threads: 0

lcannon

Offline

Newbie Poster

Subfolder Search Loop

Jul 18th, 2005

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

•

Join Date: Dec 2004

Posts: 2,413

Reputation: Comatose is a jewel in the rough

Solved Threads: 211

Comatose

Offline

Taboo Programmer

Re: Subfolder Search Loop

Jul 19th, 2005

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:

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
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"
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.