CODEWITHSUNDEEP
sqlms-access
user2271875
user2271875

Reputation: 325

What is the best way for implementing a fast user-interface wildcard text search (MS Access)?

I have several fields in a table that I want the user to be able to search for using wildcards. What I'm really looking for is a way to do a wildcard search that promotes a fair user experience (e.g. not clunky; decent speed; if not fast, then maybe it processes in the background). I know wildcard searches are frowned upon; and I may even scrap this feature if there's no viable solution. I would like to mention that I'm looking for ideas more than I am looking for specific code.

Here is an example to show what I have tried so far. Consider this hypothetical table with 3 fields that have text (the purpose of this table is irrelevant):

tableWidget

ID     |     Field1     |     Field2     |     Field3    |
1      | jimmy john     | waldo johnson  | carmen smith  |
2      | francis        | david smith    | NULL          |
3      | yvonne         | harry t.       | mr. waldo     |
4      | dr. waldo, md  | hal            | NULL          |

So I want to find all records that have "waldo" in it. My first hunch is to do something like this:

SELECT ID, Field1, Field2, Field3
FROM tableWidget
WHERE ID IN
(
  SELECT ID FROM tableWidget WHERE
  Field1 LIKE '*waldo*' OR
  Field2 LIKE '*waldo*' OR
  Field3 LIKE '*waldo*'
)

The problem here is that with the number of records I'm running, it is extremely slow. The second attempt I did was something like this:

SELECT ID, Field1, Field2, Field3
FROM tableWidget
WHERE ID IN
(
  SELECT ID FROM tableWidget 
  WHERE  Field1 & Field2 & Field3 LIKE '*waldo*'
)

This is still slow. The third way I tried this was to have a Form_Timer event that cycles through tableWidget and does a VBA comparison. My form's recordset was something like this:

SELECT * FORM tableWidget
WHERE ID IN (SELECT * FROM tempTable)

The snippet of code to append to the tape looked something like this"

With rsTableWidget
  If Searching=True AND NOT .EOF Then

    If INSTR(.Fields("Field1") & .Fields("Field2") & ".Fields("Field3"), "waldo") > 0 Then
      rsTempTable.AddNew
      rsTempTable.Fields("ID")=.Fields("ID")
      rsTempTable.Update
      mySubForm.Requery
    End If

    rsTableWidget.MoveNext

  Else

    Searching=False
    rsTableWidget.Close: rsTempTable.Close

  End If
End With

The good thing about this was that it would update in the background. The bad thing about this was that every iteration of Form_Timer, it would flicker. And when it found a record, the mySubForm.requery method would reset the form--not very user friendly.

Other methods I've considered was using VBA code to regularly parse through every word (delimited by spaces) and create a search table:

tableSearchTerm:

ID    |    searchTerm
1     |    jimmy
1     |    john
1     |    waldo
1     |    johnson
1     |    carmen
1     |    smith
2     |    francis
2     |    david
2     |    smith
etc...

I haven't tried this yet, but I suppose the good thing would be that I could index the searchTerm. And I wouldn't have to use wildcards anymore because all the terms are associated with the ID. The bad thing is that this table will get huge quick!

Is there a better method?

Upvotes: 3

Views: 247

Answers (1)

Andre
Andre

Reputation: 27634

Your query looks over-complicated, why don't you use this?

SELECT ID, Field1, Field2, Field3
FROM tableWidget
WHERE 
  Field1 LIKE '*waldo*' OR
  Field2 LIKE '*waldo*' OR
  Field3 LIKE '*waldo*'

But yes, a full text search with wildcards at the beginning of the search terms, will be slow, because no index can be used.

You may want to look at migrating to a server database as backend, that has a Full-Text Search capability. E.g. SQL Server.

This Q&A says it also works with the Express edition: Express with Advanced Services, can't create Full Text Index

If that is not an option, I suggest to let the user choose to

  • search beginnings of fields (Field1 LIKE 'waldo*'), which will be much faster if you index the fields, or
  • do the slow full text search.

It depends on your data. For names e.g., the first option is almost always sufficient.

Upvotes: 1

Related Questions