Full-Text Results to MS Access

Greetings!
I have a requirement to set up several full-text indexes on our SQL Server
2000 database. My problem/question is how to get the results to show up in a
form or datasheet in our MS Access XP front end? I'm planning to be able to
allow the user to enter search criteria via an Access form, and get the
results back by populating a subform with the returned data. But since I'm
fairly new to using SQL Server, I'm not sure how to set up the recordset in a
form (or report) in Access. Also, I was thinking that the "best" way to deal
with this is to pass the user's search criteria to a stored procedure in SQL
Server. Is this the best way to do it?
Thanks very much for your help!
PaulJS
PaulJS,
The problem should be no different than getting any normal (non-FTS) SQL
Server backend database ad hoc or stored procedure query results returned to
your MS Access XP front end. For example, using the pubs database and the
authors table, you can execute:
select * from authors where au_lname = 'white'
Once, you've FT-enable the authors table in the pubs database, and run a
Full Population via the FT Indexing Wizard, you can issue the following
query:
select * from authors where contains(au_lname, 'white')
As for the best way to deal with the users search criteria (and to avoid the
infamous "ignore word" error), you may want to parse out the noise words
first, and/or enclose the search criteria within the appropriate quotes. For
more details on this, see KB article 246800 (Q246800) "INF: Correctly
Parsing Quotation Marks in FTS Queries" at
http://support.microsoft.com//defaul...;EN-US;246800. You can
find more SQL Server 2000 Full-Text Search Resources and Links at:
http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> Greetings!
> I have a requirement to set up several full-text indexes on our SQL Server
> 2000 database. My problem/question is how to get the results to show up
in a
> form or datasheet in our MS Access XP front end? I'm planning to be able
to
> allow the user to enter search criteria via an Access form, and get the
> results back by populating a subform with the returned data. But since
I'm
> fairly new to using SQL Server, I'm not sure how to set up the recordset
in a
> form (or report) in Access. Also, I was thinking that the "best" way to
deal
> with this is to pass the user's search criteria to a stored procedure in
SQL
> Server. Is this the best way to do it?
> Thanks very much for your help!
> --
> PaulJS
|||John:
Thanks for the info! After reading your response, I think my problem is
more of an elemental one: Being new to SQL Server and ADO, how can I bind the
results of my full-text query to, say, an Access form or datasheet (datagrid)
with the results? (My database was started in pre-ADO days so the Access
front end uses DAO on recordset operations.) Although I'm using ADO more and
more, I use it mostly to perform some operation on data and return a result
like a true/false flag, a value, etc., but if the result is a recordset, I
don't know how to bind (display) those records to the form. Access makes it
easy to use an Access query to be bound to the form as the form's record
source. My problem (or weakness), is not knowing how to get recordset data
results back from a stored procedure and display those results in a form. I
want to be able to let users enter search criteria, then get a list of
results, and let the user select one of those records to do some other
operation. For instance, a user wants to search a table named "tblProject"
based on some full-text search criteria. The criteria is sent to a stored
procedure, and then the Project records matching the criteria are displayed
in an Access datagrid or form; the user selects one of the records, then does
some operation based on the selected Project record.
Did that make sense? If not, let me know.
Thanks again!
"John Kane" wrote:

> PaulJS,
> The problem should be no different than getting any normal (non-FTS) SQL
> Server backend database ad hoc or stored procedure query results returned to
> your MS Access XP front end. For example, using the pubs database and the
> authors table, you can execute:
> select * from authors where au_lname = 'white'
> Once, you've FT-enable the authors table in the pubs database, and run a
> Full Population via the FT Indexing Wizard, you can issue the following
> query:
> select * from authors where contains(au_lname, 'white')
> As for the best way to deal with the users search criteria (and to avoid the
> infamous "ignore word" error), you may want to parse out the noise words
> first, and/or enclose the search criteria within the appropriate quotes. For
> more details on this, see KB article 246800 (Q246800) "INF: Correctly
> Parsing Quotation Marks in FTS Queries" at
> http://support.microsoft.com//defaul...;EN-US;246800. You can
> find more SQL Server 2000 Full-Text Search Resources and Links at:
> http://spaces.msn.com/members/jtkane/Blog/cns!1pWDBCiDX1uvH5ATJmNCVLPQ!305.entry
> Regards,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "PaulJS" <PaulJS@.discussions.microsoft.com> wrote in message
> news:C42FDBE2-6120-44FB-8553-CE36A977315C@.microsoft.com...
> in a
> to
> I'm
> in a
> deal
> SQL
>
>