Need Code to Search for Databases

[Antonette]

As a result of writing so many Access programs, I want to open a form that
will search a drive and find all .mdb files.

I have worked with TableDefs before, but cannot remember where it is at.

Is there a way to search for the mdb files without using the Microsoft
Search Engine.

I know that in (NT 2.0 or NT 3.0, also Windows 97) you could search for text
in an access DB or even a table name.

The other programmers here remember this also, but it seems that the users
in our group never knew you could do this.

After I get this to work I am then going to search for specific tables in
each .mdb.

Thanks in Advance

Toni

 

[John W. Vinson]

As a result of writing so many Access programs, I want to open a form that
will search a drive and find all .mdb files.

I have worked with TableDefs before, but cannot remember where it is at.

It's a collection in the Database object. See below.

Is there a way to search for the mdb files without using the Microsoft
Search Engine.

The Dir function can do this. See the VBA help for Dir.

I know that in (NT 2.0 or NT 3.0, also Windows 97) you could search for text
in an access DB or even a table name.

The other programmers here remember this also, but it seems that the users
in our group never knew you could do this.

I wasn't either!!!

After I get this to work I am then going to search for specific tables in
each .mdb.

Partially tested code. Assumes that either there is no workgroup security or
that you've already Joined the workgroup, and that there is no database
password (the error will be trapped if this isn't the case). Looks only for
..mdb files but you can tweak that. This doesn't search subdirectories but you
can use some recursive dir calls to do so.


Public Sub FindTableAnywhere(strDirectory As String, strTablename As String)
Dim strFile As String
Dim tdf As DAO.TableDef
Dim ws As DAO.Workspace
Dim db As DAO.Database
On Error GoTo Proc_Error

Set ws = DBEngine(0) ' get current workspace
strFile = Dir(strDirectory & "\*.mdb")
Do While strFile <> "" ' loop until you run out of mdb files
Set db = ws.OpenDatabase(strDirectory & "\" & strFile)
Set tdf = db.TableDefs(strTablename)
If Not (tdf Is Nothing) Then
Debug.Print strTablename & " is in " & strFile
End If
strFile = Dir()
Loop
Proc_Exit:
Exit Sub
Proc_Error:
Select Case Err.Number
Case 3265 ' item not found in this connection
Resume Next
Case Else
MsgBox "Error " & Err.Number & " in FindTableAnywhere:" & vbCrLf &
Err.Description
Resume Proc_Exit
End Select
End Sub