Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS Access

Posted Oct 17, 2008

Searching For Text Anywhere In a Database

By Garry Robinson

Summary

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) exists in those tables. It does this by building SQL queries after analyzing the system tables in any Access/Jet database. This code is suited to Access 2000, XP, 2003 and 2007.

Introduction

My favorite 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, the 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 an 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 back-end 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 back-end 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 of these tables are searched to find all of 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 of the text fields in each table.

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

Figure 1 - Tool to search back-end databases for the location of strings
Figure 1 - Tool to search back-end databases for the location of strings

The Code

All of the source for this search tool lies under the search button. The first technical bit of the software is to loop through all of 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 of 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 field names 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 back-end 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 back-end 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 that 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 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 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 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 up 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 article. 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.

Download dbSearchAll.mdb.

» See All Articles by Columnist Garry Robinson



MS Access Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS Access Forum
Topic By Replies Updated
Help With Microsoft Access kasy 0 September 4th, 07:35 PM
Linked table not sorting or filtering - ODBC error Java 1 August 28th, 10:37 AM
Use Parameter in select statement (Sql in Microsoft Access) katty.jonh 1 July 25th, 06:45 AM
Query Issue algebroni 7 July 23rd, 04:22 PM