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.
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.
Figure 1 - Tool to search backend databases for the location of strings
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( _
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 & "]"
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 "
wherestr = wherestr & "(" & fldStr _
& " like '" & searchString(1) & "'"
If Len(searchString(2)) > 1 Then
wherestr = wherestr & OrStr & fldStr _
& " like '" & searchString(2) & "')"
wherestr = wherestr & ")"
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
If .NoMatch Then
sqlFilterNot = sqlFilterNot & UCase(tableName) _
& " : Not Found" & vbCrLf & _
"Select * from " & tableName _
& " where " & wherestr & ";" _
& vbCrLf & vbCrLf
sqlFilter = sqlFilter & UCase(tableName) _
& " : FOUND" & vbCrLf & "Select * from " _
& tableName & " where " & wherestr & ";" _
& vbCrLf & vbCrLf
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 'email@example.com') or (Subject like 'firstname.lastname@example.org') or
([From] like 'email@example.com') or ([Sender Name] like 'firstname.lastname@example.org') or
(CC like 'email@example.com') or (To like 'firstname.lastname@example.org')
or ([Subject Prefix] like 'email@example.com') or ([Normalized Subject] like 'firstname.lastname@example.org')
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
If you do NOT own "The Toolbox", Click here to find out how to purchase The Toolbox.
You will find the full size screen shots in Toolbox
Sample database is suited to all versions of Access