Searching for Text Anywhere in a Database

Garry Robinson

One of the more powerful operating system utilities that comes with Windows today is the File Search Utility. We can search all files in a given directory tree that have a particular text string and, once we locate and open the desired file, might very well be able to continue searching using the application’s own search utility–for example, Word or Excel’s Search.

But what happens when the string is located in a database? If you can (you have rights to) open the database, you might be presented with an application, but you might not see anything unless you know what the tables’ names are, and so on. In this article, I’ll show you how you can search all the tables in an Access database and then return which tables have the required string in them. The same techniques could be applied either directly to other databases or indirectly via Access’s linking feature. [If you’re using SQL Server 7.0, be sure to find out whether the database has been indexed for full-text searches. You might also want to order part number 098-82314 or download Microsoft’s excellent white paper, "Textual Searches on Database Data Using Microsoft SQL Server 7.0," from www.microsoft.com/sql. You’ll find out how you can use it to do proximity and natural language searches and even assign weights, and about the overhead and performance hit associated with it.–Ed.]

Finding a text string

The concept behind this database search utility is that by using Microsoft Access–the Jet Engine–as a front-end database interface, database links can be set up to let you search just about any data source you want. Here’s the program logic:

The code

All of the source code for this search tool lies under the search button. Initially, the software sets up the definitions of the variables and handles the form selections (see Figure 1). The first technical bit of the software is establishing the workspace and opening the Jet database that was selected by the user. We then loop through all the tables in the Table collection (avoiding the Jet system tables):

' Create Microsoft Jet Workspace object
Set wrkJet = CreateWorkspace(","admin", ",dbUseJet)
Set myDb = wrkJet.OpenDatabase(selectedFile, True)
' Loop through all tables extracting the names
For i = 0 To myDb.TableDefs.Count - 1
   Set MyTable = myDb.TableDefs(i)       
   tableName = MyTable.Name 
   If Left(tableName, 4) <> "MSys" Then

Now the software opens a recordset and loops through all the fields in each table to establish which of the fields are actually text fields. At this stage, the filter that we’re going to use in the search of this table is reset:

numFields = MyTable.Fields.Count        
Set rstSearchTable = myDb.OpenRecordset( _
  tableName, dbOpenSnapshot)
wherestr = "
For j = 0 To numFields - 1
   Set myField = MyTable.Fields(j)
   fldStr = myField.Name
   fldType = myField.Type
   if fldType = dbText Then

Now the hard part. It doesn’t take many database encounters before you realize how creative end users can be with their names–especially given Access’s flexibility. Mostly you’ll find extended descriptions with spaces like "Emergency Contact First Name" or % or # or even full stops ("."). Access manages this internally by allowing you to wrap square brackets around the field or table name [ ]. The following code shows how to handle some of these:

blankpos = InStr(1, fldStr, " ") + _
   InStr(1, fldStr, "#") + InStr(1, fldStr, "/")
If blankpos > 1 Then
   fldStr = "[" & fldStr & "]"
End If

Now we’re going to assemble the SQL filter string for the text fields according to the entries that have been made for searching on the main screen. Because I’m assuming that we’re searching an Access database, we use Jet’s LIKE in lieu of more standard SQL MATCHES. The wildcard character in Jet is an asterisk (*), while in ANSI SQL it can be either a percentage sign (%) or an underscore (_). Either way, the Jet engine sorts this out irrespective of what back-end database you’re working on via ODBC. AndOrOpt is the option box that allows you to select whether to try to find both strings (if you use two) in the same field or simply find one or the other. SearchString is the field on the form where you enter the search string. Use the wildcard character in this field to search in any location of the field rather than invoking an exact match.

If andOrOpt(1) Then 
   andOrStr = " and "
Else
   andOrStr = " or "
End If 
If Len(wherestr) > 1 Then
   wherestr = wherestr & " or "
End If
wherestr = wherestr & "(" & fldStr  _
   & " like '" & searchString(1) & "'"
If Len(searchString(2)) > 1 Then        
   wherestr = wherestr & andOrStr & fldStr _
   & " like '" & searchString(2) & "')"
Else
   wherestr = wherestr & ")"
End If

Now that we’ve built a suitable filter for the table, we start up the recordset that we established earlier and search for any success with the filter using the Recordset FindFirst method. At this point, the software writes the full SQL to the text box called sqlFilter and labels the search as successful or not. It doesn’t continue on through the full table after matching the filter, as the aim of the software was to tell you where a string was in the database.

If Len(wherestr) > 1 Then
   With rstSearchTable          
   .FindFirst wherestr            
    If .NoMatch Then
        sqlFilter = sqlFilter & UCase(tableName) _ 
           & " : Not Found" & vbCrLf & _
           "Select * from " & tableName _
           & " where " & wherestr & ";" _
           & vbCrLf & vbCrLf
       GoTo nextTable
   End If
   sqlFilter = sqlFilter & UCase(tableName) _
      & " : FOUND" & vbCrLf & "Select * from " _
      & tableName & " where " & wherestr & ";" _
      & vbCrLf & vbCrLf 
   End With
End If

The software then continues on through all of the tables in the database, building a full list of those searches that either failed or were successful. To make the text output more readable, the text string that’s sent to the sqlFilter text box is padded out using the vbCrLF constant that outputs carriage returns and linefeed.

Ideas for elaboration

The software demonstrated only determines whether a text string exists in a table, and then reports it. You could improve on this by running a query for each individual field in each table and reporting the fields that matched the query, changing the Access system table search routines to work on your own database structures by interrogating your database schemas to find which fields are text searchable, allowing for sequential searches (find next), and so on.

Download VBSRCH.exe

Garry Robinson runs a software development company called GR-FX based in Sydney, Australia. Several of his articles have appeared in Smart Access, and he’s written a popular shareware data mining tool that allows you to drill down on data in any linked back-end database and then visualize that data in 2-D or 3-D using MS Chart. +61 2 9665 2871, www.gr-fx.com, Garry@gr-fx.com.