Use Access 2007 to search a linked SQL 2008 FAST using Full-Text search?
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.
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.
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.
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.
ASKER
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.
ASKER
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.
ASKER
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.....
>>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.
I am not sure what this means, so I cannot tell you if you can or cannot accomplish this with SQL Server alone.
ASKER
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.
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.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It's not what I am looking for but the reality is there may not be an answer that I "want".
>>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:
https://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.
That is correct and is covered under the EE Guidelines here:
https://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.
http://msdn.microsoft.com/en-us/library/ms142559.aspx