Search entire database 1

[rrhandle]

I need to create a form in which the user can enter a string, then search the entire database for the string, and return the table name and field of all records where the string was found.

Any ideas?

 

[rubbernilly]

Here is code to search every field of every table. You'll need a Private Type declared on the form (or a public type declared somewhere else). This code is for a button named cmdSearch that uses whatever was entered in a TextBox named "txtSearchText"...

Here is the Private Type:

Private Type MySearchReturn
  srTable As String
  srField As String
  srValue As Variant
End Type

Private Sub cmdSearch_Click()
Dim srReturn() As MySearchReturn
Dim strSQL As String
Dim rs As DAO.Recordset
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Dim a As Long

a = 0
For Each tbl In CurrentDb.TableDefs
  For Each fld In tbl.Fields
    strSQL = "SELECT [" & fld.Name & "] FROM [" & tbl.Name & "] WHERE [" & fld.Name & "] Like '*" & txtSearchText & "*'"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    If rs.EOF = False Then
      rs.MoveFirst
      While Not rs.EOF
        ReDim Preserve srReturn(a)
        srReturn(a).srTable = tbl.Name
        srReturn(a).srField = fld.Name
        srReturn(a).srValue = rs(fld.Name)
        a = a + 1
        rs.MoveNext
      Wend
    End If
  Next
Next

For a = a - 1 To 0 Step -1
  Debug.Print srReturn(a).srTable & "::" & srReturn(a).srField & "::" & srReturn(a).srValue
Next a

Set rs = Nothing

End Sub

You should be able to manipulate that array for whatever output you want to provide the user, based on the properties of the type as defined.

HTH

Rubbernilly

 

[rrhandle]

EXCELLENT!

 

[rrhandle]

Here is another solution using DAO. It is even simpler. Just paste the code, and you are ready to go.
Thread 1153303

I never did get the ADO one above to actually work. Kept getting and "invalid SQL statement".