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