Use Access 2007 to search a linked SQL 2008 FAST using Full-Text search?

TrialWorks
TrialWorks used Ask the Experts™
on
I am struggling on finding easy to follow guides on searching tables efficiently in SQL Server. I am not a programmer but managed to assemble an application that has been very valuable in our daily operations... however.. searching is a pain.  

For practical uproses, it's almost impossible to search for annything because of the amount if time it takes to query a large table (lets say 19,000 rows.  When it does return results, its basically doing a keyword search until it finds one... stops... and continues.  Same like inside a word document.  

So, the way this app is setup is we have a basic MS Access 2007 Front-End connected to a very extensive SQL back end.  The SQL backend is used by another application;  my piece is a small plugin that accesses only small pieces of the overall data.

I would like to be able to add something to this Access 2007 front end that can do full-text queries of two tables.  I setup the full-text catalogs and stuff on the SQL server, just dont know how to begin using any of it at the user-level.   Key here is that it needs to work simply for the end user -- and needs to be relatively easiy to assemble for me.

Look forward to feedback.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Top Expert 2012

Commented:
Since you have already created the catalog and (I assume) done a Full Population, all you have to do is build your SQL Statement like this:
1.  For exact matches:
SELECT Col1, Col2, Col3,..
FROM YourTable
WHERE CONTAINS(*, 'keyword')

2. For a phrase:
SELECT Col1, Col2, Col3,..
FROM YourTable
WHERE CONTAINS(*, '"This is a phrase"')

3. For any value starting with:
SELECT Col1, Col2, Col3,..
FROM YourTable
WHERE CONTAINS(*, '"keyword*"')

4.  There are other more advanced possibilities using boolean logic and "INFLECTIONAL" terms. Let me know if you need help with that.

Author

Commented:
LSMConsulting - absolutely.  Looking for more production ready info.  I don't know what the next move is to implement the MSDN stuff inside my app.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
AcPerkins - I did not realize it would be literally as simple as that.    I did not realize that runnign a regular select statement took advantage of the catalogs.  I've been all over looking for various solutions (like 3rd party search engine stuff) that could work.   I'll give it a shot today and see how it works out - that would be too easy but I'll take it.

Author

Commented:
It sort of works, but the issue becomes search logic for me.  I would love some pre-built tool that accounts for all that.  Basically I want smarter search results with minimal effort.   An ability to easily do various searches.....
Top Expert 2012

Commented:
>>An ability to easily do various searches.....<<
I am not sure what this means, so I cannot tell you if you can or cannot accomplish this with SQL Server alone.

Author

Commented:
User friendly searches. Stuff that searches for multiple words in different parts of the record as well as phrases and single words... more over searches within searches.   Basically a useful search and user friendly.  
Top Expert 2012

Commented:
You should be able to accomplish most if not all of that with Full-Text Search, unfortunately you will have to roll-up your sleaves and actually write some code to accomplish that.

Author

Commented:
I find it hard to believe there is not a solution pre-built already to accomplish some of this.   I found ASP driven search engines for SQL they were a little far out from what I want.     I'll keep looking for a pre-built part in the mean time; writing one for now is too far out.
Top Expert 2012
Commented:
>>I find it hard to believe there is not a solution pre-built already to accomplish some of this<<
I am afraid there is no "pre-built" toolkit that makes use of Full-Text search for MS Access 2007, as I stated a month ago you will have to roll-up your sleeves and write some code.

Incidentally, re-reading your original question you state that you have "a large table (lets say 19,000 rows".  19K rows is a very small table and Full-Text search is probably over-kill for a table that size.  You would be better off normalizing and indexing the table appropriately rather than using Full-Text Search.

Author

Commented:
It's not what I am looking for but the reality is there may not be an answer that I "want".  
Top Expert 2012

Commented:
>>It's not what I am looking for but the reality is there may not be an answer that I "want". <<
That is correct and is covered under the EE Guidelines here:
http://www.experts-exchange.com/help.jsp#hs=29&hi=405

The correct answer to some questions is "You can't do that."
Sometimes, you will get an answer that isn't what you want to read, but it still may be the correct answer, and you should award points to the Expert that gave you that answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial