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.

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

Full article...