Make use of SQL 2000 full text search with Access front end

  • Thread starter Thread starter Jordan
  • Start date Start date
J

Jordan

So far I have helped my users search for records by building search forms
that will allow them to check a few boxes and build a query for them. It
has always been "Find orders for Customer X", "Find closed orders", "Find
shipments over X dollars", etc.

Now I have got some tables that have memo fields in them and users want to
be able to find records based on some of the words and phrases in the memos.
I now need the ability to find record that mention 60 degrees, shaft
wobbles, and so on. Naturally doing a query like below would be horrible on
a large table with a lot of notes:

SELECT * from BIGTABLE where NoteField = "Dumb Phrase"

I figure that I will turn on the SQL 2000 servers ability to perform full
text indexes on these table (or fields in the table), but I don't know how
to query the tables to make use of it. I am hoping to get results something
like a web seach engine where if I put in the words Dumb and Phrase I get a
likely order of results
 
The
SELECT * from BIGTABLE where NoteField like "*Dumb Phrase*"
Click to expand...

I've slightly corrected the needed syntax to search a memo field in access
as per above.
if I put in the words Dumb and Phrase I get a likely order of results
Click to expand...


you owned a likely order results, the search will work is always a day
before, and any memo field with the words dumb phrase will be returned. The
only difference using text indexing in SQL server is that you'll find it
simply performs much faster.

is really nothing else special you have to do. of course you might want a
throw the results of that above query into a continuous sub form and, you
get the results as in my article here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

just go:

strSql = "select * from BIGTABLE where noteFeilds like '*" & me.TextSeach
& "*'"
me.recordSource = strSql

So the general idea here is to simply take your SQL and fro into a
continuous form, or even in some cases you can use a list box. here's some
more screen shots of searching ideas that I've used over the years:

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm
 
Back
Top