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.
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
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 'firstname.lastname@example.org') or (Subject like 'email@example.com') or ([From] like 'firstname.lastname@example.org') or ([Sender Name] like 'email@example.com') or (CC like 'firstname.lastname@example.org') or (To like 'email@example.com') or ([Subject Prefix] like 'firstname.lastname@example.org') or ([Normalized Subject] like 'email@example.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.
» See All Articles by Columnist Garry Robinson