How to search all records of all tables

  • Thread starter Thread starter gianni_sacco
  • Start date Start date
G

gianni_sacco

Is it possible to easily search all fields of all tables
for specific criteria?

I've stumbled across some articles on how to do this but
it's beyond my ken, not being a VB guy in the least. Would
anybody have a macro or module that I could cut and paste
to accomplish this?
 
Is it possible to easily search all fields of all tables
for specific criteria?

I've stumbled across some articles on how to do this but
it's beyond my ken, not being a VB guy in the least. Would
anybody have a macro or module that I could cut and paste
to accomplish this?
Click to expand...

There's no easy way to do this, and in a properly normalized database
you would essentially never need to do this. Each field should contain
a DIFFERENT attribute of the entity modeled by the table. It wouldn't
make sense to search a Social Security Number field, a Zipcode field,
and a LastName field for the same criterion - they're different
logical domains!

If you really want to do this, you'll have to construct a Query for
every table, with a criterion on every field, with OR connecting the
criteria; and open all these queries. Since they'll have different
numbers and types of fields you can't use a UNION query.

Could you explain what the structure of your tables is, that this is
something that you need to do!?
 
Hi John, I hear you. To make a long story short, this
horrendous database is the business system for the small
company I work for. They never got an ER diagram or any
substantive documentation, so we have no map of how this
thing was put together. And due to "less than stellar"
relations with the vendor that created this joke, we can't
get anything from them either.

Bottom line is that this is what I'm stuck with. When I
need to find information, I have to go on a fishing
expedition through dozens of poorly named tables and just
troll for incidences of what I'm looking for so I can
determine in which tables/fields it resides and then
figure out which of those tables is what I want to query.

I did come across this article, but I don't know how to
adapt it to make it work in Access:

http://vyaskn.tripod.com/search_all_columns_in_all_tables.h
tm

This is a total Dilbert-like scenario but there it is. Any
tips most welcome, I'm at my wits' end.
 
I did come across this article, but I don't know how to
adapt it to make it work in Access:

http://vyaskn.tripod.com/search_all_columns_in_all_tables.h
tm
Click to expand...

That's effectively a function if you were to translate it to VBA.

Define a new class call 'SearchResults' and put these members in it.

' ***
Public TableName As String
Public ColumnNames As New VBA.Collection
Public ResultRows As New VBA.Collection
' ***

Put these 2 procs in a standard module and run 'TestSearchAllTables'. The
actual work is done in SearchAllTables routines. You can modify the SQL
in it so that only one column is searched against if you need to figure
out which particular column contained the string instead of the table.

' ***
Sub TestSearchAllTables()
Dim results As VBA.Collection
Dim result As SearchResults
Dim i As Integer, j As Integer, k As Integer

Set results = SearchAllTables("An")
If results.Count > 0 Then
For i = 1 To results.Count
Set result = results.item(i)
With result
Debug.Print "***************"
Debug.Print "Result found in: " & .TableName
Debug.Print "***************"
For j = 1 To .ColumnNames.Count
Debug.Print .ColumnNames.item(j),
Next
Debug.Print
Debug.Print "---------------------"
For j = 1 To .ResultRows.Count
For k = 0 To .ColumnNames.Count - 1
Debug.Print .ResultRows.item(j)(k),
Next
Next
Debug.Print
End With
Next
Else
Debug.Print "No records found"
End If
End Sub

Function SearchAllTables(criteria As String) As VBA.Collection
Dim rs As dao.Recordset
Dim tdf As dao.TableDef
Dim db As dao.Database
Dim fld As dao.Field
Dim sql As String, i As Integer, j As Integer
Dim doInclude As Boolean
Dim results As VBA.Collection
Dim item As SearchResults, items() As String
On Error GoTo ErrHandler

Set db = CurrentDb
Set results = New VBA.Collection

For Each tdf In db.TableDefs

doInclude = (Not CBool(tdf.Attributes And _
dbSystemObject)) And _
(Not CBool(tdf.Attributes And dbHiddenObject))
If (doInclude) Then
sql = "select * from [" & tdf.Name & _
"] where "
For Each fld In tdf.Fields
sql = sql & "[" & fld.Name & "] like '*" & _
criteria & "*' or "
Next
sql = Left$(sql, Len(sql) - 3)
Set rs = db.OpenRecordset(sql)

If (rs.RecordCount > 0) Then
Set item = New SearchResults

item.TableName = tdf.Name
rs.MoveFirst
ReDim items(0 To rs.Fields.Count - 1)
For i = 0 To rs.RecordCount - 1
For j = 0 To rs.Fields.Count - 1
items(j) = rs.Fields(j).Value & vbNullString
Next
item.ResultRows.Add items
rs.MoveNext
Next
For j = 0 To rs.Fields.Count - 1
item.ColumnNames.Add rs.Fields(j).Name
Next
results.Add item:=item, Key:=tdf.Name
End If
rs.Close
End If
Next

Set SearchAllTables = results

Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing

Exit Function
ErrHandler:
With Err
MsgBox "Error: " & .Number & vbCrLf & _
.Description, vbOKOnly Or vbCritical, "SearchAllTables"
End With
Set tdf = Nothing
Set fld = Nothing
Set rs = Nothing
Set db = Nothing
End Function

' ***

-- Dev
 
Back
Top