Access 97 linked to SQL 2000 large fields problems

Hi there,
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
Tim
Well, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim
|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:

>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Tim

Access 97 linked to SQL 2000 large fields problems

Hi there,
I've been slogging away at this one for a while and searching on
Google Groups so I thought perhaps if I ask someone can help.
We have an old Access 97 Db, which we have recently moved to SQL 2000
to make it easier to build a .net web interface. However we still
need to use the old Access 97 interface, so we've linked the tables
using an ODBC file DSN.
Unfortunately the memo fields have come across to SQL (using the DTS)
as Text. This means that when the Access forms try to write back to
the SQL tables they attempt to match all the fields like so:
exec sp_executesql N'UPDATE "dbo"."MyTable"
SET "Name"=@.P1 WHERE "Number" = @.P2 AND "Name" = @.P3',
N'@.P1 varchar(255),@.P2 int,@.P3 varchar(255)',
'NewNameVal', 1, 'OldNameVal'
If the "Name" happens to be "Text" (rather than a varchar as in the
example) then I get the error "The text, ntext, and image data types
cannot be compared or sorted, except when using IS NULL or LIKE
operator.". Fair enough, but I can't change the way the Access tries
to match up the data (can I?).
So I've changed all the text fields into (large) varchars and
re-linked the tables. Now the whole thing just collapses in a heap -
"ODBC--Call failed", and I'm at the end of my tether.
If anyone can help I'd be very grateful.
TimWell, from further searching it appears that Access 97 linking to SQL
Server 2000 has this problem when there are "spaces in the fieldnames"
(I generally try to avoid this myself but it's not my database
"design"). Not that the fields with the spaces in the names are the
memo fields themselves but they are in the same table and apparently
that's enough to confuse it (from other posts, confirmed by my
testing). Rather than rename the fields and mess up all the Access
front end (forms and code) as well as the existing web interface,
we're going to upgrade to Access XP which seems to have no such
problems. Job done.
Cheers anyway,
Tim|||The other issue you may not have realized could cause problems is
basic data type incompatibility between A97 and SQLS 2000, which
supports Unicode. Moving to a more recent version of Access means that
a lot of the incompatibilities won't exist --plus, A97 isn't being
supported anymore :-(
--Mary
On 14 Apr 2004 08:13:06 -0700, temp1999@.yahoo.co.uk (Tim) wrote:

>Well, from further searching it appears that Access 97 linking to SQL
>Server 2000 has this problem when there are "spaces in the fieldnames"
>(I generally try to avoid this myself but it's not my database
>"design"). Not that the fields with the spaces in the names are the
>memo fields themselves but they are in the same table and apparently
>that's enough to confuse it (from other posts, confirmed by my
>testing). Rather than rename the fields and mess up all the Access
>front end (forms and code) as well as the existing web interface,
>we're going to upgrade to Access XP which seems to have no such
>problems. Job done.
>Cheers anyway,
>Timsql

accents in full-text searching

If I have accented characters in my table which is full-text indexed then I
cannot search for the corresponding non-accented character. e.g. I have the
character "é" in my column (which is nvarchar), so searching for "é" comes up
with results because it is actually entered as "é" in the database, but
searching for "e" does not come up with any results. I have tried changing
the collation on this column to SQL Collation "SQL_Latin1_General_CP1_CI_AI"
and then I tried Windows Collation "Latin1_General_CI_AI". My database
collation name is "Latin1_General_CI_AI". I've set the default full-text
language to 0 (neutral).
Does anyone know how to solve the problem of accent insensitivity for
full-text searching?
my select @.@.version is:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT
5.1 (Build 2600: Service Pack 2)
Thanks in advance
Enoch
This is handled correctly in SQL 2005. However in the meantime you should
trap for these accented versions of the words and expand your search for
both the accented and unaccented versions of the work.
So a search on bebe would be expanded to "bb" or "bebe".
There will be some correct stemming when you do a FreeText query is you are
using the French word breaker. For instance if you have removed noise words
for all verb forms of the verb to be (etre) from the French noise word list,
you will get search results to the accented versions of this word when doing
a FreeText search.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Enoch" <Enoch@.discussions.microsoft.com> wrote in message
news:E10365FE-F791-4973-84F1-DC4C9596B1AD@.microsoft.com...
> If I have accented characters in my table which is full-text indexed then
> I
> cannot search for the corresponding non-accented character. e.g. I have
> the
> character "" in my column (which is nvarchar), so searching for "" comes
> up
> with results because it is actually entered as "" in the database, but
> searching for "e" does not come up with any results. I have tried
> changing
> the collation on this column to SQL Collation
> "SQL_Latin1_General_CP1_CI_AI"
> and then I tried Windows Collation "Latin1_General_CI_AI". My database
> collation name is "Latin1_General_CI_AI". I've set the default full-text
> language to 0 (neutral).
> Does anyone know how to solve the problem of accent insensitivity for
> full-text searching?
> my select @.@.version is:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows
> NT
> 5.1 (Build 2600: Service Pack 2)
> Thanks in advance
> Enoch
>