Search Tables Collection for Text or Whole Field Number

[Ronster]

I'm using MS Access 2003 and I am looking for a function that will
search the
entire Tables Collection for a specific string or whole field number
(not part of a field).For example if I enter "widget" or "123456" it
interrogates the entire tables collection and returns the table name
and column name where the data is found.

I have found some excellent code in this group for seaching for text
but noting if I looking for a whole field number like 123456. (not
345).

Appreciate any help.

 

[Guest]

This is what I think you want; however, note that it will only find the first
occurance.

Function FindFieldValue(varFind As Variant) 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
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
lngFldTot = rst.Fields.Count - 1
For lngFldCount = 0 To lngFldTot
If rst.Fields(lngFldCount).Value = varFind 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
End If
Next lngTblCount
Set rst = Nothing
Set tdfs = Nothing
End Function

 

[Ronster]

Excellent program Klatuu! And it's quick! Nice work.

Since I need to be able to search all tables I have made some
modifications to your code.

This will search all tables for one find only then move to the next
table.

Function SearchAllTablesForValue(varFind As Variant) 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

SearchAllTablesForValue = "Not Found"
Set tdfs = CurrentDb.TableDefs
lngTblTot = tdfs.Count - 1
For lngTblCount = 0 To lngTblTot
Set rst = tdfs(lngTblCount).OpenRecordset
If rst.RecordCount <> 0 And Left(UCase(tdfs(lngTblCount).Name), 4)
<> "MSYS" Then
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
lngFldTot = rst.Fields.Count - 1
For lngFldCount = 0 To lngFldTot
If rst.Fields(lngFldCount).Type = dbLongBinary Then
lngFldCount = lngFldCount + 1
End If
If rst.Fields(lngFldCount).Value = varFind Then
SearchAllTablesForValue = "Table - " &
tdfs(lngTblCount).Name & _
Chr(13) & Chr(13) & "Field - " &
rst.Fields(lngFldCount).Name
MsgBox SearchAllTablesForValue, , "Searching For:
" & varFind
Exit Do
End If
Next lngFldCount
If lngTblCount >= lngTblTot Then
rst.Close
Exit For
Else
rst.MoveNext
End If
Loop 'While Not rst.EOF
rst.Close
End If
Next lngTblCount

MsgBox "Search Complete"

Set rst = Nothing
Set tdfs = Nothing

End Function

' *****************************************

Sub MyTestSub()

' Examples:
' DateValue("11/30/2005") (for 11/30/2005)
' "moser"
' 34704
' 13000 (for $13,000.00)

SearchAllTablesForValue DateValue("11/30/2005")

End Sub

 

[Guest]

Glad it worked for you. I can see where you may want to find in multiple
tables, I considered that, but haven't had time to get back to it. I think
I'll steal your code