2

My Access DB is linked to a SQL Server table that contains a NVARCHAR(MAX) field that stores Rich Text. When trying to retrieve the field from Access the text is cut off at 255 characters. Access is currently reading the Data Type as "Long Text" however, I cannot set the Text Format to Rich Text in the Field Properties window due to it being a linked table (I receive the message "Operation is not supported for this type of object").

Any suggestions on how to display more than 255 characters in Access when pulling from an ODBC connection? Below is a test of numbers to show where the text is cutting off, I made these numbers bold, but removed the bold tags to display here.

ODBC Connection INFO: SQL Server ODBC Driver Version 10.00.17763.01, File SQLSRV32.DLL

SQL Server Test Displays: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300

Access Text Box and Table link only show: 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78

braX
  • 11,506
  • 5
  • 20
  • 33
MRG123
  • 123
  • 3
  • 12
  • Exactly this is operating well here: A forms textbox control, set to text format 'Rich Text', which is bound to a Microsoft SQL Server table field of type 'NVARCHAR(MAX)'. – AHeyne Oct 22 '19 at 12:28
  • Please include steps to reproduce this problem. Access should be able to handle this just fine. – Erik A Oct 22 '19 at 12:32
  • Which ODBC driver do you use? – Andre Oct 22 '19 at 12:41
  • You can't set the Text Format to Rich Text in the linked table Design view, you need to use a form, and set "Rich Text" in a text box. – Andre Oct 22 '19 at 12:42
  • I am using SQL Server ODBC Driver Version 10.00.17763.01, File SQLSRV32.DLL. The Text Box I am using to pull the data in is set to Rich Text. And it displays the formatting correctly but cuts off at 255 Characters. When viewing the table in Access I also only see 255 characters. When viewing the table in SQL Server I see all of the data. – MRG123 Oct 22 '19 at 12:49
  • This may be relevant: https://stackoverflow.com/questions/7978879/moving-from-text-to-varcharmax-are-there-any-troubles-to-expect-with-ms-acces -- but nothing in there explains your problem. If feasible, try a newer ODBC driver. Or even try NTEXT instead of NVARCHAR(MAX). – Andre Oct 22 '19 at 13:34
  • For heavens sake don't use NTEXT. That datatype has been deprecated now for almost 15 years. You should use nvarchar(max) INSTEAD of NTEXT, not the other way around. - @Andre https://learn.microsoft.com/en-us/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15 – Sean Lange Oct 22 '19 at 13:45
  • I know all that, but things are not that simple with ODBC. Please have a look at the question I linked above. @SeanLange – Andre Oct 22 '19 at 13:51
  • We're currently at [version 17](https://learn.microsoft.com/sql/connect/odbc/download-odbc-driver-for-sql-server) of the ODBC drivers. The driver you're using is an ancient one included with the MDAC components for Windows. It has the dubious benefit of working without an installation, but it's incredibly old. At least the newer drivers should allows strings of up to 8000 characters. If you do have to go with `TEXT`/`NTEXT` for compat purposes, make sure to constrain this to a view (with `CAST`); don't change your column types just to please Access. – Jeroen Mostert Oct 23 '19 at 10:43

0 Answers0