Finding Other Databases 1

[GummowN]

The powers that be have decided that the method in which a number is calculated should be changed. We have many hundreds of reports generated from a large number of databases.

I have written code that scans the SQL of each query in a database and tells me the query and the string it contains.

However does anyone know any code to rip through a directory and append the path of all mdb files to a local table?

A strange request I know, but you guys have always come up with an answer in the past

Thanks in advance

 

[SBendBuckeye]

Set a reference to Microsoft Scripting Runtime and then check out the FileSystemObject. It exposes a drives collection and then a RootFolder object. The RootFolder object exposes Subfolders and Files collections which you can then recurse and iterate to get what you need.

Sorry I don't have time to post an example, but hopefully this will get you started. If you run into trouble post again and I'll try to help you.

Good Luck!

 

[GummowN]

I thought I would see what I could come up with myself, so now incumbered with a headache but a good sense of achievement here is what I came up with.

I wrote it so you can comment it!!!

Public Function fncCheckQueryforText(strFind As String) As Boolean

On Error Resume Next
Dim dbs As Database, dbOther As Database, rst As Recordset, qdf As QueryDef
Dim strSearch As String
Dim fs As FileSearch
Dim i As Integer
Dim app As Workspace
Dim strPath As String

Set app = DBEngine.Workspaces(0)
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_querys&quot
strSearch = "*" & strFind & "*"

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = "\\bctdev\projects"
.SearchSubFolders = True
.FileType = msoFileTypeDatabases
If .Execute(SortBy:=msoSortbyFileName, SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
strPath = .FoundFiles(i)
Set dbOther = app.OpenDatabase(strPath, False, True)
For Each qdf In dbOther.QueryDefs
If qdf.SQL Like strSearch Then
With rst
.AddNew
.Fields("Database&quot = strPath
.Fields("Query&quot = qdf.Name
.Fields("contains&quot = strFind
.Update
End With
End If
Next qdf
Next i
Else
MsgBox "There were no files found."
End If
End With

rst.Close
Set dbs = Nothing
fncCheckQueryforText = True

End Function

 

[SBendBuckeye]

Very nicely done! I wasn't even thinking of FileSearch when I made my suggestion above. It looks like you nailed it. I wouldn't change anything except that I always set all objects = nothing at the end of my procedures.

Have a great day!

 

[GummowN]

That will be me being too excited by code that works too actually tide or comment it ;-)