469,361 Members | 2,376 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,361 developers. It's quick & easy.

Access 97 linked to SQL 2000 large fields problems

Tim
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
Nov 12 '05 #1
2 1403
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
Nov 12 '05 #2
Thought I'd mention... if your database object name contains spaces or
is a reserved SQL server keyword, you'll need to surround the name with
square brackets.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Praty77 | last post: by
4 posts views Thread by Vanessa | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.