Searching For Text Anywhere In A Database                                                    

<< Click to Display Table of Contents >>

Navigation:  Tables >

Searching For Text Anywhere In A Database                                                    

 

dbsearch1_small by  Garry Robinson for Access 2007 and below

Thanks to internet search engines, users are used to searching for text but unfortunately even the desktop search tools from Microsoft and Google do not have a search that looks into tables (easily). This article outlines a simple tool to search tables or linked tables to find if a text string(s) exist in those tables. It does this by building SQL queries after analysing the the system tables in any Access/Jet database. This code is suited to Access 2000, XP, 2003 and 2007.

 

Introduction

My favourite search is actually a search of my own computer using Google desktop search (Vista has a similar tool). Included with this search is a list of all files which have a particular text string that you can then open. Generally then programs that are associated with that file will have a search utility so that you can locate that text string within the file (spreadsheets and word processing documents being prime examples).

Unfortunately this Search utility is found wanting when it comes to databases, well actually it offers nothing at the moment. So what happens when the string is located in a database. If you (can) open the database you are presented with an application or possibly a large number of tables and very few basic tools to search the tables themselves or a cumbersome query interface. This basically means that you are required to know the database intimately to guess where and if that text string exists. This article shows you how you can search all the tables in a Access database and then return which tables have the required string in them. The same techniques could be applied to other database formats using the powerful Microsoft Access linking technology.

Finding A Text String

The Access form (see Figure 1) that drives the database search that I am outlining can be imported into your database.  The search tool then searches every table or linked table in that database. The software has the following characteristics

A front end Access database is established and links are made to all the Backend tables that you want a particular user or group of users to search. Access allows you to link to Access databases, text files and spreadsheets, and through ODBC to specialized drivers to the more popular backend databases such as SQL Server or MySQL or Oracle.

The System Tables are then analyzed so that we can establish all the tables inside the database.

All these tables are then searched to find all the fields in the tables that contain text data. All numerical, date and blob type data fields are ignored in this process. A SQL statement is then constructed to query all the text fields in each table.

We then run the query on each table using recordsets and find and report any successful (first) match so that the user knows which tables have the required string in them.

 

dbsearch1
Figure 1 - Tool to search backend databases for the location of strings

 

The Code

All the source for this search tool lies under the search button. The first technical bit of the software is to loop through all the tables in the Table collection (avoiding the Jet system tables and temporary tables).

' Establish the phrases to be used for searching from

' the fields on the form. The database file is selected

' using standard VB file controls

 Set myDb = CurrentDb ' 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" And Left(tableName, 4) <> "usys" _

   And Left(tableName, 1) <> "~" 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 are going to use in the search of this table is reset.

 

 '     Now find the text fields

 

       booRstSch = True

       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

Next and importantly for Access databases is to manage the variety of names that can be given to fields. After much experience with end user databases, I have found that the only thing that you do not run into very often is table and fieldnames that follow sensible naming conventions. Mostly you will 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.

  '         Jet fieldnames can include unusual letters

           blankpos = InStr(1, fldStr, " ") + _

            InStr(1, fldStr, "#") + _

            InStr(1, fldStr, "-") + _

            InStr(1, fldStr, "/")

           If blankpos > 1 Then

 

'            Make sure blank spaces and other odd

'            fieldname characters are handled correctly

             fldStr = "[" & fldStr & "]"

 

           End If

Now we are 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. For this system, the searches utilize the Jet Engine LIKE phrase which is the same as MATCHES in a SQL backend database. The wildcard character in Jet is an * whilst in ANSI SQL it can be either a percentage sign % or an underscore. Either way the jet engine sorts this out irrespective of what backend database you are working on. 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. Note that the software allows you to enter two search strings which can be expanded to more.

            If Len(wherestr) > 1 Then

             wherestr = wherestr & " or "

           End If

           wherestr = wherestr & "(" & fldStr _

            & " like '" & searchString(1) & "'"

           If Len(searchString(2)) > 1 Then

           

             wherestr = wherestr & OrStr & fldStr _

              & " like '" & searchString(2) & "')"

           Else

             wherestr = wherestr & ")"

           End If

         End If

         

       Next j

Now we have 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 stage the software then writes the full SQL to the textbox called sqlFilter and labels the search as successful or not. It does not continue on through the full table after matching the filter as the aim of the software was to tell you if a string was in a table.

'       Now search for a string that matches

 

       If Len(wherestr) > 1 Then

         With rstSearchTable

         

           .FindFirst wherestr

           

           If .NoMatch Then

             

              sqlFilterNot = sqlFilterNot & 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 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 is sent to the sqlFilter text box is padded out using the vbCrLF constant that outputs carriage return and linefeed.  That is all the code does, the rest is upto to you to craft the software to your needs.

Sample Where Clause for a table linked to the Outlook Inbox
 

where (Icon like 'access@gr-fx.com') or (Subject like 'access@gr-fx.com') or 
([From] like 'access@gr-fx.com') or ([Sender Name] like 'access@gr-fx.com') or 
(CC like 'access@gr-fx.com') or (To like 'access@gr-fx.com') 
or ([Subject Prefix] like 'access@gr-fx.com') or ([Normalized Subject] like 'access@gr-fx.com')

 

Extensions To The System

The software demonstrated only finds if a text string exists in a table and then reports it. It also repeats filters that haven't found any text strings and saves these to a separate log. You could improve on this by

Running a query for each individual field in each table and reporting the fields that matched the Search phrases.

Make it easy to see the data by actually outputting the rows with a match by creating software using

 

Currentdb.QueryDefs(item).SQL = "select .... from ... where ..." 

 

So thanks for reading my latest story, If you end up adapting this software to your database, drop me an email at www.gr-fx.com and let me know how it goes.

About The Author

Garry Robinson runs a software development company called GR-FX based in Sydney, Australia. Over the years he has written a number of articles on topics such as Consolidating Data Using Queries, Using Excel as a Backend Database and a simple fix for the Access Bookmark Bug. He developed a popular shareware data mining tool that will allow you to drilldown on data in any linked backend database and then to visualize that data in 2 or 3D using Microsoft Chart. Contact details www.gr-fx.com

key Your Sample Database Is Called   "vbsearch.zip"

 
 
This can be purchased with all the other downloads on this page

 

 

Articles on search appeared in Smart Access Aug-2000  and Jun-2002