Search all table in database 4

[rrhandle]

I need to search through all the tables, and each field in each table, to find a match with my WHERE clause. Another way to put this would be to say I need to find any records in the entire database that contain the word "dog."

Any idea how to do this without writing a query for each field in each table?

Thanks.

 

[M8KWR]

what you could do is to create a union select query, then off this query, query that query for "dog" in each field.

That would work, may be slow though...

 

[formerTexan]

You can raise a Tables collection and loop through each field of every table performing a string match along the way.

TO short circuit this somewhat, you can eliminate numeric fields if you are looking for "dog" or some other string.

And then to make this exercise useful, you will likely want to retain the field/table name and PK value whenever you hit a match. Using ADO rather than DAO will offer a somewhat less circuitous means of determining which is the PK field.

I have no idea how this will compare speed wise to using a Union query, but when I contemplated a Union query for 20 tables with varying numbers of fields, I had to look for the Alka Seltzer. Either way, if the database is substantial (hundreds of tables with dozens of fields or lots of verbose memo fields), I think restricting this capability to the wee hours of the morning would be wise.

Sorry for not offering the specifics, but working out the gory details won't be on my agenda until next month.

Cheers,
Bill

 

[r937]

that has to be one of the best posts i've seen in a very, very long time

star for the excellent writing, bill

r937.com | rudy.ca

 

[formerTexan]

Howdy,

As a postscript, following is a sample function. It is a rough and rather untested draft and will need some alteration for a particular setting, however as it stands it is "plug n play". But first a bit of rambling about a potential issue.

Lets say a user wants to search for a commonly used city in a database with numerous addresses. We will use "Denver" for this example. In a typical 3rd normal database, the search results are likely to return only one match, in a Cities lookup table. All of the other references to Denver are merely going to be foreign keys "pointing" back to the lookup table. So the result will be an unhappy user wondering where all the other instances of Denver have gone to.

So the next step will be to use ADOX and raise all of the foreign key fields and then do a search for Denver's key value. But alas, if you have using autonumbers for your primary keys, you are out of luck. Because the key value for Denver likely matches all sorts of other keys in various tables. And that gives me an excuse to bring up another topic.

There are two "solutions" to this problem. One is to denormalize which I will quickly say (before I get tarred and feathered)is not really an option. The real solution is to use non-repeating (unique) primary key values. This is a good idea for a number of reasons, but the case in hand serves as an excellent illustration. By using non-repeating values (that is, at least non-repeating for the database if not for the universe), all of the instances of the key value for Denver can be effectively be located therefore making for a satisfied user.

Private mastr() As String

' by Bill Butler : thewdata_sa
' November 2005, Cape Town
' =============================================
' Searches text and memo fields in all non-system tables for a designated string (strMatch).
' Returns array with table name/field name/PK field name/PK value for matches
' ==============================================
' set ADOX references
' multi-column primary keys generate multiple returns (one for each field in the PK)
' CAVEAT: no returns for tables without PK's
Private Function SearchDB(strMatch As String) As Variant
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column
Dim idx As ADOX.index
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim lngWhere As Long
Dim strSearch As String
Dim iCt As Integer

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection

For Each tbl In cat.Tables
If tbl.Type = "TABLE" Or tbl.Type = "link" Then ' No views, queries or system tables).

rs.Open (tbl.Name), cnn, adOpenForwardOnly, adLockReadOnly

Do While Not rs.EOF
For Each fld In rs.Fields
If fld.Type = "202" Or fld.Type = "203" Then ' just text and memo fields for this version
With fld
strSearch = fld.value & vbNullString
lngWhere = InStr(strSearch, strMatch) ' Find string in text and return its position to intWhere.
If lngWhere > 0 Then ' returns 0 if no match
' If match found.
With tbl
For Each idx In .Indexes ' find PK field
With idx
If idx.PrimaryKey Then ' the array only loads when a PK exists
For Each clm In .Columns ' get PK field name
'Debug.Print tbl.Name & ";" & fld.Name & ";" & clm.Name & ";" & rs(clm.Name) & ";"
ReDim Preserve mastr(3, iCt)
mastr(0, iCt) = tbl.Name
mastr(1, iCt) = fld.Name
mastr(2, iCt) = clm.Name
mastr(3, iCt) = rs(clm.Name)
iCt = iCt + 1
Next clm
End If
End With
Next idx
End With
End If
End With
End If
Next
rs.MoveNext
Loop
rs.Close
End If
Next

cnn.Close
Set rs = Nothing
Set cnn = Nothing
SearchDB = mastr()
End Function

 

[rrhandle]

That is GREAT!
Thank you.

 

[JerryKlmns]

rrhandle,

I think that this is more than GREAT!

And surely formerTexan deserves more than one *. Would you concider making it a FAQ??

 

[rrhandle]

Absolutely. I was already planning to post it on my site. Mmm... how does one go about making it a FAQ?

 

[nissan240zx]

Excellent answer.
Deserves a STAR...

Happy Holidays..!!!!

A good programmer is someone who looks both ways before crossing a one-way street. - Doug Linder