Access Crashes with CONTAINS in ServerFilter

Please excuse the crosspost from the Access forms group.
We are trying to implement a keyword search functionality in an Access 2002
project. We have set up SQL Server 2000 for full-text searching. Testing
SQL Server through Query Analyzer produces the expected results.
In a search tab on a form in an Access project, we dynamically construct a
WHERE clause for a subform. A keyword search is implemented by adding the
CONTAINS predicate to the ServerFilter string.
Once constructed, the ServerFilter is then applied to the subform and the
subform requeried to display the search's results. In testing, when we enter
criteria into the keyword field, Access crashes with no preceeding error
message. The crash occurs on the Requery statement for the subform. On the
screen, the subform fields all go blank, one gets the #Name error appearing
in it, and then the "Access has encountered . . . " message appears. An
example of an exact ServerFilter string producing the error is
"CONTAINS(*,'data')".
The documentation with Access says that the ServerFilter property is a WHERE
clause (without the WHERE). Is there an undocumented limitation that
predicates like CONTAINS cannot be used in the ServerFilter string, or am I
missing something? Any help appreciated.
can you run profiler on SQL Server to see what the sql command access is
running on SQL Server is.
if you can run this command in query analyzer, the problem is not with SQL
Server FTS, but rather with Access.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Smith" <PGCS@.online.nospam> wrote in message
news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> Please excuse the crosspost from the Access forms group.
> We are trying to implement a keyword search functionality in an Access
2002
> project. We have set up SQL Server 2000 for full-text searching.
Testing
> SQL Server through Query Analyzer produces the expected results.
> In a search tab on a form in an Access project, we dynamically construct a
> WHERE clause for a subform. A keyword search is implemented by adding the
> CONTAINS predicate to the ServerFilter string.
> Once constructed, the ServerFilter is then applied to the subform and the
> subform requeried to display the search's results. In testing, when we
enter
> criteria into the keyword field, Access crashes with no preceeding error
> message. The crash occurs on the Requery statement for the subform. On
the
> screen, the subform fields all go blank, one gets the #Name error
appearing
> in it, and then the "Access has encountered . . . " message appears. An
> example of an exact ServerFilter string producing the error is
> "CONTAINS(*,'data')".
> The documentation with Access says that the ServerFilter property is a
WHERE
> clause (without the WHERE). Is there an undocumented limitation that
> predicates like CONTAINS cannot be used in the ServerFilter string, or am
I
> missing something? Any help appreciated.
|||The culprit is definitely Access. About that there was really no question.
I am hoping someone who uses SQL Server full text searching may have
encountered this problem with Access and know how to resolve it. Access is
producing a string of SQL SELECT statements (54 to be exact) which are in
the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
With each successive call by Access, an additional set of parentheses is
added around the CONTAINS predicate.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> can you run profiler on SQL Server to see what the sql command access is
> running on SQL Server is.
> if you can run this command in query analyzer, the problem is not with SQL
> Server FTS, but rather with Access.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Paul Smith" <PGCS@.online.nospam> wrote in message
> news:AF65C9BB-3C00-4A95-B71B-95C6542CB6A1@.microsoft.com...
> 2002
> Testing
a[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
> enter
> the
> appearing
> WHERE
am
> I
>
|||Paul,
To the best of my knowledge, no one has reported a similar problem via this
newsgroup and I've been replying and monitoring it since it's inception in
late 2000. I'd recommend that you open a support case with Microsoft PSS SQL
Server &/or Access support to see if they have had past customers who have
encountered this possible bug. If you do, could you post back here what the
result was?
Other than the multiple additional set of parentheses that Access is adding,
can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
'string') via Access and if so do you get the same error? Is your goal to
issue SQL FTS queries to SQL Server as the backend to an Access front-end?
Thanks,
John
"Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> The culprit is definitely Access. About that there was really no
question.
> I am hoping someone who uses SQL Server full text searching may have
> encountered this problem with Access and know how to resolve it. Access
is[vbcol=seagreen]
> producing a string of SQL SELECT statements (54 to be exact) which are in
> the form SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*, 'string')).
> With each successive call by Access, an additional set of parentheses is
> added around the CONTAINS predicate.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:O47ozRtiEHA.2012@.TK2MSFTNGP09.phx.gbl...
SQL[vbcol=seagreen]
construct[vbcol=seagreen]
> a
> the
> the
we[vbcol=seagreen]
error[vbcol=seagreen]
On[vbcol=seagreen]
An
> am
>
|||For anyone else who might encounter this problem: we seem to have resolved
the problem here. One of our developers noticed that we had gotten angled
quotation mark characters rather than the typical straight apostrophe as a
string delimiter. We aren't quite sure how this happened, but once this was
corrected, things seemed to start working correctly.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23Ic$xQxiEHA.2812@.tk2msftngp13.phx.gbl...
> Paul,
> To the best of my knowledge, no one has reported a similar problem via
this
> newsgroup and I've been replying and monitoring it since it's inception in
> late 2000. I'd recommend that you open a support case with Microsoft PSS
SQL
> Server &/or Access support to see if they have had past customers who have
> encountered this possible bug. If you do, could you post back here what
the
> result was?
> Other than the multiple additional set of parentheses that Access is
adding,[vbcol=seagreen]
> can you issue a SELECT * FROM "dbo"."tablename" WHERE (CONTAINS (*,
> 'string') via Access and if so do you get the same error? Is your goal to
> issue SQL FTS queries to SQL Server as the backend to an Access front-end?
> Thanks,
> John
>
> "Paul Smith" <paulgcsmith@.hotmail.com> wrote in message
> news:u7pEKwtiEHA.2696@.TK2MSFTNGP11.phx.gbl...
> question.
> is
in[vbcol=seagreen]
is[vbcol=seagreen]
> SQL
Access[vbcol=seagreen]
> construct
adding[vbcol=seagreen]
and[vbcol=seagreen]
> we
> error
> On
> An
a[vbcol=seagreen]
that[vbcol=seagreen]
or
>