Efficiency

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I wrote a script that built the SQL to examine text fields using a LIKE
operator,

text LIKE '*searchtext*'

but for some reason or other I couldn't get it to work. I instead
decided to test for inclusion by using

InStr(1, text, searchtext) > 0

which works, but the code runs a whole lot slower than I'd like it to.
The question is how much less efficient is evaluating the InStr
function and then doing a comparison versus simply using a LIKE
operator (i.e. would it be worth it for me to spend the time to make
the latter work)?
 
The Like operator should work. If the back end is not an Access table, or if
you have set the option for ANSI 92, you might try using % as the wildcard
characters instead of *.

If you have lots of these criteria, and want to build an efficient search
based on only those boxes where the user actually enters something, see this
article and sample database:
Search Database: how to handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
 
Fortunately, the query isn't based upon a user input... the entire
process takes something on the order of 3 hours to run.

I too think the LIKE operator should work, but Access doesn't seem to
agree. I create a SQL query in vb which counts the number of records
which satisfy the LIKE condition (among other things), and the answer
returned when running the code is always "0" (when I know it's not).
If I step through the code, generate the SQL string, and then run that
query within Access, it returns the correct number. So for some reason
the generated SQL works when I run it individually, but it fails when I
run it from within vb. I cannot think of a single good explanation for
this. That's why I tried using the InStr (without changing anything
else) and it works within vb, mysteriously enough.

So, rather than agonize about it, should I just move on?
 
When you say "VB", you mean pure Visual Basic, not VBA in Access?
How are you making the connection to Access?
Using DAO, or ADO?
Did you try the % as wildcard?
 
Pardon, I meant VBA. It's ADO, using the 'CurrentProject.Connection'
connection. I'll try the other wildcard when this latest batch
finishes running! :-)
 
Back
Top