473,854 Members | 1,659 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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"."MyTa ble"
SET "Name"=@P1 WHERE "Number" = @P2 AND "Name" = @P3',
N'@P1 varchar(255),@P 2 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.publi c.sqlserver.odb c yesterday but have not had any answers

Nov 12 '05 #1
2 1602
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,

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Justin Michael | last post by:
Hi, I have a linked server in sql 7 that connects to a ms access 2000 database. It has been working for a while, however lately, after we imported a large amount of new data, the database fails to query a certain (large number of records) table. I can query and get responses for every other table except the largest table.
by: Max | last post by:
Hi, I have SqlServer 2000 as back end and Access 2000 as front-end. All tables from Sqlserver are linked to Access 2000. I am having write conflict problem with one of my form which is bound to a query which consist of let say tableA , TableB and TableC (all are linked tables from SqlServer. While the form is open I am modifying TableA through code on Form_AfterUpdate Event and getting the following message.
by: Praty77 | last post by:
Hello - we have been using access 97 for a multiple user trading system at a small bank. Typically around 10 users entered a total of hundred trades everyday. Some of the data was shared with a MS SQL 2000 server, which we accessed (both for read and write) as a linked ODBC table. Over the period of time, the system has become quite complex, with 30 tables, 30 forms and a size of 140 M. We have been facing a number of problems during...
by: Wayne Aprato | last post by:
I have a simple database which was originally written in Access 97. When converted to Access 2000 file format it ran flawlessly in Access 2002. I've just tried to run it in Access 2003 and I am seeing the following behaviour: Some of the fields on the continuous main form which is a list of jobs with their related details "flicker". While this is going on the form seems to function as it should and isn't locked up. If I click and hold...
by: Tim | last post by:
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.
by: B Moor | last post by:
Host PC: Win SBS 2003 with 2.6 GHz processor and 1GB RAM SQL Server 2000 sp3 (or latest).MS Office 2k3 I have the access 2k3 front end running on server (this may get changed) and all was well until an upgrade 1 week ago when I installed a new version of the mde (with very minor changes) and a new VB application via a VB6 installer setup.exe. Access uses odbc to linked tables on SQL Server, plus a few local tables.
by: smd | last post by:
Hello and thanks for taking the time to read this. I've looked all over the web and newsgroups and can't find a solution to my problem. I've posted this question to the Access 2000 group as well - sorry for cross posting. Back ground: User at remote site has Access 2000 running on XP (don't know SP of either). Linked tables are DB2 v8 running on AIX. All of the linked tables are in the same database and schema.
by: Vanessa | last post by:
Hi there I am an Access developer, and I have written applications for a 30 telephone call center, using the standard multiuser jet engine, it all works fine, but I want to move our systems onto MySQL, as we get the odd #DELETED# (that old chestnut) so that it is more stable, I have installed mysql and myodbc and configured it in data sources (ODBC) and it works fine (done the TEST). But when I try to export a table from the mdb
by: ARC | last post by:
Just curious if anyone is having issues with Acc 2007 once the number of objects and complexity increases? I have a fairly large app, with many linked tables, 100's of forms, queries, reports, and lots of vba code. I'm nearly finished with re-doing my app in access 2007, and just imported an add-in program, which has added even more forms, queries and linked tables. Every so often now, after opening many different screens, I'll...
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.