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. I posted this to
microsoft.public.sqlserver.odbc yesterday but have not had any answers
yet.
Tim
