One of the more powerful operating system utilities that comes with the standard Windows 95, 98 and NT 4 interfaces is the File Search Utility. Included with this option is the ability to search all files in a given directory tree which have a particular text string. This then retrieves a list of files 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).

Now 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 query interface. This basically means that you are required to know the database intimately to guess where 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 schemas or you can simply use the powerful linking technology in Access to link to many different backend formats.

This article should benefit people who utilize SQL, system administrators who would like to pass on some of the searching chores to users and as a reference for Jet database structures. The sidebar describes the Find Text search extensions to SQL Server which shows how the popularity of the Internet Search engines has moved the requirements for text search technology quickly into mainstream computing.

The concept behind this database search utility is that by using Microsoft Access (Jet Engine) as a front end database interface, database links can be setup to a variety of different formats. The software has the following characteristics (see Figure 1)

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 database formats ranging from text files and spreadsheets, PC databases such as Paradox, Access and Dbase and through ODBC to specialized drivers to the more popular backend databases.

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 the first match so that the user knows which tables have the required string in them.

All the source for this search tool lies under the search button. Initially the software sets up the definitions of the variables and handles the form selections (Figure 1). The first technical bit of the software is establishing the workspace and opening the Jet database that was selected by the user. We then loop through all the tables in the Table collection (avoiding the Jet system tables).

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.

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. AndOrOpt is the option box that allows you to select whether to try and find both strings (if you use 2) in the same field or simply find one or the other. 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.

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 where a string was in the database.

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.

Extensions To The System

The software demonstrated only finds if a text string exists in a table and then reports it. You could improve on this by

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

Changing the Access system table search routines to work on your own database structures by interrogating your database schemas to find which fields are text searchable.

One of the new features SQL Server 7.0 is the "Full Text Search Extensions". These tools are designed to bring the Boolean text search utilities that you will have used at internet sites such as AltaVista, Excite, Infoseek and allow you to perform these types of searches using extensions to the SQL language. These extensions follow the ISO-SQL-3 functional methodology for full text syntax. For example

will return rows where the BookDescription has the word Database and also has the words Visual Basic grouped together.

When you have to select from multiple tables, the SQL then must incorporate the ContainsTable in the Where clause and the syntax would look similar to the following

These commands can be expanded using normal Boolean terms plus the use of the NEAR() phrase that will test that words occur within a certain number of words of another word. As text searches can return results which are not necessarily of equal ranking, the ContainsTable predicate can also return a ranking number which you can use to sort the quality of each individual match.

There are a number of penalties that will be associated with these extensions and the most notable being that you will need to purchase the enterprise edition of SQL server and that the actual Text indexes are kept outside the SQL database and need to be updated on a regular basis. To implement the tools you need to

Define which fields in which tables are going to have text search capabilities

Define locations where the Text indexes are to be stored

Define SQL procedures that will be run on regular basis to update the indexes

Create or purchase a user interface that will make the moderately onerous CONTAINS clause easier

SQL Sever comes with some wizards to help implement these extensions and if you are interested in reading more on this topic, you can read the Microsoft White Paper entitled "Extensions To SQL Server to Support Full-Text Search".

**end side note**

Notes On The Article

   Click here for the download file if you own "The Toolshed"  Else click here   

Adding Interactivity To A User Control

This article first appeared in the September edition of Visual Basic Developer produced by Pinnacle Publishing