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

 

[Guest]

Here is the code you asked for. Note that it will only return the first
occurrance. If there are other fields and other tables where the value might
be, you will not see them. Also, the return value will be the table name a |
and the field name. For example, if the Table is named Frodo and the field
is named Foo, the return will be "Frodo|Foo".

Function FindFieldValue(strFind As String) As String
Dim tdfs As TableDefs
Dim rst As Recordset
Dim lngTblCount As Long
Dim lngFldCount As Long
Dim lngTblTot As Long
Dim lngFldTot As Long
Dim blnFoundIt As Boolean

FindFieldValue = "Not Found"
Set tdfs = CurrentDb.TableDefs
lngTblTot = tdfs.Count - 1
For lngTblCount = 0 To lngTblTot
Set rst = tdfs(lngTblCount).OpenRecordset
If rst.RecordCount = 0 Then
Exit For
Else
rst.MoveLast
rst.MoveFirst
End If
Do While Not rst.EOF
lngFldTot = rst.Fields.Count - 1
For lngFldCount = 0 To lngFldTot
If rst.Fields(lngFldCount).Value = strFind Then
FindFieldValue = tdfs(lngTblCount).Name & "|" _
& rst.Fields(lngFldCount).Name
blnFoundIt = True
Exit For
End If
Next lngFldCount
If blnFoundIt Then
rst.Close
Exit For
Else
rst.MoveNext
End If
Loop 'While Not rst.EOF
rst.Close
Next lngTblCount
Set rst = Nothing
Set tdfs = Nothing
End Function

 

[Douglas J. Steele]

You probably should have a check that the field type is Text, otherwise
you'll probably end up with a Type Mismatch error.

For lngFldCount = 0 To lngFldTot
If rstFields(lngFldCount).Type = dbText Or _
rstFields(lngFldCount).Type = dbMemo Then
If rst.Fields(lngFldCount).Value = strFind Then
FindFieldValue = tdfs(lngTblCount).Name & "|" _
& rst.Fields(lngFldCount).Name
blnFoundIt = True
Exit For
End If
End If
Next lngFldCount

As well, it's not clear whether Rob is looking for fields with just Widget
in them, or fields that have Widget anywhere in them. If the latter, then
replace

If rst.Fields(lngFldCount).Value = strFind Then

with

If InStr(rst.Fields(lngFldCount).Value, strFind) > 0 Then

 

[Guest]

I considered all the issues you raised here. I puprposely left it is
simplistic as possible. If he has those other requirements, it will be a
good learning exercise to make the necessary modifications. Also, as I
stated in my post, it will only find the first occurance.

I put that together per his requestt. I do think I will add some
functionality to it to give options to find exact match, anywhere in a
string, find all, etc.

But thanks for your analysis.

As to checking the type, I was going to put that in, but in testing it,
there was no issue with type matching.