Search A Database

[jsimard]

Is there a way to search an entire database? Almost like a google search where a user could put in a few key words and it would retrieve all records no matter which fields the key words appeared in?

[burrina]

Yes, here is link that may be what you are looking for. http://www.databasejournal.com/featu...a-Database.htm


HTH

[ranman256]

Try:

Public Sub ScanAllForText(ByVal pvVal, ByVal pvDataType)
Dim rst, rst2  'As Recordset
Dim tdf As TableDef
Dim sSql As String
Dim fld As Field
Dim vDelim, vTyp
Select Case pvDataType
   Case "T"  'text
     vDelim = "'"
     vTyp = 10
     
   Case "D"  'date
     vDelim = "#"
     vTyp = 8
   
   Case Else  'number
End Select
Debug.Print pvVal; " found in: "
For Each tdf In CurrentDb.TableDefs   'each tbl
  If InStr(tdf.Name, "msys") = 0 Then   'skip sys tables
        Set rst = CurrentDb.OpenRecordset("Select * from [" & tdf.Name & "]")
        With rst
           For Each fld In .Fields    'each fld
              If fld.Type = vTyp Or vTyp = "" Then
                    sSql = "select [" & fld.Name & "] from [" & tdf.Name & "] where [" & fld.Name & "] = " & vDelim & pvVal & vDelim
                    Set rst2 = CurrentDb.OpenRecordset(sSql)
                    If rst2.RecordCount > 0 Then Debug.Print tdf.Name & "." & fld.Name
              End If
           Next
        End With
  End If
Next
Set rst = Nothing
Set rst2 = Nothing
Set tdf = Nothing
Set fld = Nothing
MsgBox "done"
End Sub