Code to Search Tables Collection for a Specific String

[robboll]

Using MS Access 2003 I am looking for a function that will search the
entire Tables Collection for a specific string in text or memo fields.
For example if I enter "widget" it interrogates the entire tables
collection and returns the table name and column name where the word is
found, otherwise returns "Not Found"

Any help with this greatly appreciated.

RBollinger

[Salad]

robboll wrote:

Using MS Access 2003 I am looking for a function that will search the
entire Tables Collection for a specific string in text or memo fields.
For example if I enter "widget" it interrogates the entire tables
collection and returns the table name and column name where the word is
found, otherwise returns "Not Found"

Any help with this greatly appreciated.

RBollinger

I've created code to search for text fields. I don't think you can use
FindFirst with memos. You may have to create a SQL string and run a
query for memo fields.

Sub ScanFieldsForString()

Dim dbs As Database
Dim fld As Field
Dim tdf As TableDef
Dim rst As DAO.Recordset
Dim strFind As String

Set dbs = CurrentDb

With dbs
For Each tdf In dbs.TableDefs
If Left(tdf.name, 4) <> "Msys" Then
Set rst = CurrentDb.OpenRecordset(tdf.name, dbOpenSnapshot)
For Each fld In tdf.Fields
If fld.Type = dbTex Then
strFind = "[" & fld.name & "] Like
""*SearchString*"""
rst.FindFirst strFind
If Not rst.NoMatch Then MsgBox tdf.name & " " &
fld.name
End If
Next fld
rst.Close
Set rst = Nothing
End If
Next tdf
End With
dbs.Close
Set dbs = Nothing
MsgBox "Done"
End Sub