By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

Access 97 linked to SQL 2000 large fields problems

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.