DB Search method

[Pipo]

Hi RSusanto,

For finding what?
Records?
just perform a query, something like SELECT * FROM yourtable WHERE
SearchValue = ....

 

[Guest]

Hi,

Can you be more specific? What are you searching for? Can you give us a
breif description of what you are attempting? Please reply to this post with
the information so the group may assist you better.

Thanks

 

[Scott M.]

The first one is pure Structured Query Language. The second is a mix of SQL
and VB so there is more processing that has to be done to resolve the SQL.

The first one is better. Also, don't use InStr anymore. In .NET, string
objects have string methods:

dim x as string
dim y as integer
y = x.IndexOf(searchstring)

Similarly, rather than date/time functions (now(), time(), hour(), etc.) use
dateTime object methods, etc.

 

[RSusanto]

Hi,

Anybody have idea what is the best method to search in the access database ?

 

[RSusanto]

There are 2 type I tried,

SELECT * FROM ..LIKE ...
SELECT * FROM WHERE xx= INSTR(searchstring)

now, which one of them is the best ?

 

[Guest]

Hi,

The first will perform fine as long as the columns that you are searching
are good candidates for an index in the database or if the dataset is not too
large.

Off the top of my head I do not see how the second method will work at all.
I believe that the InStr function in VB will return an integer specifying the
start position of the first occurance of a substring within a string. So if
you have this in your VB code:

"SELECT * FROM Employees WHERE FirstName = " & INSTR("A", "A really long
string goes here")

You actually get

SELECT * FROM Employees WHERE FirstName = 1

I think that you have the right idea here but the wrong implementation. You
can GREATLY reduce the amount of processing that Sql has to do if you limit
the search on the code/application side before you do the query. If you use
= instead of LIKE your query will return much faster and be less likely to
bog down other parts of the application.

If you have any questions or if I have misunderstood your question please
respond to this post and I will try to be more clear.

I hope this helps.
----------------