April 11, 2002 at 7:45 am
We have further defined our Access 97 to SQL Server ODBC problem. The problem is specific to Access 97, and occurs on both SQL Server 7 and SQL Server 2000.
For testing, we created a blank SQL database and created in it a single table with three fields:
TestID,Int, Identity, primary key.
ShortMsg, Char(10).
LongMsg, ntext
We then create a blank Access 97 database and link it to the SQL table via ODBC.
Unless the first field is set to not be an identity field, Access cannot update the table. attempting to do so results in the error:
"[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates. (#306)"
Furthermore, any attempt to add a new record results in Access reporting that the record has been deleted as soon as another record is moved to. However, the record has in fact been sucessfully added to the SQL Server table.
All the latest service packs that I know of have been applied. We are at Access 97 (SP2b), MDAC 2.6 (ODBC Driver 2000.80.194.00), SQL Server 7.0 (SP3).
Upgrading to SQL Server 2000 does not resolve the problem.
Upgrading to Access 2000 (and staying on SQL Server 7) does resolve the problem, but is unfortunately not viable at this time due to issues with other existing applications.
I have worked on other sites where this problem does not occur, so I believe that there is a setting somewhere that needs to be changed, or a patch or service pack that still needs to be applied. Can anyone tell me what we are doing wrong?
Thanks in advance,
David L.
April 11, 2002 at 9:06 am
We use Access 97 quite a bit on tables that have identity cols, no issues that I know of. Have you tried removing the ntext column and tested without it?
Andy
April 11, 2002 at 5:37 pm
Yes, without using ntext there is no problem. Is there another field suitable for holding long messages? We're talking about the ability to take a customer complaint letter or email and copy it lock, stock and barrel into this field.
April 11, 2002 at 8:02 pm
Text or ntext is the right way to go, text being the better choice as far as disk space if you don't need to support unicode. I don't have 97 installed here to test, be interesting to profile and see what Access is submitting to SQL. My guess is that it's trying to do a pessimistic update based on all the fields in the table which would explain the problem with the where clause. Just a guess!
I see a lot of similar posts in the newsgroups, couldnt find one that had the answer. Maybe you could "trick" it with a view, dont know. Another option might be to use ADO for that part of it. You can set a reference to ADO, just remember that order matters in references. You'll want DAO first, then ADO, and you'll have to fully qualify any ADO code (adodb.recordset) so that it will resolve. Not fun if you're relying on Access for the bulk of the work. Pass through query might be another option. Worst case you can store the file name and put the document on a share.
Gotta think there is an easier way, just not sure what it is.
Andy
December 19, 2002 at 10:34 am
I would add a TimeStamp column to your SQLServer table. I had similar problems until I added a timestamp column. I now add timestamp columns on every SQLServer table that I will link to from Access.
Check out these Microsoft Knowledge base articles: http://support.microsoft.com/default.aspx?scid=kb;en-us;278696
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;128808
A slight tangent: If you are using Access linked to SQLServer tables, make sure you read this article (http://support.microsoft.com/default.aspx?scid=kb;en-us;169777 ) to make sure Access uses the appropriate index for each table.
December 19, 2002 at 12:20 pm
The problem is nothing to do with the identity or timestamp columns. In order to be able to insert/update data in table, the table must have a primary key defined.
Remeber to refresh the linked table from Access after any changes to the table in SQL server.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply